Intermittent TNS-12520 or TNS-12519 or TNS-12516 Connecting via Oracle Net Listener
Last updated on AUGUST 23, 2017
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 to 184.108.40.206 [Release 12.1 to 12.2]
Oracle Net Services - Version 220.127.116.11 to 18.104.22.168 [Release 12.1 to 12.2]
Oracle Net Services - Version 10.1.0.2 to 22.214.171.124 [Release 10.1 to 11.2]
Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [Release 11.2]
Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [Release 11.2]
Information in this document applies to any platform.
Client connections may fail intermittently with any of the following errors:
TNS-12516 TNS: listener could not find instance with matching protocol stack
ORA-12516 TNS: listener could not find instance with matching protocol stack
TNS-12519 TNS: no appropriate service handler found
ORA-12519 TNS: no appropriate service handler found
Any or all of these errors might appear in the listener.log and may accompany an ORA-12520:
ORA-12520 TNS:listener could not find available handler for requested type of server
The output of the lsnrctl services command may show that the service handler
is in a "blocked" state.
e.g. '"DEDICATED" established:1 refused:0 state:blocked'
If the errors are fleeting and appear to 'self correct', it is likely a resource depletion issue
at the instance. In particular, the PROCESSES setting can be too low.
Log in to the instance and issue the following:
select * from v$resource_limit;
Check the values for PROCESSES. If MAX_UTILIZATION is equal to OR has approached the LIMIT_VALUE, that
could signal a need for an increase in PROCESSES.
See the following:
CURRENT_UTILIZATION NUMBER Number of (resources, locks, or processes) currently being used
MAX_UTILIZATION NUMBER Maximum consumption of this resource since the last instance start-up
INITIAL_ALLOCATION VARCHAR2(10) Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITED for infinite allocation).
LIMIT_VALUE VARCHAR2(10) Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit).
It is likely a significant increase in load has occurred.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms