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包括了不同类型对象的描述,如:游标,索引,表,视...

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

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

深入理解Oracle中的shared pool与library cache组件及相关等待事件

传统的’library cache pin’在10.2.0.2之后默认被取代, 此处PIN被Mutex及其ref count取代。 当进程执行游标语句时或者需要PIN,或者需要hard parse一个...

Windows 7下安装Oracle 11g相关问题解决方法

在Windows 7旗舰版上安装Oracle 11g在“产品特定的先觉条件检查”时,会提示错误,是验证操作系统时出错,错误如下:在第一幅图片中,我们可以看到在Oracle在检查操作系统兼容性时,没有通...

[Oracle]--Library cache lock 故障解决一例 oracle10g

今天收到同事电话,说是数据库中一张名为acct_balance进行操作是奇慢,第一反映是不是扫行计划有问题,结果我错了,现将过程记录下来。 用pl/sql连上数据库情况:1、对acct_balanc...

Oracle11G密码延迟验证导致的系统HANG住

Oracle11G密码延迟验证导致的系统HANG住 870阅读 0评论2015-01-22 n-lauren 分类:Oracle 环境:AIX-Based Systems   ...

oracle 11g 用户密码过期问题

1、Oracle 11g 之前默认的用户时是没有密码过期的限制的,在Oracle 11g 中默认的profile启用了密码过期时间是180天。如下查看有效期:select * from dba_pro...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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