My Oracle Support Banner

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 12.2.0.1 [Release 10.1 to 12.2]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.2.0.1 [Release 11.2 to 12.2]
Information in this document applies to any platform.

Symptoms

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. 

SELECT *
FROM V$RESOURCE_LIMIT
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).

Changes

It is likely a significant increase in load has occurred.

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.