11g密码错误延时造成大量"library cache lock"等待

impdp数据迁移后，发现大量'Library cache lock'等待事件，username，sql_id都是空值。


通过分析，怀疑是oracle 11g 新特性密码错误验证延迟导致的。

可开启28401时间。屏蔽密码错误验证延迟功能。
SQL> alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;
后来数据库正常了。


下面是分析与处理方法。

• '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:
    

          

• 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(*) failed_logins
from  dba_audit_trail
where returncode = 1017
and timestamp > sysdate - 7
group by username, os_username, userhost, client_id, trunc(timestamp);

USERNAME   OS_USERNAME      USERHOST         CLIENT_ID        TRUNC(TIMEST FAILED_LOGINS
---------- ---------------- ---------------- ---------------- ------------ -------------
POS5G_RW   weblogic         cnpos5gpt2ap1                     30-JAN-16             2127
POS5G_RW   weblogic         cnpos5gpt2sp2                     30-JAN-16             2831
POS5G_RW   weblogic         cnpos5gpt2sp2                     29-JAN-16             1054
POS5G_RW   weblogic         cnpos5gpt2sp1                     31-JAN-16                2
POS5G_RW   weblogic         cnpos5gpt2ap2                     01-FEB-16              162
POS5G_RW   weblogic         cnpos5gpt2sp1                     29-JAN-16              419
POS5G_RW   weblogic         cnpos5gpt2sp2                     01-FEB-16              202
POS5G_RW   weblogic         cnpos5gpt2ap1                     31-JAN-16                1
POS5G_RW   weblogic         cnpos5gpt2ap2                     30-JAN-16             2341
POS5G_RW   weblogic         cnpos5gpt2sp1                     01-FEB-16               80
POS5G_RW   weblogic         cnpos5gpt2ap1                     01-FEB-16              126

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 ;

For more information see:

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

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

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

补充从10g升级到11g之后需要注意的几个密码方面问题：

1. 11g默认开始密码区分大小写，可以通过把参数设置为SEC_CASE_SENSITIVE_LOGON =FALSE 屏蔽

2. 11g密码默认有效期180天，可以通过修改ALTER PROFILE DEFAULT[根据实际的profile] LIMIT PASSWORD_LIFE_TIME UNLIMITED;   注意需要修改密码生效

3. 密码错误验证延迟，可以通过设置EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" 屏蔽

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

转载于:http://blog.itpub.net/17086096/viewspace-1985279/