碧海蓝天

现场报告连接数据库报错：ORA-12519 TNS: no appropriate service handler found

这个错误没遇到过，登录现场环境确认报错情况属实，sqlplus / as sysdba不能登录数据库；

检查操作系统oracle进程数目，与processes=600的设定比较接近；

检查应用服务器连接池连接情况，2台weblogic连接池current都是200，加起来400，有2台tomcat，current情况未知，配置最大75；

检查listener，发现state是blocked

‘”DEDICATED” established:1 refused:0 state:blocked’

这里比较异常，正常情况下应该是ready，不解；

求助metalink， 240710.1 找到了原因：

By way of instance registration, PMON is responsible for updating the listener
with information about a particular instance such as load and dispatcher
information. Maximum load for dedicated connections is determined by the
PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE
information varies according to the workload of the instance. The maximum
interval between these service updates is 10 minutes.

The listener counts the number of connections it has established to the instance
but does not immediately get information about connections that have terminated.
Only when PMON updates the listener via SERVICE_UPDATE is the listener
informed of current load. Since this can take as long as 10 minutes, there can be
a difference between the current instance load according to the listener
and the actual instance load.

When the listener believes the current number of connections has reached maximum
load, it may set the state of the service handler for an instance to “blocked”
and begin refusing incoming client connections with either of the following
errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack

pmon动态instance信息到listener，这其中就包括process数目的信息，当到达限制时，pmon会通知listener别再申请连接了，listener把相应的服务状态修改为：blocked，客户端再次连接就会报12516错误。

开始解决：两台tomcat服务器运行了归档查询业务，是最近新添加的服务，问题也就出在这里；

关闭tomcat服务器；过一会查看监听状态，发现已经变为ready，sqlplus / as sysdba尝试连接数据库，已经可以连接；

进一步查看session信息，发现tomcat服务器仍然有144个连接，说明tomcat连接有严重泄露情况；

最终，把processes增大，tomcat＝>weblogic，期望可以解决这一问题。