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

Last updated on AUGUST 07, 2024

Applies to:

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(*) failed_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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.