Troubleshooting Guide TNS - 12519 TNS - 12516 ORA -12519 ORA - 12516

In this Document

Applies to:

Purpose

Troubleshooting Guide For Error

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

You are facing TNS - 12519 / TNS - 12516 in the client application or Listener log.

Troubleshooting Steps

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

Known Issues :

1) Do you have a 8i instance registering to 9i listener? 

Note 361685.1 8i Database Registered to 9i Listener Fails with ORA-12519 
Bug 1880986 - 9i Listener Blocks 8.1.7 Service Handler After First Connection 

2) Do you have listener configured for IIOP / or IPC? 

Check whether the listener is configured correctly for External procedures. 
Note 209410.1 TNS-12516 / ORA-12516 Starting Listener configured for IIOP 

3) Are you facing this problem while connecting with Oracle CMAN(Connection Manager) ? 

Make sure the listener is started in the target database server machine. 
Refer Note 331701.1 ORA-12516 Using 10g Connection Manager 

4) Do you have Active/Passive RAC configuration? 

Note 357882.1 Ora-12520/12516 Received When Listener on Node 1 is Down in RAC Setup. 


5)Do you see the instance status as 'Blocked' in the output of lsnrctl prompt for command services? 
$ lsnrctl services
At the same time, do you see the processes value is hit maximum with the help of v$resource_limit view? 

Increase the processes value to allow the listener to spawn more server process as required in your environment. 

Diagnostic Information :

If above steps did not help in resolving the issue, please collect the below specified information and submit the same while a creating Service Request(SR).
 
1. Provide the output of  following when the issue occurs.

$ lsnrctl (from database server(s))

2. Upload the listener.ora 

3. Enable the client and listener tracing , reproduce the issue. 

Refer Note 395525.1 How to Enable Oracle Net Client,Server,Listener,Kerberos and External procedure Tracing from Net Manager (netmgr) 

Upload the resultant trace files.

4. Upload the alert log and listener log also containing tracing time period

References

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-753307/，如需轉載，請註明出處，否則將追究法律責任。