11g密碼錯誤延時造成大量"library cache lock"等待

startay發表於2016-02-01
密碼

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:

 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: 

 - 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."

 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章