ORACLE-问题处理（27）
&&& 上周CD渠道的ZSL联系我，让我看一下他在论坛上发了一个求助帖《》，告知我这是工作中实际遇到的问题，由于这个原因，用户的数据已经几天没有备份了，用户的数据安全无法得到保障，希望我能协助帮忙处理，由于***指导比较麻烦，用户又有远程环境，于是我提议进行远程处理。
&&& 远程桌面连接到用户处，首先得看一下问题现象，做了一个全库的逻辑备份，确实如其描述的一样，当逻辑备份执行到如下图所示的位置，就一直Hang在那里，几分钟都没有动弹，导致全库备份无法成功。
&&& 通过现象，我们可以分析这是一个典型的资源等待导致，因此我们需要先从ORACLE的等待事件中查找导致问题的原因。
&&& 首先肯定是要找到该逻辑导出的会话，通过查询V$SESSION视图，指定program字段为‘exp.exe’执行文件查找，得到结果如下图：
&&& 通过查询的结果，发现居然有2条记录，不过状态不同，sid为148的记录状态是KILLED,sid为213的记录状态为ACTIVE,询问现场工程师，得知148是其几天前执行逻辑备份时，由于有其他操作，技术人员手工直接杀掉会话导致，但是在视图中，其一直保持KILLED状态，而sid为213这个会话是当前执行的逻辑备份会话。经过观察，确实如其所说，接下来我们就需要重点分析下sid为213这个会话，为什么其会hang住，我们需要查下他目前是在等待什么操作，这个我们可以通过查询v$session_wait会话查看,查询下sid为213的会话目前的等待事件为什么，结果如图：
&&& 这里我们看到了一个等待事件‘library cache lock’,那么什么情况下会出现library cache lock，这里我们首先要了解什么是Library Cache。
&&& Library Cache是Oracle SGA 中Shared pool 的组成部分，它几乎是Oracle内存结构中最复杂的一部分，主要存放shared curosr（SQL）和PLSQL对象（function，procedure，trigger）的信息，以及这些对象所依赖的table，index，view等对象的信息，Library cache主要解决这些对象的三个问题：1.快速定位，2.关系依赖，3.并发控制，其中并发控制就会涉及到library cache lock，当会话访问对象时，首先必须获取lock，然后将访问的数据pin在内存中。lock的作用是控制进程间的并发访问，而pin的作用是保证数据一致性，防止数据在访问时被交换出去，了解到了这个情况，我们就需要查询到是那个会话把该逻辑备份的会话堵塞，只要找到这个堵塞会话，然后加以解决，相信该Hang的问题就能够随之解决。
&&& 如前面提到的，首先是找到什么会话导致library cache lock ，这里我们需要用到metalink的一篇文章《How to Find which Session is Holding a Particular Library Cache Lock [ID ]》，该文详细介绍了如何找到导致holding的会话，大意是说要使用的到oracle的x$内部表，有兴趣的同学可以自己查看下该文章，我这里直接引用。
&&& x$表是oracle的内部核心表，只有sys用户连接数据库才能访问，通过下面的一段SQL查出堵塞会话。
&&& 这里我眼前一亮，SID为148的会话，不正事前面我们查询出来状态为KILLED的会话吗？看来已经确定是该会话导致后续的exp堵塞，那既然已经杀掉的会话，为什么还会堵塞呢？这里我们有必要说明下oracle的kill会话的操作。
&& 在Oracle数据库中,可以通过kill session的方式来终止一个进程,语法为:
alter system kill session 'sid,serial# '，但是alter system kill session只是将session标记为可以回收，切断会话与Server Process的映射关系。但没有进行资源释放回收工作，一旦尝试连接，PMON会主动开始清理被kill的会话，同时Oracle拒绝连接操作，Server Process是一个忠实于客户端的进程，只要客户端还在启动，维持着两个之间的联系。Server Process是不会被回收的。直到客户端主动停止与Server
Process的通信，Server Process才释放资源。如何才能彻底释放资源呢？其实我们只要在操作系统层面上，结束该会话的进程就行了，首先要找到进程号，由于被置于KILLED状态的会话，该session的paddr被修改，因此无法通过v$session和v$process关联来获得spid，但是可以通过下面的SQL语句，来进行查找:
&&& 知道了会话的spid后，就可以通过orakill工具直接在操作系统命令模式下，结束掉该会话。
&&& C:\&orakill orcl 1639
&&& Kill of thread id 1639 in instance orcl successfully signalled
&&& 然后再查询v$session,可以看见killed状态的会话已经消失了，堵塞会话已经彻底清除，orakill是oracle提供的windows平台下强制结束oracle进程的工具，这方面的知识有兴趣的同学查看metalink文档《ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID ]》，接着就赶快再试试exp导出是否还会Hang住。
&&& 立即执行下exp导出，验证下问题是否已经得到解决，正常情况下，在导出系统对象和过程时也会停顿一段时间，这个时候，查询下v$session_wait视图，可以看到等待事件为SQL*Net message from client如下图:
&&& SQL*Net message from client属于空闲等待，如图，等待类型属于IDLE，已经不再是Library Cache Lock，一般情况下可以不用理会，属于正常的等待事件。过了大概半分钟，开始看到数据表的导出，此致问题得到圆满的解决。
&&&&通过本文的介绍，我们不光晓得本例中问题的解决方法，还应该基本了解到处理ORACLE一般Hang住问题的基本思路：
1.首先确定Hang住会话的等待事件。。
2.然后分析该等待事件，找到引起该等待的原因，是由于锁，还是由于资源繁忙？
3.最后根据其产生的原因，排除问题，一般都能够解决问题。
&&&& 希望本例的介绍对大家在以后处理类似业务有所帮助。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：142534次
积分：2435
积分：2435
排名：第11743名
原创：100篇
转载：20篇
评论：11条
(1)(3)(4)(1)(2)(1)(2)(1)(2)(3)(1)(4)(4)(4)(1)(2)(10)(7)(11)(11)(1)(2)(4)(1)(2)(4)(1)(1)(1)(1)(5)(1)(4)(1)(1)(1)(6)(3)(10)现象：生产库业务用户多次被锁定，锁定后伴有library cache lock问题
应急措施：
1、查询library cache lock等待事件的blocking_session，考虑这些blocking_session对应的sid为业务机器连到数据库等待验证的错误密码的session，通过查询v$session查询业务机器是哪台，让开发人员排查程序；
2、通过kill -9杀掉操作系统层面导致library cache lock的session
3、解锁用户 alter user use
事后排查方法：
1、查看listener.log 数据库锁定时的用户发起的连接，将截取的日志部分发给开发，让开发部门排查程序；
2、写记录用户登陆失败的触发器备下次再出现这样的问题使用，
CREATE OR REPLACE TRIGGER logon_denied_to_alert
AFTER servererror ON DATABASE
message VARCHAR2(168);
ip VARCHAR2(15);
v_os_user VARCHAR2(80);
v_module VARCHAR2(50);
v_action VARCHAR2(50);
v_pid VARCHAR2(10);
v_sid NUMBER;
v_program VARCHAR2(48);
IF (ora_is_servererror(1017)) THEN
-- get ip FOR remote connections :
IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
ip := sys_context('userenv', 'ip_address');
SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum & 2;
SELECT p.spid, v.program
INTO v_pid, v_program
FROM v$process p, v$session v
WHERE p.addr = v.paddr
AND v.sid = v_
v_os_user := sys_context('userenv', 'os_user');
dbms_application_info.read_module(v_module, v_action);
message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
' logon denied from ' || nvl(ip, 'localhost') || ' ' ||
v_pid || ' ' || v_os_user || ' with ' || v_program || ' - ' ||
v_module || ' ' || v_
sys.dbms_system.ksdwrt(2, message);
事实上这个触发器基本没什么用，当错误密码并发登录一来，整个数据都hang住了，v$session视图根本查不动，上面的触发器根本没法用。
当然此时最头疼的就是为什么错误密码的连接一并发登陆，就会有大量的library cache lock。在我的印象里，只有当大量并发sql去library cache获取handle时才会产生library cache lock，为什么错误密码的连接会导致大量的library cache lock，而且这些等待事件都没有sql_id，好奇怪，想不通。
查询metalink，得到了下面的结果
Library Cache Locks Due to Invalid Login Attempts (Doc ID )
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.
Set the below event in the spfile or init.ora file and restart the database:
alter system set event =&28401 TRACE NAME CONTEXT FOREVER, LEVEL 1& scope=
EVENT=&28401 TRACE NAME CONTEXT FOREVER, LEVEL 1&
好吧，我竟无言以对！
上面的原因是：11g延迟密码验证新特性，在输入错误的密码后，后续如果还是采用错误的密码登陆，将会导致密码延迟验证，而且会导致失败登陆延长。当有并发登陆失败就会造成library
cache lock。（具体是为什么我还在想）
考虑到上面问题解决办法效率不高，终极解决办法就横空出世了：
oracle的审计功能！还是我们了解不太多的东西，当时人家好用啊。
首先呢，11g是默认开启db级别的审计的，如果不太确定是否开启，什么级别：show parameter audit
但是我自己做了个实验：确认我的库是开启db审计，但是我故意输错密码，在aud$并未查到相关的记录。。。。有点灰心。
本着试试的心态问题百度，哈哈居然有结果。
虽然数据库审计是开着的，但还需要开启对试图尝试口令的访问的审计
ALL BY ACCESS WHENEVER NOT SUCCESSFUL
开启之后，再做几个实验，通过
&select&sessionid,&
userhost,&
comment$text,&
to_char(ntimestamp#&+&1&/&3,&'yyyy-mm-dd&hh24:mi:ss')&
from&sys.aud$&a&
where&a.ntimestamp#&&&sysdate-3&
and&returncode&=&1017&
order&by&ntimestamp#& &
能够查询到业务机器错误密码的登陆记录。万事大吉！
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &文章不太详细 &需要技术支持或是技术沟通，请联系 & & 王杰 &
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &夏家祥 QQ：
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1351次
排名：千里之外
