ORA-12516 TNS:listener could not find available handler with matching protocol stack

We have a test system which is mainly used for workflow tests. Recently we have started to increase the load such as data volume and client connections. Then we saw the error below:

2022/01/10 00:41:44.610 +0000 WARNING [com.jboss.start.modules.DatabaseConnectionModule] Could not connect: Listener refused the connection with the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack

From the MOS article — Doc ID 240710.1:

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:

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

Additionally, an ORA-12520 error may appear in the listener log. The output of the LSNRCTL services command will likely show that the service handler is “blocked”.

So for a routine healthcheck, we should keep an eye on the view v$resource_limit; Check the values for PROCESSES. In this view: 

CURRENT_UTILIZATION — Number of (resources, locks, or processes) currently being used
MAX_UTILIZATION — Maximum consumption of this resource since the last instance start-up
INITIAL_ALLOCATION — This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITED for infinite allocation).
LIMIT_VALUE –Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit).

SELECT * FROM V$RESOURCE_LIMIT WHERE resource_name in ('sessions','processes');

If MAX_UTILIZATION is equal to OR has approached the LIMIT_VALUE, that could signal a need for an increase in PROCESSES.

References:

  1. Error ‘ORA-12516, TNS: listener could not find available handler with matching protocol stack’ When Running the RunETL Process (Doc ID 1100982.1)
  2. Intermittent TNS-12520 or TNS-12519 or TNS-12516 Connecting via Oracle Net Listener (Doc ID 240710.1)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s