Unusual Library Cache Lock

We had a "library cache lock" issue on our 11g database and ended up having to shutdown the DB to resolve the issue. After starting the database, we investigated the problem and the following note is about how to find the root cause of the problem. Here I used Tanel Poder's scripts to investigate this issue.

First, I checked the problem using the following script. Since the ash data has been moved to history, I used the dashtop script (to use dba_hist_active_sess_history) to find the top wait events. The first image shows that the "library cache lock" event occurred at 13:39:00.

In the second step, I checked the "top_level_call_name" column from oracle ash. I found that my top-level call is "OAUTH". It seems to be related to user authentication.

My third check using top_level_call_name='OAUTH' showed that three machines were busy with this top call.

And using dash_wait_chains I found the top blocker in that time frame in the database. Query showed me that session number 14476 is the top blocker.

Here I checked that session id and again it showed me sid relates to one of the machines I found in the third step. But one important column for this session is “in_connection_mgmt” with value “Y”.

And finally, I checked the connection management processing percentage at the time of the problem, it showed me that 97% of the database time was in handling database connection requests.

Based on oracle support note 1308738.1 we found these symptoms are because of too many login failures to database. Connection requests of machines has checked in step third there were many logins failure to database and that cause high “library cache lock” in the database. this means that three machine from third step had too many login failures and caused “library cache lock” problem in the DB.