ORA-12519引出的问题

采用实例注册的方法，PMON进程负责更新监听的信息(负载、分发等)。最大负载由PROCESSES参数决定。PMON提供服务更新的频率依据实例

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

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

相关阅读：

SPFILE 错误导致数据库无法启动(ORA-01565)

ORA-01172、ORA-01151错误处理

ORA-00600 [2662]错误解决

ORA-01078 和 LRM-00109 报错解决方法

ORA-00471 处理方法笔记