一、问题描述

在外接到一个告警信息，显示一个库无法访问，提示应用登陆用户被锁了，需赶紧处理下。
于是火速骑电动车赶回家，打开电脑，看到同事发来的报错信息如下：

根据报错信息，可以清楚的看到是由于用户密码过期导致应用无法连接。
这套生产库已经运行好几年了，之前未曾出现过此情况，之前将密码过期时间设置为unlimited密码永不过期,也取消了密码失败登陆次数限制，不可能出现密码过期问题。
根据领导描述，是另外一个同事在该RAC数据库上又创建了一个实例和相应生产用户，但实例创建后，保持了密码默认策略，未进行修改，该实例已经运行了大半年，出现了密码过期导致应用用户无法登陆。
该新建实例查询到的密码过期策略和登陆次数限制策略如下：

SYS@dsginfo1> set long 99999 head off pages 0 lines 1000SYS@dsginfo1> select username,account_status ,created from dba_users;SYSTEM                         OPEN                             2013-08-24 11:37:40SYS                            OPEN                             2013-08-24 11:37:40DSXXX                          EXPIRED                          2022-10-26 11:03:59DSGXXX                         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 10MONITORING_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;
XXXX NULL
XXXXXX 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;

以上两个语句同样都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;              ACTIVExxx-xxx-xxx-xxx                    alter system kill session '1987,59679'immediate;             ACTIVExxx-xxx-xxx-xxx                    alter system kill session '2271,18817'immediate;             ACTIVExxx-xxx-xxx-xxx                    alter system kill session '2555,48615'immediate;             ACTIVExxx-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 sysdbaoradebug setorapname recooradebug unlimitoradebug -g all hanganalyze 3# 过一两分钟后再执行hanganalyzeoradebug -g all hanganalyze 3oradebug -g all dump systemstate 266oradebug -g all dump systemstate 266

通过分析产生的dump文件来详细查看相应会话，但因为时间关系未采用此方法，而是通过sql语句查询相关会话并查杀。
另外，通过如下SQL语句可根据基表查询到对应用户的密码hash值。

select name,password from user$ where name=upper('xxx');

ITPub博客

Oracle 11g 库因密码过期修改密码产生library cache lock等待事件导致用户hang问题分析及处理

一、问题描述

二、处理过程

三、问题总结

最新文章