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

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

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

TNS-12519 TNS: no appropriate service handler found

ORA-12519 TNS: no appropriate service handler found

ORA-12520 TNS:listener could not find available handler for requested type of server

Cause:

PMON update listener with information about instance such as load and dispatcher information. PROCESS parameter determines the maximum load for dedicated connection in database. The interval at which PMON provides SERVICE_UPDATE information differs according to the workload of the instance. The maximum interval between these service updates is 10 minutes. When the threshold exceeds the limit then listener become “Blocked” and no new session can be made since it refusing incoming connection. Once, listener gets the information from PMON that the thresholds are below the configured limit then listener resume accepting connection. Since SERVICE_UPDATE can take maximum 10 minutes, therefore, there can be a difference between the current instance load according to the listener and the actual instance load. The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated.

Solution:

· check if the number of current connections by using the following command in Unix:

ps -ef | grep oracleSID | grep -v grep | wc –l

· Check process parameter in database

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

SQL> show parameter processes

NAME TYPE VALUE

------------------ ----------- -------

processes integer 200

SQL> select count(*) from v$process;

COUNT(*)

----------

199

Increase process:

processes=x

session=(1.5 * PROCESSES) + 22

SQL> alter system set processes=300 scope=spfile;

Processes is not a dynamic parameter, so you need to restart database to take the effect.