3个最常见案例详解DBA日常维护-技术圈

导读：DBA的大部分工作都是围绕着对数据库的维护而展开的，常规的日常维护更是占了绝大多数。本节将围绕日常维护中最常见的三个案例展开讲解，与大家分享排查此类问题的思路。

作者：叶桦徐浩张梦颖应以峰

来源：大数据DT（ID：hzdashuju）

01 TX锁处理

TX锁，也称事务锁或行级锁，是控制数据库并发访问的一项重要技术，也是数据完整性和一致性的重要保证。本文不会过多阐述锁的类型和具体原理，而是重点讲解在生产环境中遇到锁的时候，如何快速查找源头并进行查杀。

有经验的DBA在遇到TX锁时，第一反应就是查询v$lock和v$session视图，定位LMODE和REQUEST类型互斥的会话并进行查杀。然而，随着数据库版本不断地迭代更新，v$session视图的内容越来越丰富，可以直接使用blocking_session、blocking_instance、final_blocking_instance和final_blocking_session字段进行定位。

对于锁层次的排查可以重复查询v$session来确定，但如果锁层次有100层，那么通过人工遍历100次的方式，显然过于低效，不适用于生产环境。

下面就来介绍本节的主角：Oracle的SYS_CONNECT_BY_PATH函数。

自Oracle 9i开始，DBA就可以使用SYS_CONNECT_BY_PATH函数将父节点到当前行的内容以“路径”或层次的形式显示出来。该功能刚好符合我们递归查找锁层次的需求，在这里，笔者模拟了锁环境，可以使用如下语句查询锁信息：

SQL> select a.inst_id,
       a.process,
       a.sid,
       a.serial#,
       a.sql_id,
       a.event,
       a.status,
       a.program,
       a.machine,
       connect_by_isleaf as isleaf,
       sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
       level as tree_level
  from gv$session a
 start with a.blocking_session is not null
connect by (a.sid || '@' || a.inst_id) = prior
           (a.blocking_session || '@' || a.blocking_instance);
<!--省略部分列-->
INST_ID PROCESS SID  SERIAL# EVENT                         STATUS  ISLEAF TREE            TREE_LEVEL
------- ------- ---- ------- ----------------------------- ------- ------ --------------- ---------
      1    7663   17    6749 enq: TX - row lock contention ACTIVE       0 <- 17@1                 1
      1    6198   25    9989 SQL*Net message from client   INACTIVE     1 <- 17@1 <- 25@1         2
      1    6310   28   23199 enq: TX - row lock contention ACTIVE       0 <- 28@1                 1
      1    6198   25    9989 SQL*Net message from client   INACTIVE     1 <- 28@1 <- 25@1         2

下面对代码段中的部分参数进行说明。

INST_ID：会话所在的节点号。
PROCESS：客户端进程号，与v$process中的spid不是同一个。
SID、SERIAL#、SQL_ID、STATUS、PROGRAM、MACHINE：会话信息。
ISLEAF：是否为源头，0代表否，1代表是。
TREE：树形结构，锁的层次，例如，<- 152@2 <- 153@2 <- 161@1，从左到右依次表示为节点2的会话152被节点2的会话153堵塞，而节点2的会话153又被节点1的会话161堵塞。所以节点1的会话161是锁的源头。
TREE_LEVEL：树形层次。

锁源头的查杀方法有两种，说明如下。

1）通过ISLEAF进行筛选，直接查杀锁源头，语句如下：

SQL> select 'alter system kill session ''' || sid || '' || ',' || serial# || ',@' ||
       inst_id || ''' immediate;' db_kill_session
  from (select a.inst_id,
               a.process,
               a.sid,
               a.serial#,
               a.sql_id,
               a.event,
               a.status,
               a.program,
               a.machine,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
               level as tree_level
          from gv$session a
         start with a.blocking_session is not null
        connect by (a.sid || '@' || a.inst_id) = prior
                   (a.blocking_session || '@' || a.blocking_instance))
 where isleaf = 1
 order by tree_level asc;
KILL_SESSION
---------------------------------------------------
alter system kill session '161,5579,@1' immediate;
alter system kill session '161,5579,@1' immediate;

SQL> select inst_id, 'kill -9 ' || spid os_kill_session
  from (select p.inst_id,
               p.spid,
               a.sid,
               a.serial#,
               a.sql_id,
               a.event,
               a.status,
               a.program,
               a.machine,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
               level as tree_level
          from gv$session a, gv$process p
         where a.inst_id = p.inst_id
           and a.paddr = p.addr
         start with a.blocking_session is not null
        connect by (a.sid || '@' || a.inst_id) = prior
                   (a.blocking_session || '@' || a.blocking_instance))
 where isleaf = 1
 order by tree_level asc; 
   INST_ID OS_KILL_SESSION
---------- --------------------------------
         1 kill -9 30049

2）借助v$session中的final_blocking_instance和final_blocking_session定位锁源头，语句如下：

SQL> select 'alter system kill session ''' || ss.sid || '' || ',' || ss.serial# || ',@' ||
       ss.inst_id || ''' immediate;' db_kill_session
  from gv$session s, gv$session ss
 where s.final_blocking_session is not null
   and s.final_blocking_instance = ss.inst_id
   and s.final_blocking_session = ss.sid
   and s.sid <> ss.sid
DB_KILL_SESSION
--------------------------------------------------
alter system kill session '161,5579,@1' immediate;
alter system kill session '161,5579,@1' immediate;

SQL> select p.inst_id, 'kill -9 ' || p.spid os_kill_session
  from gv$session s, gv$session ss, gv$process p
 where s.final_blocking_session is not null
   and s.final_blocking_instance = ss.inst_id
   and s.final_blocking_session = ss.sid
   and ss.paddr = p.addr
   and ss.inst_id = p.inst_id
   and s.sid <> ss.sid
   INST_ID OS_KILL_SESSION
---------- --------------------------------
         1 kill -9 30049

执行拼接生成的语句，即可杀掉锁的源头。

想必大家都遇到过在数据库层面发起“alter system kill session”（数据库层杀掉会话，不加immediate关键字）时，经常会出现资源无法及时释放、会话一直处于killed状态的情况。

如果这个会话是锁的源头，那么除了等待PMON（进程监视器）来清理之外，再没有更好的办法了，而在操作系统层面杀掉进程的方式，基本上是百试百灵。

使用系统命令“kill -9”杀死进程，系统向该process进程发出sigkill，sigkill信号直接发送给init进程，终止process进程。这种方式直接终止了Oracle 会话中对应的操作进程，资源也可以直接释放。

下面就来重点讲解“alter system kill session”的过程，以及在“alter system kill session”杀掉会话之后，为何会查不到处于killed状态的会话所对应的系统进程spid。

“alter system kill session”（不加immediate关键字）杀掉会话可分为两种场景进行讨论：会话状态分别是active和inactive。

使用此命令杀掉处于active状态的会话时，过程可以简单概括如下：

会话在收到kill信号后进行回滚，此过程不可被中断，直至过程完成，该会话会接收到“ORA-00028: your session has been killed”信息，PMON清理会话，释放资源。如果1分钟过后，上述动作还未完成，则该会话将被标记为killed状态，若会话拥有的资源未释放，则等待PMON进程清理会话。

使用此命令杀掉处于inactive状态的会话时，过程可以简单概括如下：

会话在收到kill信号后被标记为killed状态，会话拥有的资源未释放，等待PMON进程清理会话。如果会话再次发出查询信号，会话就会接收到“ORA-00028: your session has been killed”信息，PMON清理会话，释放资源。

接下来模拟不加immediate参数，杀掉会话后状态被标记为killed，操作系统查不到进程的实验场景，过程如下：

SQL> select username,sid,serial#,paddr,server,status from v$session where username = 'SCOTT';

USERNAME     SID    SERIAL# PADDR            SERVER    STATUS
---------- ----- ---------- ---------------- --------- --------
SCOTT         17       6733 00000000A34C7040 DEDICATED INACTIVE
SCOTT        158       9177 00000000A34D4998 DEDICATED INACTIVE

SQL> select b.sid,b.serial#,c.spid,b.status from v$session b,v$process c where 
    b.paddr = c.addr and b.sid in (17,158);
 SID    SERIAL# SPID      STATUS
---- ---------- --------- --------
  17       6733 23883     INACTIVE
 158       9177 24120     INACTIVE

手动杀掉这两个会话的命令如下：

SQL> alter system kill session '17,6733';
SQL> alter system kill session '158,9177';

再次查询这两个会话的状态，命令及结果如下：

SQL> select username,sid,serial#,paddr,server,status from v$session where username = 'SCOTT';
USERNAME    SID    SERIAL# PADDR            SERVER    STATUS
---------- ---- ---------- ---------------- --------- --------
SCOTT        17       6733 00000000A3551F18 PSEUDO    KILLED
SCOTT       158       9177 00000000A3551F18 PSEUDO    KILLED

从代码中我们可以发现，当两个会话的状态为killed时，会话的paddr指向同一地址00000000A3551F18（虚拟地址），此地址在操作系统层面并无对应的spid，这就是当会话的状态变为killed之后，使用以下语句查不到spid的原因，查询示例代码如下所示：

SQL> select b.sid,b.serial#,c.spid,b.status from v$session b,v$process c where 
    b.paddr = c.addr and b.sid in (17,158);
no rows selected

此时，我们就可以使用前文的查询语句，查杀并清理会话，命令及结果如下：

SQL> select 'alter system kill session ''' || c.sid || '' || ',' || c.serial# || '''
    immediate;' kill_session from v$session c where status='KILLED';
KILL_SESSION
-----------------------------------------------
alter system kill session '17,6733' immediate;
alter system kill session '158,9177' immediate;

因此，在查杀会话时，可以考虑直接使用“alter system kill session 'sid,serial#' immediate”命令快速清理会话。需要注意的是，在查杀会话之前一定要再三确认信息，千万不要误杀了系统核心进程。

02 高峰期谨慎编译业务对象

想必大家都遇到过这样的情况，在业务高峰期如果编译存储过程、函数或视图，就会导致大量使用该对象的会话堵塞，自身也将处于挂起状态，后台等待事件为“library cache pin”。

在日常运维中，“library cache”相关等待较为常见，主要分为“library cache lock”或“library cache pin”，前者维护“library object handle”上的并发访问，后者维护“library object handle”下对应heap的并发访问，lock管理并发，pin管理一致性。

当我们编译存储过程、函数或视图的时候，Oracle就会在这些对象的handle上获得一个“library cache lock”，然后在这些对象的heap上获得pin，这样就能保证在编译的时候其他进程不会来更改这些对象。

有了以上的理论基础，当高峰期编译对象出现会话堵塞的问题时，我们应该如何处理呢？这里就会用到基表DBA_KGLLOCK，其包含如下两个字段。

kgllkuse字段：“Address of the user session that holds the lock or pin”，主要用于记录持有lock或pin的用户地址。
kgllkhdl字段：“Address of the handle for the KGL object”，主要用于记录handle的对象地址。

故障发生时，首先查看后台等待事件，命令及输出具体如下：

SQL> select inst_id,sid, event, p1,p1text,p1raw,p2,p2text,p2raw from gv$session 
    where wait_class<>'Idle';

INST_ID  SID EVENT              P1 P1TEXT                   P1RAW         
------- ---- ------------------ -------------------------   ---------------- 
      1   33 library cache pin  2081944584 handle address   000000007C17F408

根据等待事件“library cache pin”获取“p1 handle address 000000007C17F408”。

关联视图“dba_kgllock dk,v$session”获取锁信息，命令及输出如下：

SQL> select s.sid,s.sql_id,s.event,dk.* from dba_kgllock dk,v$session s where 
    s.saddr = dk.KGLLKUSE and KGLLKHDL='000000007C17F408';

SID SQL_ID       EVENT         KGLLKUSE            KGLLKHDL         KGLLKMOD KGLLKREQ KGLL
--- ------------ ------------- ------------------- ---------------- -------- -------- ----
33  087rrdjwc2act library cache pin 00000000A92FC040 000000007C17F408        3         0  Lock
33  087rrdjwc2act library cache pin 00000000A92FC040 000000007C17F408        0         3  Pin

从以上返回结果中可以看出，我们并没有找到pin的持有者，KGLLKREQ表示当前会话需要申请的锁模式，KGLLKMOD表示当前系统中持有的锁模式，由于该系统为RAC，各节点之间的内存结构不同，handle地址不能公用，因此我们需要定位出owner和object_name在其他节点持有pin的会话。命令及输出如下：

SQL> select ADDR,INDX,INST_ID,KGLHDADR,KGLNAOWN,KGLNAOBJ from x$kglob where 
    KGLHDADR='000000007C17F408';

ADDR             INDX INST_ID KGLHDADR         KGLNAOWN   KGLNAOBJ
---------------- ---- ------- ---------------- ---------- ---------
00007FE9B0B45850 4979       1 000000007C17F408 SYS        DUMMY

其中，x$kglob为“library cache object”对象的视图。

RAC节点2根据object_name查找对应的handle地址信息，命令及输出如下：

SQL> select ADDR,INDX,INST_ID,KGLHDADR,KGLNAOWN,KGLNAOBJ from x$kglob where 
    KGLNAOBJ='DUMMY'

ADDR             INDX INST_ID KGLHDADR         KGLNAOWN  KGLNAOBJ
---------------- ---- ------- ---------------- --------- ---------
00007F987B1D8ED0 4150       2 00000000AA193870 SYS       DUMMY

查看锁的持有情况，命令及输出如下：

SQL> select s.sid,s.sql_id,s.event,dk.* from dba_kgllock dk,v$session s where 
    s.saddr = dk.KGLLKUSE and KGLLKHDL='00000000AA193870';

SID SQL_ID        EVENT             KGLLKUSE         KGLLKHDL         KGLLKMOD KGLLKREQ KGLL
--- ------------  ----------------- ---------------- ---------------- -------- -------- ----
424 d4wnj5j8y1mq7 PL/SQL lock timer 00000000A9787DA0 00000000AA193870        1        0 Lock
424 d4wnj5j8y1mq7 PL/SQL lock timer 00000000A9787DA0 00000000AA193870        2        0 Pin

最终定位节点2上的会话424持有的模式为2（即共享模式）的锁，堵塞了KGLLKREQ 3排它锁的申请，为了能够顺利编译，我们只需要杀掉节点2上的会话424即可。

03 数据误删恢复

在笔者多年的工作经历中，时常会遇到数据被随意篡改或删除的情况，那么在没有备份的情况下又该如何恢复数据呢。

对于drop操作（删除整个表，包括结构和数据），如果没有使用purge参数，那么我们可以使用回收站进行恢复，而对于truncate操作（只删除数据，不删除表的结构），则需要使用非常规的恢复方法，这些不在本书的讨论范围之内，本节将以delete为例演示数据被误删后的恢复。

1. 利用undo闪回查询

根据undo信息，利用前镜像，可以把表置于一个删除前的时间点或SCN（System Change Number），从而找回数据。具体命令如下：

SQL> select * from emp as of timestamp to_timestamp('2019-11-05 08:00:00', 'YYYY-
    MM-DD HH:MI:SS');

但是此方法会受限于undo_retention的配置，默认情况下，undo_retention的值为900秒，即在删除数据900秒之后，undo中的数据会过期。

但如果业务比较繁忙，在undo表空间不足的情况下，即使镜像没有过期，数据也还是会被覆盖。若此时查询就会收到“ORA-08180: no snapshot found based on specified time”的报错信息。

2. logminer挖掘

数据库所有DML（数据操纵语言）的操作都会记录在redo日志中，只要归档文件还存在，那么所有DML的记录都可以找回，使用方法如下。

1）确定DML时间点日志信息，命令如下：

SQL> select t.THREAD#, t.SEQUENCE#, t.NAME
    from v$archived_log t
    where t.FIRST_TIME >=to_date('2019-11-05 10:24:30', 'yyyy-mm-dd hh24:mi:ss')
    and t.NEXT_TIME <=to_date('2019-11-05 14:00:30', 'yyyy-mm-dd hh24:mi:ss');
    THREAD#  SEQUENCE# NAME
---------- ---------- --------------------------------------------------
         1          2 /app_target/easdb_dg/arch/1_2_1023532682.dbf
         1          1 /app_target/easdb_dg/arch/1_1_1023532682.dbf
         1          3 /app_target/easdb_dg/arch/1_3_1023532682.dbf

2）安装logminer安装包，默认系统自带该安装包，安装命令如下：

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
Package created.
Grant succeeded.
Synonym created.

3）添加挖掘日志，添加命令如下：

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/app_target/easdb_dg/arch/
    1_2_1023532682.dbf',options=>dbms_logmnr.new);
<!--继续添加-->
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/app_target/easdb_dg/arch/
    1_1_1023532682.dbf',options=>dbms_logmnr.addfile);
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/app_target/easdb_dg/arch/
    1_3_1023532682.dbf',options=>dbms_logmnr.addfile);

注意：第一个添加日志选项是new，后续添加选项是addfile。

4）开启logminer，命令如下：

SQL> execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

5）查询v$logmnr_contents视图获取挖掘信息，命令如下：

SQL> select sql_redo from v$logmnr_contents where SEG_OWNER='SCOTT';
<!--sql_redo用于记录当时DML的操作记录-->
SQL> select sql_undo from v$logmnr_contents where SEG_OWNER='SCOTT';
<!--若是误操作回退，则可以使用sql_undo，执行还原操作-->

最终，我们可以根据sql_undo进行DML误操作恢复。

关于作者：叶桦，Oracle OCM，MySQL认证专家，超10年乙方数据库维护经验，美创科技运维服务团队负责人。具备丰富的行业经验与技术积累，所服务的对象包括大型运营商、金融机构、政府机关以及制造业等多个行业客户，对于数据库技术具有深刻的理解。精通Oracle和MySQL数据库内核原理、架构规划和调优诊断，擅长Shell和Python自动化运维开发。

徐浩，美创科技运维部经理，Oracle、MySQL、云数据库高级认证专家。拥有8年以上的数据库领域从业经验，TB级高并发数据库与中大型项目的管理经验。对于分布式高可用架构和性能调优有着丰富的实战经验，擅长故障诊断及数据灾难挽救，服务的行业包括运营商、制造业、金融、医疗、政府等。目前，主要负责Oracle、MySQL、阿里云等技术的研究和运维管理，以及数据库智能运维平台的设计开发等工作。

本文摘编自《DBA攻坚指南：左手Oracle，右手MySQL》，经出版方授权发布。