library cache lock and user password

在连接项目CI数据库的时候，老是报"ORA-12520: TNS:listener could not find available handler for requested type of server" 的错误。造成这个错误的原因应该是数据库服务器不能够分配新的server process来服务于新的数据库连接了，查看下后来alert日志，有很多如下错误...

Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
  Time: 11-MAR-2012 21:52:41
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.133.131.82)(PORT=55723))
WARNING: inbound connection timed out (ORA-3136)
Sun Mar 11 21:52:51 2012

 

因为数据库设置的都是dedicated server模式，大概是process值太小了，查了下当前值为300， 于是改成了500。

以为问题没了，但是没多久这种问题有出现了，查看了下v$session，发现当前有496个活动session! God，又把process都占光了！仔细瞅了下v$session, 居然发现大部分的session都处于“library cache lock”等待状态！！！ 这个太诡异了，怎么会有这种事情！ 

网上查了下很多介绍如果处理分析"library cache lock"问题，但是感觉跟我遇到的都不一样，杯具。

然后，再然后，在yangtingkun的网站上看到如下一篇文章 -- 11.2数据库登录出现library cache lock等待（一） 感觉跟我遇到的问题很像！因为这个问题的起因是因为在进行项目数据库升级的时候，把数据库schema的密码给改了，防止在升级过程中有人连接数据库进行操作。但是因为有两台web server也是连接这个数据库的，默认会开启一定数量的连接池。因为在升级的时候没有shut down这两台web server，结果有可能就造成了这个问题。

 

用yangtingkun给出的Sql语句查询了下...

SELECT sid, machine, username, event, p1text, p1, p2text, p2, p3text, p3, seconds_in_wait, blocking_session
 FROM gv$session
  WHERE event = 'library cache lock'
order by seconds_in_wait desc;

 

正像yangtingkun指出的那样，username这一列都是空的！而且如果我这个时候尝试把那个schema的密码改回原来的值会发现当前session也hang住了，查询下等待事件也是'library cache lock'! 无奈只有杀掉当前session.

 

解析来我把数据库服务器的listener给停掉了，结果是这些'libary cache lock'会逐渐地消失。（刚开始我没有等这些session都消失再重新启动listener,结果session数很快又爆到近500！）

 

等了一个小时左右，所有的这些library cache lock session（也就是那两台web server过来的session)都消失了！

 

这个时候再把密码给改回来，然后启动listener， 过了一会那两台web server (moonqa02/szv-dev-lo-d01)又连接到数据库上，session数增加了70就稳定不动了，大概连接池的大小就设置这么多连接数。

 

后来我想试着把密码再改了想重现之前的错误，但是没有成功，这些session数就一直稳定了，后来我尝试把这些session都kill掉，但是很奇怪web server不再进行数据库连接了，session数没有变化 :( 重启数据库也没有效果）

 

不过重现这个问题也很容易，按照yangtingkun说的那样，同时多开几个session, 然后每个session都用错误的密码连接数据库，多试几次之后就会发现library cache lock等待时间出现在v$session中了！！！ 

下面是yangtingkun的推测...

"

在所有的疑问都解开了，用户程序已错误的密码不断登录，在加上11g的密码延迟验证，使得用户的验证等待时间不断加长。这也解释了为什么输入正确的密码登录后，这个现象曾短暂消失，因为用户成功登录，密码延迟验证的时间归零。
找到问题的原因后，在客户的数据库上以其他的用户尝试模拟这个现象，测试发现，如果一个会话登录数据库，即使每次密码都不正确使得延迟验证时间不断变长，也不会引发library cache lock的等待时间，但是只要该用户存在第二个登录会话，这时library cache lock会在两个会话同时出现，而且即使这个会话尝试使用正确的密码登录，在成功登录之前，也要等待library cache lock事件。
根据这个现象，个人推测Oracle为了实现延迟验证，必然需要在共享池中保存一个类似计数器的对象。这个计数器记录用户登录连续密码错误次数，从而确定延迟验证的等待时间。当用户成功登录，计数器清零。如果是一个会话，那么只需要独占这个计数器就可以了，当存在两个以上的会话，且两个会话都试图修改计数器的内容，那么资源竞争就出现了，而体现在数据库中的等待事件就是library cache lock。当然这只是个人的猜测而已，还没有看到Oracle官方对这种情况的说明。

"

 

所以对于解决我遇到的问题就是在进行数据库升级之前把web server也都停掉，避免对数据库的用错误密码持续连接！