现象：生产库业务用户多次被锁定，锁定后伴有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