There were massive contention in the shared pool, in form of wait events alerted as "library cache locks".Sufficient resource were available for instance to work.
Issue was caused by an incorrect password configuration on the application server/Discoverere.
Observation:
Shared pool were totally exhausted, caused by "library cache lock".
View V$EVENT_NAME showed that the wait event was accompanied by the additional information found in the columns parameter1 through parameter3,
which turned out to be helpful further on:
select name, wait_class,parameter1,parameter2,parameter3
from v$event_name
where wait_class = 'Concurrency'
and name = 'library cache lock';
NAME WAIT_CLASS PARAMETER1 PARAMETER2 PARAMETER3
library cache lock Concurrency handle address lock address 100*mode+namespace
Was unable to login as below as well:
[oracle@~]$ sqlplus apps/*****
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 10 10:41:14 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ ~]$
On drilldown found that the issue was due to a built-in delay between failed login attempts in Oracle 11g.
"The 'library cache lock' wait is seen due to the fact that the account status gets updated due to incorrect login.
To prevent password guessing attack, there's a sleep() in the code when incorrect login attempts exceed count of 3.
And because of this sleep() you see a wait on library cache, as the process is yet to release the lock."
11.1.0.7, patch 7715339 was released to remove this delay.
11.2.X, the DBA must set an event to remove the delay, as follows:
alter system set events '28401 trace name context forever, level 1';
According to Oracle, the purpose of the built-sleep is to make it harder to succeed in a "password guessing attack", particularly in cases where FAILED_LOGIN_ATTEMPTS is set to UNLIMITED. Oracle Development is pointing out that disabling the sleep-function is not recommended. A better solution is to set the FAILED_LOGIN_ATTEMPTS to a reasonable value. When the number of failed login attempts for a session hits the limit, the account will be locked. Subsequent logon attempts with incorrect password will then be rejected immediately without any contention in the library cache.
Bug 15882590 : 'LIBRARY CACHE LOCK' DURING WRONG PASSWORD LOGON
Issue was caused by an incorrect password configuration on the application server/Discoverere.
Observation:
Shared pool were totally exhausted, caused by "library cache lock".
View V$EVENT_NAME showed that the wait event was accompanied by the additional information found in the columns parameter1 through parameter3,
which turned out to be helpful further on:
select name, wait_class,parameter1,parameter2,parameter3
from v$event_name
where wait_class = 'Concurrency'
and name = 'library cache lock';
NAME WAIT_CLASS PARAMETER1 PARAMETER2 PARAMETER3
library cache lock Concurrency handle address lock address 100*mode+namespace
Was unable to login as below as well:
[oracle@~]$ sqlplus apps/*****
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 10 10:41:14 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ ~]$
On drilldown found that the issue was due to a built-in delay between failed login attempts in Oracle 11g.
"The 'library cache lock' wait is seen due to the fact that the account status gets updated due to incorrect login.
To prevent password guessing attack, there's a sleep() in the code when incorrect login attempts exceed count of 3.
And because of this sleep() you see a wait on library cache, as the process is yet to release the lock."
11.1.0.7, patch 7715339 was released to remove this delay.
11.2.X, the DBA must set an event to remove the delay, as follows:
alter system set events '28401 trace name context forever, level 1';
According to Oracle, the purpose of the built-sleep is to make it harder to succeed in a "password guessing attack", particularly in cases where FAILED_LOGIN_ATTEMPTS is set to UNLIMITED. Oracle Development is pointing out that disabling the sleep-function is not recommended. A better solution is to set the FAILED_LOGIN_ATTEMPTS to a reasonable value. When the number of failed login attempts for a session hits the limit, the account will be locked. Subsequent logon attempts with incorrect password will then be rejected immediately without any contention in the library cache.
Bug 15882590 : 'LIBRARY CACHE LOCK' DURING WRONG PASSWORD LOGON
