NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

Not able to connect to Apps user in Database due to library cache locks

2 comments
If there are many applications integrated with EBS then this issue arises, when we change apps password but the integrated applications keep trying to connect with old password causes library cache locks in the Database.
This prevent apps user from connecting to database.This scenario also arises when EBS instance is refreshed.
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.

We can observe  'Library cache lock' or 'row cache lock' when concurrent users login with wrong password to the database.
'row cache lock' is seen in 10.2 and 11.1
'library cache lock' is seen in 11.2.

Solution

1. Check for bad or incorrect password or login attack by running following sql:

select username,
os_username,
userhost,
client_id,
trunc(timestamp),
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
timestamp < sysdate -7
group by username,os_username,userhost, client_id,trunc(timestamp);

 
2. Set the below event in the spfile or init.ora file and restart the database:

alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

REF:Library Cache Locks Due to Invalid Login Attempts (Doc ID 1309738.1)

2 comments :

  1. We can observe 'Library cache lock' or 'row cache lock' when concurrent users login with wrong password to the database.karni kole locksmith indy

    ReplyDelete
  2. TO Richard C. Lambert

    What do you know about this locksmiths service?
    http://24locksmithjerseycity.com/
    Now, I'm at Jersey City and my car closed with keys inside. Please tell me exact service with good reputation that I can use to solve my problem. Thanks in advance!

    ReplyDelete