首页 > Linux操作系统 > Linux操作系统 > ORA-12519引出的问题


原创 Linux操作系统 作者:bisal 时间:2013-09-14 12:22:45 0 删除 编辑



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。


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 
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'


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



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


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。

1. select count(*) from v$process;                                                  取得数据库目前的进程数。
2. select value from v$parameter where name = 'processes';    取得进程数的上限。等同于show parameter processes。
3. select * from v$license;                                                                查询数据库自启动以来最大的并发数量。

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: 数据库优化小计
请登录后发表评论 登录
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺


  • 博文量
  • 访问量