一、问题描述
这个五一假日，旅游景点真的是人山人海，不是看景而是看人。于是，索性假日就不去景点，就在家附近逛逛。
假日的一天中午，在家附近上场，接到同事打来电话，有个国外点的生产库应用无法访问，提示应用登陆用户被锁了，让赶紧处理下。
于是火速骑电动车赶回家，打开电脑，看到同事发来的报错信息如下：
根据报错信息，可以清楚的看到是由于用户密码过期导致应用无法连接。
这套生产库已经运行好几年了，之前未曾出现过此情况，之前将密码过期时间设置为unlimited密码永不过期,也取消了密码失败登陆次数限制，不可能出现密码过期问题。
根据领导描述，是另外一个同事在该RAC数据库上又创建了一个实例和相应生产用户，但实例创建后，保持了密码默认策略，未进行修改，该实例已经运行了大半年，出现了密码过期导致应用用户无法登陆。
该新建实例查询到的密码过期策略和登陆次数限制策略如下：
SYS@dsginfo1> set long 99999 head off pages 0 lines 1000
SYS@dsginfo1> select username,account_status ,created from dba_users;
SYSTEM OPEN 2013-08-24 11:37:40
SYS OPEN 2013-08-24 11:37:40
DSXXX EXPIRED 2022-10-26 11:03:59
DSGXXX EXPIRED 2022-10-26 11:03:49
SYS@xxxx> SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
SYS@xxxx> select * from dba_profiles where resource_name = 'FAILED_LOGIN_ATTEMPTS';
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
可以看到密码过期时间是默认180天，密码失败10次即限制登陆。
在修改密码后遇到了连接hang住，并且产生大量library cache lock等待事件。
二、处理过程
首先想查看下两个账号的加密后的密码，希望通过加密后的密码来进行修改，使用如下语句查询：
SYS@silkinfo2> select username,decode(password,NULL,‘NULL’,password) password from dba_users;
MOTORD NULL
SILKINFO NULL
因为该库是11g，通过如上语句查询到的密码都是空。
通过如下方法来设置密码不锁定和重新设置密码策略。
alter user xxx account unlock;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
以上方式设置后仍然无法解决密码过期和应用登陆问题。
此时同事查找到了这两个用户的密码，我使用如下方法对密码进行了修改：
alter user dgxxx identified by "xxxx" account unlock;
alter user dsgxxx identified by "xxxx" account unlock;
修改后赶紧通知应用相关人员检查是否应用可正常连接，应用人员反馈还是无法连接。
我就尝试在数据库端是否可正常登陆数据库，使用conn dsgxxx/xxxx登陆数据库，结果发现无法正常连接，并hang住了。
当时还怀疑是否是因粘贴复制密码有空格之类导致hang，于是又执行如下语句：
alter user dgxxx identified by "xxxx" account unlock;
alter user dgxxx account unlock;
以上两个语句同样都hang住无法执行。
查询下是什么原因导致hang住，通过如下SQL语句查询当前实例有什么等待事件：
[oracle@xxxx ~]$ ora event
INST_ID EVENT# EVENT WAIT_CLASS COUNT(*)
---------- ---------- ---------------------------------------- --------------- ----------
1 287 library cache lock Concurrency 6
1 289 library cache: mutex X Concurrency 1
2 287 library cache lock Concurrency 12
[oracle@xxxxx ~]$ ora active
SID PROCESS SPID USERNAME OSUSER MACHINE PROGRAM SQL_ID EVENT LAST_CALL_ET
---------- -------- -------- ---------- ---------- -------------------- ----------------------------------- ------------- ------------------------------ ------------
2272 23866 23867 SYS oracle xxxxx sqlplus@xxxxx (TNS V1 8v8x5hkap0x3y SQL*Net message to client 0
2552 1234 23228 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 11
574 1234 23098 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 16
4250 1234 23101 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 16
5 1234 22636 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 26
1421 1234 22443 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 31
3690 1234 22245 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 36
3400 1234 21753 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 46
1136 1234 21350 root xxxx-xxx-xxx-xxx JDBC Thin Client gc cr request 56
291 1234 20914 root xxxx-xxx-xxx-xxx JDBC Thin Client library cache lock 66
发现数据库当前产生了大量library cache lock等待事件。
因为时间紧迫，要赶紧处理问题，避免对业务产生更大影响，于是通过如下语句查杀引起library cache lock的会话。
SYS@xxxx> select machine,
2 'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
3 status
4 from v$session
5 where type='USER' and event like 'library cache lock' and status = 'ACTIVE';
MACHINE KILL_SESSION STATUS
-------------------------------------------------- ------------------------------------------------------------ --------
xxx-xxx-xxx-xxx alter system kill session '573,18917'immediate; ACTIVE
xxx-xxx-xxx-xxx alter system kill session '1987,59679'immediate; ACTIVE
xxx-xxx-xxx-xxx alter system kill session '2271,18817'immediate; ACTIVE
xxx-xxx-xxx-xxx alter system kill session '2555,48615'immediate; ACTIVE
xxx-xxx-xxx-xxx alter system kill session '3970,5431'immediate; ACTIVE
......
最后，通过一系列的查杀，终于将引起library cache lock等待事件的相关会话杀掉。
再次使用用户名和密码通过conn可以正常登陆数据库，联系应用人员进行测试，提示应用已可以正常连接。
三、问题总结
该生产库是Oracle 11g库，数据库版本为11.2.0.4，Oracle在11g有个新特性，即有一个用户使用错误的密码连接数据库，随着登陆失败次数的增加，每次登陆验证前延迟等待的时间也会增加，这个新特性的主要目的是用于防止一些应用程序使用错误的密码尝试登陆数据库，由于密码延迟策略，大量会话等待登陆验证，导致数据库后续连接产生大量library cache lock等待事件导致hang住。
此问题可以在MOS上查询(ID 1309738.1)这篇文章，有详细描述。
对于该情况，MOS上给出了可以关闭Oracle 11g的这一密码验证延迟特性，通过如下方式可以关闭，但要重启数据库才生效。
alter system set event= '28401 trace name context off' scope=spfile ;
在本案例中，密码是由项目组提供的，经确认密码是对的，但因为密码过期，在修改密码后，应用反复连接产生了library cache lock等待事件。
在数据库登陆hang住时，其实可通过如下方式详细查看哪些会话产生了library cache lock等待事件：
sqlplus -prelim / as sysdba
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
# 过一两分钟后再执行hanganalyze
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
oradebug -g all dump systemstate 266
通过分析产生的dump文件来详细查看相应会话，但因为时间关系未采用此方法，而是通过sql语句查询相关会话并查杀。
另外，通过如下SQL语句可根据基表查询到对应用户的密码hash值。
select name,password from user$ where name=upper('xxx');