Library cache lock Due to failed logins (Concurrency in db)

Application users with wrong password try to login to the database continuously and it’s causing Concurrency in db due to Library cache locks

Blocking session and event details

  SID    BLOCKING_SESSION  EVENT         

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

  3130          4127      library cache lock  

Blocking session connection details using PID

 $ps -ef | grep 6311

Oracle 6311 1 3 21:10 00:00:01 oraclejcraju(LOCAL=NO)-nonlocal connection    

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

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

      1794          116       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; 

USERNAME OS_USERNAME USERHOST CLIENT_ID TRUNC(TIMESTAMP)       FAILED_LOGINS

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

JCR            oracle             jcraju.prod.com                 8/25/2018 1474

JCR            oracle             jcraju.prod.com                 8/25/2018 1483

Solution:

Correct user password in configuration or reset it to correct one,