问题出现：

27日下午，客户抱怨无法连上Oracle数据库O02ESG1（版本11.2.0.2），错误如下：

ERROR:
ORA-00018: maximum number of sessions exceeded

此时即使用sysdba也无法登陆数据库，估计session数已经到达上限了。为了能让sysdba能登陆数据库，我在操作系统层面kill掉一些用户进程。
登陆到数据库之后，在v$session中查询结果到类似下面的大量session：

SID USERNAME EVENT PROGRAM BLOCKING_SESSION

---------- ------------------------------ ---------------------------------------- -----------------------------------------------------------
      1002 OPGCEP1                        library cache lock                       JDBC Thin Client                576
      1003 OPGCEP1                        library cache lock                       JDBC Thin Client                576
      1004 OPGCEP1                        library cache lock                       JDBC Thin Client                576
      1005                                            SQL*Net message from client JDBC Thin Client                576
      1006                               library cache lock                       JDBC Thin Client                576

和客户沟通了一下，得出以下几个信息：
1. OPGCEP1正是应用程序连接数据库的用户名；
2. 应用程序采用多线程的JDBC连接数据库，且当前连接时的密码是错误的。

问题解决：

Oracle认为这是个Bug:
LIBRARY CACHE LOCKS DUE TO INVALID LOGIN ATTEMPTS (Doc ID 1309738.1)
Bug 12990384: HIGH LIBRARY CACHE LOCK IN 11.2.0.2.0
并给出了解决方法：

Dear Customer,

There was new SLEEP () code introduced which will get activated after Failed Login Attempts exceeds 3 and not the threshold setting set for the FAILED_LOGIN_ATTEMPTS parameter value.

Please set the below event in your init.ora file and restart the database.

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

After setting the above event, you will not be observing the "Library Cache Lock" issue during login with wrong password.

This event was introduced from 11gR2 release onwards to allow customer to turn off the "sleep" after three failed logon attempts, by adding the event to their instance initialization file.

Best Regards,
Tracy Li (李秀文)
Oracle Global Software Support

根据Oracle给出的解决方法，我在参数文件中加入一行EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"，重启数据库后，问题解决。

ITPub博客

大量"library cache lock"事件导致数据库无法连接

最新文章