Troubleshooting TNS-12519 TNS-12516 ORA-12519 ORA-12516

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.

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 )

Google+ photo

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

Connecting to %s