密码错误频繁登录引发的“library cache lock”或“row cache lock”等待

密码错误频繁登录引发的”library cache lock”或”row cache lock”等待

---

---

对于正常的系统，由于密码的更改，可能存在某些被遗漏的客户端，不断重复尝试使用错误密码登录数据库，从而引起数据库内部长时间的”library cache lock”或”row cache lock”的等待，这种情形非常常见。这种现象在Oracle 10.2和11.1中体现的等待事件为：”row cache lock”，而在Oracle 11.2中体现的等待事件为：”library cache lock”。

row cache lock等待事件的处理：http://blog.itpub.net/26736162/viewspace-2139754/

---

---

在 Oracle 11g 中，为了提升安全性，Oracle 引入了『密码延迟验证』的新特性。这个特性的作用是，如果用户输入了错误的密码尝试登录，那么随着登录错误次数的增加，每次登录前验证的时间也会增加，以此减缓可能对于数据库重复的口令尝试攻击。

但是对于正常的系统，由于口令的更改，可能存在某些被遗漏的客户端，不断重复尝试，从而引起数据库内部长时间的 Library Cache Lock的等待，这种情形非常常见。

如果遇到这一类问题，可以通过Event 28401关闭这个特性，从而消除此类影响，以下命令将修改设置在参数文件中：

ALTER SYSTEM SET EVENT =

 '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;

出现这类问题非常典型的AWR报告呈现如下，首先在 TOP 5 中，你可能看到显著的 Library Cache Lock 的等待，以下范例来自11.2.0.3.0版本的真实情况：

在这类情况下，时间模型 - Time Model 中会显示如下指标，其中 connection management call elapsed time 占据了主要的DB Time，这个等待直接表明是在建立数据库连接时产生的：

这类问题，在Oracle的11g中是常见和确定的，在MOS上可以找到相应的记录：High 'library cache lock' Wait Time Due to Invalid Login Attempts(1309738.1）此外Oracle 11g开启了密码大小写验证，如果从Oracle 10g升级过来，需要特别的当心这个变化，通过初始化参数SEC_CASE_SENSITIVE_LOGON 可以来控制这个特性。

---

---

High 'library cache lock' Wait Time Due to Invalid Login Attempts (文档 ID 1309738.1)

类型: 状态: 上次主更新: 上次更新:

PROBLEM PUBLISHED 2017-8-2 2017-8-2

In this Document

Symptoms

Changes

Cause

Solution

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.5 and later
Information in this document applies to any platform.

SYMPTOMS

 

• 'Library cache lock' or 'row cache lock' can be observed when concurrent users login with wrong password to the database.
• The 'row cache lock' is seen in 10.2 and 11.1 while the 'library cache lock' is seen in 11.2.
• ASH Report displays
  • High Percentage of execution time attributed to Connection Management:
    
    
    
  • High percentage of calls of type OAUTH
    
    
    
    
• Stack contains one of the following functions: 
  
  kziavua
  kziaia  
  kziasfc
   
• Checking the exclusive holder from DBA_DDL_LOCKS,  a session may be seen holding a lock type (kglhdnsp) 79 on object (kglnaobj) 5:
  

  SQL> select * from dba_ddl_locks where mode_held='Exclusive';

   SESSION_ID OWNER     NAME       TYPE       MODE_HELD MODE_REQU

  ---------- --------- ---------- ---------- --------- ---------

         612           5          79         Exclusive None

 

• If AUDIT_TRAIL is enabled, login failures can be checked by running SQLs similar to the following:
  

  Checks for entries in the last 7 days in DBA_AUDIT_TRAIL with error ORA-1017  invalid username/password; logon denied

  select username, os_username, userhost, client_id, trunc(timestamp), count(*) fa
  iled_logins
  from  dba_audit_trail
  where returncode = 1017
  and timestamp > sysdate - 7
  group by username, os_username, userhost, client_id, trunc(timestamp);

  
  Checks for entries in the last 7 days in DBA_AUDIT_SESSION where an error was returned

  select username, os_username, userhost, timestamp, returncode
  from sys.dba_audit_session
  where returncode != 0 
  and timestamp > sysdate - 7;

  
  

CHANGES

Many users with wrong password try to login to the database simultaneously

CAUSE

A hang is possible in earlier versions of RDBMS as a result of an unpublished bug fixed in the following versions:

    12.1.0.1 (Base Release)
    11.2.0.2 (Server Patch Set)
    11.1.0.7 Patch 42 on Windows Platforms

Document 9776608.8  Bug 9776608 - Hang from concurrent login to same account with a wrong password

Even with this fix, numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits. 

This was reported in:

Bug 11742803  LOTS OF 'LIBRARY CACHE LOCK' DURING USER LOGON AUTHENTICATION

This was closed as not a bug because there is an intentional wait when a login fails.

SOLUTION

In Oracle 11g Release 11.1.0.7,  the wait is disabled unconditionally

In Oracle 11g Release 2 and higher, in order to disable the wait between login failures the event 28401 needs to be explicitly enabled:

The event can be set as follows:

alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

To unset the event, set as follows:

SQL> Alter system set event= '28401 trace name context off' scope=spfile ;

For more information see:

Document:7715339.8  Logon failures causes "row cache lock" waits - Allow disable of logon delay

The following functions match per above bug:

nanosleep nanosleep kziasfc kpolnb kpoauth

Note: Care should be taken when setting this event, as this is disabling the sleep time which can leave the system more vulnerable.

For databases using MTS, a further enhancement has been created in: 

Bug 19867671  - LIBRARY CACHE LOCK CAUSED BY WRONG PASSWORD LOGIN <=====This bug is superseded by following bug:
Bug 20016491  : DB12.2SEC: LOCK_DATE NOT RESET EVEN AFTER A/C UNLOCKED POST PWD_LOCK_TIME ELAPSE

as setting the event is not as effective as it is for dedicated systems. The following enhancement is included in 12.2:

"The failed login counter will be disabled entirely for any user that has the setting UNLIMITED for their account's FAILED_LOGIN_ATTEMPTS password profile setting."

Bug:19867671  LIBRARY CACHE LOCK CAUSED BY WRONG PASSWORD LOGIN

To find which Session is Holding a Particular Library Cache Lock, review following note:

Document 122793.1  How to Find which Session is Holding a Particular Library Cache Lock

 

 

 

REFERENCES

BUG:11742803  - LOTS OF 'LIBRARY CACHE LOCK' DURING USER LOGON AUTHENTICATION

NOTE:7715339.8  - Bug 7715339 - Logon failures causes "row cache lock" waits - Allow disable of logon delay
BUG:19867671  - LIBRARY CACHE LOCK CAUSED BY WRONG PASSWORD LOGIN

---

---

---

---

About Me

............................................................................................................................................. ● 本文整理自网络 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友（646634621），注明添加缘由 ● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。    小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店 .............................................................................................................................................

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2143106/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2143106/