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:42
  • 6528

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

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

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

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

Oracle Library Cache Lock 解决思路

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

一次大量Library Cache lock的处理

上周六一个项目反馈数据查询非常慢,单个数据索引查询平时只需要几秒,进行查询时1个小时还未出结果. 登陆项目数据库服务器,查看em,等待事件中大量的Library Cache lock; 第...
  • ousingo
  • ousingo
  • 2013-08-20 17:13:17
  • 317

library cache lock 相关的bug list

NB Prob Bug Fixed Description II 5650841 Hang / deadlock from...
  • royjj
  • royjj
  • 2017-02-02 21:34:05
  • 663

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

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

Library cache内部机制详解

library cache: mutex X替代了之前的library cache latch,主要作用是在hash bucket中定位handle时使用。(比如SQL硬解析时,需要往hash buc...
  • notbaron
  • notbaron
  • 2016-03-16 23:51:35
  • 30318

Oracle内存详解之二 Library cache 库缓冲

Library cache是Shared pool的一部分,它几乎是Oracle内存结构中最复杂的一部分,主要存放shared curosr(SQL)和PLSQL对象(function,procedu...
  • ZengMuAnSha
  • ZengMuAnSha
  • 2011-09-02 22:15:36
  • 8078

11g library cache lock分析思路

create or replace procedure prc_test1 is begin loop execute immediate 'select * from dual'; end...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2014-09-14 11:24:07
  • 588
收藏助手
不良信息举报
您举报文章:oracle 11g 多次业务用户被锁定 library cache lock导致数据hang住问题解决
举报原因:
原因补充:

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