devbox
不正经
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：不正经 | 2024-04-29 08:41:20

赞

踩

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

一、问题描述

这个五一假日，旅游景点真的是人山人海，不是看景而是看人。于是，索性假日就不去景点，就在家附近逛逛。
假日的一天中午，在家附近上场，接到同事打来电话，有个国外点的生产库应用无法访问，提示应用登陆用户被锁了，让赶紧处理下。
于是火速骑电动车赶回家，打开电脑，看到同事发来的报错信息如下：
 

image.png


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

  1. SYS@dsginfo1> set long 99999 head off pages 0 lines 1000
  2. SYS@dsginfo1> select username,account_status ,created from dba_users;
  3. SYSTEM                         OPEN                             2013-08-24 11:37:40
  4. SYS                            OPEN                             2013-08-24 11:37:40
  5. DSXXX                          EXPIRED                          2022-10-26 11:03:59
  6. DSGXXX                         EXPIRED                          2022-10-26 11:03:49
  7.  
  8. SYS@xxxx> SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
  9. DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
  10.  
  11. SYS@xxxx> select * from dba_profiles where resource_name = 'FAILED_LOGIN_ATTEMPTS';
  12. DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
  13. 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，通过如上语句查询到的密码都是空。
通过如下方法来设置密码不锁定和重新设置密码策略。

  1. alter user xxx account unlock;
  2. alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
  3. ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

以上方式设置后仍然无法解决密码过期和应用登陆问题。

此时同事查找到了这两个用户的密码，我使用如下方法对密码进行了修改：

  1.  alter user dgxxx identified by "xxxx" account unlock;
  2.  alter user dsgxxx identified by "xxxx" account unlock;

修改后赶紧通知应用相关人员检查是否应用可正常连接，应用人员反馈还是无法连接。
我就尝试在数据库端是否可正常登陆数据库，使用conn dsgxxx/xxxx登陆数据库，结果发现无法正常连接，并hang住了。
当时还怀疑是否是因粘贴复制密码有空格之类导致hang，于是又执行如下语句：

  1. alter user dgxxx identified by "xxxx" account unlock;  
  2. alter user dgxxx account unlock;

以上两个语句同样都hang住无法执行。

查询下是什么原因导致hang住，通过如下SQL语句查询当前实例有什么等待事件：

  1. [oracle@xxxx ~]$ ora event
  2.  
  3.    INST_ID     EVENT# EVENT                                    WAIT_CLASS        COUNT(*)
  4. ---------- ---------- ---------------------------------------- --------------- ----------
  5.          1        287 library cache lock                       Concurrency              6
  6.          1        289 library cache: mutex X                   Concurrency              1
  7.          2        287 library cache lock                       Concurrency             12
  8.  
  9.  
  10. [oracle@xxxxx ~]$ ora active
  11.  
  12.        SID PROCESS  SPID     USERNAME   OSUSER     MACHINE              PROGRAM                             SQL_ID        EVENT                          LAST_CALL_ET
  13. ---------- -------- -------- ---------- ---------- -------------------- ----------------------------------- ------------- ------------------------------ ------------
  14.       2272 23866    23867    SYS        oracle     xxxxx                sqlplus@xxxxx (TNS V1 8v8x5hkap0x3y SQL*Net message to client                 0
  15.       2552 1234     23228               root       xxxx-xxx-xxx-xxx JDBC Thin Client                                  library cache lock                       11
  16.        574 1234     23098               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  library cache lock                       16
  17.       4250 1234     23101               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  library cache lock                       16
  18.          5 1234     22636               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  library cache lock                       26
  19.       1421 1234     22443               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  library cache lock                       31
  20.       3690 1234     22245               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  library cache lock                       36
  21.       3400 1234     21753               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  library cache lock                       46
  22.       1136 1234     21350               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  gc cr request                            56
  23.        291 1234     20914               root       xxxx-xxx-xxx-xxx  JDBC Thin Client                                  library cache lock                       66

发现数据库当前产生了大量library cache lock等待事件。
因为时间紧迫，要赶紧处理问题，避免对业务产生更大影响，于是通过如下语句查杀引起library cache lock的会话。

  1. SYS@xxxx> select machine,                                                                                                                          
  2.   2         'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,                                    
  3.   3         status                                                                                                                            
  4.   4    from v$session                                                                                                                         
  5.   5   where type='USER' and event like 'library cache lock' and status = 'ACTIVE'; 
  6.  
  7. MACHINE                            KILL_SESSION                                                 STATUS
  8. -------------------------------------------------- ------------------------------------------------------------ --------
  9. xxx-xxx-xxx-xxx                    alter system kill session '573,18917'immediate;              ACTIVE
  10. xxx-xxx-xxx-xxx                    alter system kill session '1987,59679'immediate;             ACTIVE
  11. xxx-xxx-xxx-xxx                    alter system kill session '2271,18817'immediate;             ACTIVE
  12. xxx-xxx-xxx-xxx                    alter system kill session '2555,48615'immediate;             ACTIVE
  13. xxx-xxx-xxx-xxx                    alter system kill session '3970,5431'immediate;              ACTIVE	
  14. ......

最后，通过一系列的查杀，终于将引起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等待事件：

  1. sqlplus -prelim / as sysdba
  2. oradebug setorapname reco
  3. oradebug unlimit
  4. oradebug -g all hanganalyze 3
  5. # 过一两分钟后再执行hanganalyze
  6. oradebug -g all hanganalyze 3
  7. oradebug -g all dump systemstate 266
  8. oradebug -g all dump systemstate 266

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

select name,password from user$ where name=upper('xxx');
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/506846
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号