This is a quick post to troubleshoot ORA-12519 TNS:no appropriate service handler found & ORA-12516 TNS:listener could not find available handler with matching protocol stack.

Most common reasons for the ORA-12516 and ORA-12519 errors is the configured maximum number of PROCESSES and SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become “Blocked” and no new connections can be made.

PMON is responsible for updating the listener with information about load and dispatcher of a particular instance. Maximum load for dedicated connections is determined by the PROCESSES parameter. The listener counts the number of connections it has established to the instance and 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: ora-12519 or ora-12516.

So if the cause of ORA-12519 or ORA-12516 is the limit being reached, increase the processes parameter and restart the instance to resolve the issue.

• To check the current parameter value:-

show parameter processes;

• To increase the parameter value:-

alter system set processes = <New value that you want to set>;

• To restart the instance:-

Since processes is a static parameter it needs an instance reboot.

shutdown immediate;
startup;

Hope this helps in resolving the ORA-12519 TNS:no appropriate service handler found & ORA-12516 TNS:listener could not find available handler with matching protocol stack errors.