Intermittent TNS-12520 or TNS-12519 or TNS-12516 Connecting via Oracle Net Listener (Doc ID 240710.1)

Last updated on JULY 28, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.2.0.1 [Release 12.1 to 12.2]
Oracle Net Services - Version 12.1.0.2 to 12.2.0.1 [Release 12.1 to 12.2]
Oracle Net Services - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.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. 

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

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