oracle 11g 多次业务用户被锁定 library cache lock导致数据hang住问题解决

原创 2015年04月24日 15:57:12

现象:生产库业务用户多次被锁定,锁定后伴有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 username account unlock;

事后排查方法:

1、查看listener.log 数据库锁定时的用户发起的连接,将截取的日志部分发给开发,让开发部门排查程序;

2、写记录用户登陆失败的触发器备下次再出现这样的问题使用,

CREATE OR REPLACE TRIGGER logon_denied_to_alert
AFTER servererror ON DATABASE
DECLARE
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);
BEGIN
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');
END IF;

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_sid;

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_action;

sys.dbms_system.ksdwrt(2, message);

END IF;
END;
/

事实上这个触发器基本没什么用,当错误密码并发登录一来,整个数据都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 1309738.1)

Cause
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=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"


好吧,我竟无言以对!

上面的原因是:11g延迟密码验证新特性,在输入错误的密码后,后续如果还是采用错误的密码登陆,将会导致密码延迟验证,而且会导致失败登陆延长。当有并发登陆失败就会造成library cache lock。(具体是为什么我还在想)


考虑到上面问题解决办法效率不高,终极解决办法就横空出世了:

oracle的审计功能!还是我们了解不太多的东西,当时人家好用啊。

首先呢,11g是默认开启db级别的审计的,如果不太确定是否开启,什么级别:show parameter audit

但是我自己做了个实验:确认我的库是开启db审计,但是我故意输错密码,在aud$并未查到相关的记录。。。。有点灰心。

本着试试的心态问题百度,哈哈居然有结果。

虽然数据库审计是开着的,但还需要开启对试图尝试口令的访问的审计

SQL>AUDIT ALL BY ACCESS WHENEVER NOT SUCCESSFUL

开启之后,再做几个实验,通过

 select sessionid, 
userid, 
userhost, 
comment$text, 
spare1, 
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# desc;  
能够查询到业务机器错误密码的登陆记录。万事大吉!


                                                                                                                 文章不太详细  需要技术支持或是技术沟通,请联系     王杰  15314117200

                                                                                                                                                                                               夏家祥 QQ:592379255


Library cache lock/pin详解

Library cache lock/pin 一、概述 ---本文是网络资料加metalink 等整理得来 一个实例中的library cache包括了不同类型对象的描述,如:游标,索引,表,视...
  • xiaofan23z
  • xiaofan23z
  • 2012年06月28日 16:10
  • 6251

Oracle 11g下重现library cache lock等待事件

从下面的例子中可以看到,在生产数据库中对象的重新编译会导致library cache lock,所以应该尽量避免在业务高峰期编译对象。如果是package或过程中存在复杂的依赖关系就极易导致libra...
  • guogang83
  • guogang83
  • 2014年08月11日 11:43
  • 1413

Oracle Library Cache Lock 解决思路

一.  Library Cache Lock    Library cacheHandle 里保存了lock 和 pin 的信息。而且在Library cache handle 和child curs...
  • tianlesoftware
  • tianlesoftware
  • 2012年09月07日 23:26
  • 26971

彻底搞清楚library cache lock的成因和解决方法(一)

问题描述:接到应用人员的报告,说是在任何对表CSNOZ629926699966的操作都会hang,包括desc CSNOZ629926699966,例如:ora9i@cs_dc02:/ora9i > ...
  • lunar2000
  • lunar2000
  • 2005年01月10日 19:35
  • 11761

几种常见的library cache lock产生的原因

几种常见的library cache lock产生的原因 By Zhangqm-Oracle on 十二月 04, 2014 常见的library cache lock产生的原...
  • su377486
  • su377486
  • 2015年09月30日 00:43
  • 867

彻底搞清楚library cache lock的成因和解决方法

  • 2010年05月18日 23:04
  • 54KB
  • 下载

11g 密码错误延迟验证引发的library cache lock

问题发生在一个用户上,使用这个用户登录需要等待很长时间,而使用其他的用户登录则不存在问题,查看等待事件发现: SQL> set linesize 200 SQL> select sid,p1, P1...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2014年09月03日 20:31
  • 806

彻底搞清楚library cache lock的成因和解决方法(二)

上一个例子中我们主要借助于X$KGLLK基表和event systemstate解决问题,那么如果你不了解X$KGLLK基表,或者忘记了如何使用它,那也不要紧张,这里再介绍一种常规的方法。从syste...
  • lunar2000
  • lunar2000
  • 2005年01月10日 20:07
  • 2662

2013-12-26一次library cache lock的诊断--OEM引发的

公司内有一个系统普遍慢,对于这种普遍慢的情况,就看AWR报告,晚上在用户不适用的情况下负载都很高(有4个逻辑CPU),可以看到library cache lock的占比非常大。 SQL> select...
  • guogang83
  • guogang83
  • 2013年12月27日 09:45
  • 1375

【翻译自mos文章】找到持有library cache lock session的方法

【翻译自mos文章】找到持有library cache lock session的方法
  • msdnchina
  • msdnchina
  • 2015年06月14日 19:49
  • 1074
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle 11g 多次业务用户被锁定 library cache lock导致数据hang住问题解决
举报原因:
原因补充:

(最多只允许输入30个字)