Intermittent TNS-12520 or TNS-12519 or TNS-12516 Connecting via Oracle Net Listener
(Doc ID 240710.1)
Last updated on AUGUST 04, 2018
Applies to:Oracle Net Services - Version 10.1.0.5 to 18.104.22.168 [Release 10.1 to 12.2]
Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.2 to 12.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.
WHERE resource_name in ('sessions','processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------- ------------------- --------------- -------------------- -----------
processes 288 296 300 300
sessions 296 319 335 335
Note that the MAX util is at or approaching the LIMIT. We would expect these errors to occur
under this circumstance when PMON sets the handler to block additional connections to a database
that is overloaded.
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!