ORA-12519引出的问题

+

看到这篇帖子提到一个ORA-12519的问题处理：

http://blog.csdn.net/diguoguo/article/details/6185536

oerr的解释：

12519, 00000, "TNS:no appropriate service handler found" // *Cause: The listener could not find any available service handlers that // are appropriate for the client connection. // *Action: Run "lsnrctl services" to ensure that the instance(s) have // registered with the listener, and are accepting connections.

帖子上的做法是查看了lsnrctl service，instance已注册，状态是ready。

MOS的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.

采用实例注册的方法，PMON进程负责更新监听的信息(负载、分发等)。最大负载由PROCESSES参数决定。PMON提供服务更新的频率依据实例负载的不同有所区别。这些服务更新之间的最大间隔是10分钟。

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.

监听会计算已经连接到此实例的connection数，但不会立即知道终止进程的信息。只有当PMON通过SERVICE_UPDATE更新监听的时候，监听才知道当前的负载。既然间隔有10分钟，那就存在监听当前知道的负载与实际负载的差异。

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  TNS-12519 TNS:no appropriate service handler found  Additionally, an ORA-12520 error may appear in the listener log.  The output of the LSNRCTL services command will likely show that the service handler is "blocked".  e.g. '"DEDICATED" established:1 refused:0 state:blocked'

当监听认为当前连接数已经到达最大负载，就将每实例的service handler状态设置为“blocked”，开始拒绝新进来的客户端连接请求，报错：

TNS-12516 TNS:listener could not find instance with matching protocol stack TNS-12519 TNS:no appropriate service handler found

另外，一个ORA-12520的错误也可能出现在监听日志中。LSNRCTL服务命令的输出可能出现service handler状态是“blocked”。

例如：'"DEDICATED" established:1 refused:0 state:blocked'

这篇帖子中的问题最后看是由于PROCESSES参数设置导致的。

"根据Oracle文档，SESSIONS和TRANSACTIONS的初始化参数应该源于PROCESSES参数，根据默认设置SESSIONS = PROCESSES * 1.1 + 5。 但是目前SESSIONS的设置达到了600，而PROCESSES的设置没有改变，仍然为150，导致了过多的用户会话连接到Oracle上时，Oracle没有足够的后台进程来支持这些会话。"

需要将PROCESSES参数设置为正确的值。

PROCESSES参数：

Property Description Parameter type Integer Default value Derived from PARALLEL_MAX_SERVERS Modifiable No Range of values 6 to operating system-dependent Basic Yes

SESSIONS参数：

Property Description Parameter type Integer Default value Derived: (1.1 * PROCESSES) + 5 Modifiable No Range of values 1 to 231 Basic Yes

表明这两个参数都是需要重启数据库才能生效的。修改initSID.ora中的processes的大小，重新启动数据库到nomount状态下，执行create spfile from pfile; 并startup open。 另外，这些SQL有助于帮助排查问题： 1. select count(*) from v$process;                                                  取得数据库目前的进程数。 2. select value from v$parameter where name = 'processes';    取得进程数的上限。等同于show parameter processes。 3. select * from v$license;                                                                查询数据库自启动以来最大的并发数量。