[oracle@orcl12x ~]$ oerr ORA 12516 12516, 00000, "TNS:listener could not find available handler with matching protocol stack" // *Cause: None of the known and available service handlers for the given // SERVICE_NAME support the client's protocol stack: transport, session, // and presentation protocols. // *Action: Check to make sure that the service handlers (e.g. dispatchers) // for the given SERVICE_NAME are registered with the listener, are accepting // connections, and that they are properly configured to support the desired // protocols.
Client use the user process and try to connect to the oracle database where PMON monitors and registers the incoming connections.
PMON updates the service to the listener every now and then ,when a new connection connect to the database.
SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ processes integer 300
PROCESSES is the parameter which allocates the maximum load for a connection.
When the load on the database increase and reaches to maximum utilization, PMON updates the listener using service update and listener assumes resource starvation even though there are enough processes to handle the requests.
So the listener marks the status as BLOCKED to stop incoming connections.You can see the status below. Sometimes it is pretty much frustating to spend our valuable time troubleshooting a silly error right 🙂
[oracle@orcl12x ~]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-JUL-2020 09:35:57
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl12x)(PORT=1521)))
Services Summary…
Service “orcl12x” has 1 instance(s).
Instance “orcl12x”, status BLOCKED, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
One of the reason for this error due to less process allocation in the database
- Increase the process to a higher value to avoid listener handler error!!!
SQL> select count(*) from v$process;
COUNT(*)
----------
22
SQL> alter system set processes=500 scope=spfile;
System altered.
Check the max utilization of the process.Follow this rule of thumb!!
if max_utilization = limit_value then go ahead and increase the process limit to a high value.So that application team would be happy always!!
SQL> select * from v$resource_limit where resource_name in ('processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
------------------------------ ------------------- --------------- ----------
LIMIT_VALU CON_ID
---------- ----------
processes 22 24 300
2. Second reason might be due to JDBC connection pool errors.If the client is trying to connect to the database using java connection.
By default connection pooling is enabled if not,enable and increase the connection pooling using max pool size
Connection pooling reuses the connections to the database instead of establishing new connection.
