下载掌阅APP，畅读海量书库
立即打开
DBA的大部分工作都是围绕着对数据库的维护而展开的，常规的日常维护更是占了绝大多数。本节将围绕日常维护中最常见的三个案例展开讲解，与大家分享排查此类问题的思路。
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”命令快速清理会话。需要注意的是，在查杀会话之前一定要再三确认信息，千万不要误杀了系统核心进程。
想必大家都遇到过这样的情况，在业务高峰期如果编译存储过程、函数或视图，就会导致大量使用该对象的会话堵塞，自身也将处于挂起状态，后台等待事件为“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即可。
在笔者多年的工作经历中，时常会遇到数据被随意纂改或删除的情况，那么在没有备份的情况下又该如何恢复数据呢。对于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误操作恢复。