SQL*Plus Connection Slow ! Library Cache Lock…..

Oracle Database 11g introduced a new security feature, which is meant to protect a users’ password. Refer to section “What are the Oracle Password built-in Password Protections ?” of Chapter 3 of Oracle Database Security Guide. The document says, if a user tries to log in to the database more than 3 times with an Invalid Password, Oracle Database delays each login after third try. Most of them are unware of this new Security feature, and therefore, this sometimes creates a panic situation and an unexpected escalations to Oracle Support.

Recently, one of my customer reported a slow database connection to one of their Application Schema. The connections to other schemas, except the problematic one, were going through perfectly fine. The application connects to the Database through Weblogic Server and the problem summary mentioned that while the Weblogic Server is able to connect successfully (with zero delay) to the same schema on UAT, it takes around 30 seconds to connect to the same user on production. Therefore, concern over an issue with Database Server was raised. A severity 1 SR was raised and operating system truss output for the problematic and non-problematic user was uploaded as well. The code path of both the output was exactly same, except for the time taken for the problematic user.

One simple logic to understand was, that if it is an Oracle Server Issue, then it should cause the delay in connection for all the Schema’s and not just to one. Therefore, there had to be some problem with this Schema. It was thoroughly checked whether there are any AUDITING or ON-LOGON Triggers on this Schema. Database Audit Trail was enabled for all the users and therefore, this was not an exception for this schema. There were no ON-LOGON triggers on this User. Therefore, what was causing this delay was a big question.

Customer noticed this due to the fact that they have couple of scheduled cron jobs that runs every 30 seconds. These jobs usually takes, 5-10 seconds to complete, but since last few days, they have observed these overlapping. While Investigating this issue, I noticed that when we issue sqlplus username/password, the session waits on Library Cache Lock for around 15-20 seconds and then authenticates the user. This was the case with the scheduled jobs as well. Each of these jobs were waiting on the latch. Why should an SQL*Plus command wait on Library Cache Lock ? For other users, it could immediately authenticate.

In this case, Database Audit Trail, which inserts a record in aud$ came to our rescue. In the aud$ table, we noticed that for last 3-4 days, the number of failed attempts had increased dramatically. Further, it was also revealed that these connections from our the Application Servers configured at a DR site and were pointing to the production database server using wrong password. The error message reported in the aud$ was 1017, which is “Invalid Username / Password”.

The library cache lock wait is seen due to the fact that the account status gets updated due to incorrect login and is maintained in the library cache. This is to prevent password guessing attack as there’s a sleep in the code when incorrect login attempts exceed count of 3. These sleeps cause the latch, as the process holding it is yet to release the lock. While this behaviour is a Security enhancements, it can be disabled by setting event 28401.