3个最常见案例详解DBA日常维护

骑猪看日落 2022-08-29 10:11 119阅读 0赞

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

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

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

![7bf9d3f3bb696be6e15986212ee87293.png][]

**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';

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

从代码中我们可以发现，当两个会话的状态为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> select sql_undo from v$logmnr_contents where SEG_OWNER='SCOTT';

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

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

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

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

![4724ecc3c1ae026a5ff25a7f2c91dc8c.png][]