无法通过listener建立连接 ora-12519

最近客户在进行数据连接的时候，出现 tns-12516/12519错误，现在将解决方案及相关知识进行一下整理。

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 出现tns-12516/12519错误最常见的就是由于达到了processes或者是sessions参数的负载最大值。当tns listener确信连接数达到了最大负载值，就将相对应的实例的service handler状态设置为blocked，同时拒绝对新近的数据库连接请求进行服务。

tns listener如何获得特定实例的负载值？和注册服务一样，PMON进程负责定期执行SERVICE_UPDATE更新特定实例的负载或者是dispatcher信息。最大负载值是由processes初始化参数决定的，信息更新频率由实例的workload决定，最大更新间隔为10分钟。由于listener不能立即获得数据库实例的负载信息，所以试图通过listener建立数据库连接而被实例终止的信息是不能立刻被listener获得，只能通过PMON进程进行service_update才能获得实例的负载信息，且最大间隔10分钟。因此会出现通过listener查询的实例负载和实例的真实负载不一致的情况。

根据Oracle文档，SESSIONS和TRANSACTIONS的初始化参数应该源于PROCESSES参数，根据默认设置SESSIONS = PROCESSES * 1.1 + 5。

异常：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".

解决：设置processes参数

 

Background Of The Error:
One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.

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: ora-12519 or ora-12516

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".

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26276376/viewspace-759596/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26276376/viewspace-759596/