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

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

We observed below error on application side. A few transactions were dropped because of this.
We have 2 node RAC configure and applications connect trough SCAN using JDBC.
Version:
DB : 12.2.0.1.180417
GI : 12.2.0.1.180417

Error:
Following error are same kind of errors:

ORA-12516 TNS:listener could not find available handler with matching protocol stack
ORA-12520 TNS:listener could not find available handler for requested type of server
TNS-12519 TNS: no appropriate service handler found
ORA-12519 TNS: no appropriate service handler found

Cause
Processes parameter reached its maximum limit. Basically PMON background process is responsible for updating listener about the load and dispatcher information.
Time in-between update is maximum 10 minutes, frequency changes according to workload on instance.
Listener has count of established connection but not having terminate count happen on DB Server. PMON process update listener about current position.
It having gap of around 10 min, if listener reached maximum connection count, then it update to “Blocked” state and start dropping new connections.

Solution

1. Check the status of listener
It show status is blocked.

lsnrctl status

Service "XE" has 1 instance(s).
Instance "xe", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

2. Check the number of processes.

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

RESOURCE_N CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU CON_ID
---------- ------------------- --------------- ---------- ---------- ----------
processes 290 316 320 320 1
sessions 410 460 504 504 1

Note: Maximum utilization is almost similar to maximum available limit.

3. Increase the Processes parameter in Oracle

--Check current value of parameter
Show parameter processes

NAME       TYPE     VALUE
---------- -------- ----- 
processes  integer  320

--Increase the value
SQL> alter system set processes=500 scope=spfile;

--Restart the Oracle Database for taking parameter in effect.
Shutdown immediate;
startup

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.