Wednesday, November 19, 2014

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

My Application Team getting below error and unable to initiate connection to the oracle database:

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

When I connected and checked on the database, (here my db is RAC server and using spfile)

On NODE 2:

Checking alert log file 

vi alert_DEVD2.log      

No errors found

$ sqlplus / as sysdba

SQL*Plus: Release Production on Tue Nov 18 19:35:36 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

col name format a30
col value format a10

SQL> select name, value from v$spparameter where name in ('processes','sessions');

NAME                           VALUE
------------------------------ ----------
processes                      400
sessions                        400

or, check the max and current utilization of these parameters

select resource_name, current_utilization/limit_value*100,current_utilization, max_utilization,limit_value from v$resource_limit where resource_name in ('processes','sessions');

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


On NODE 1 :

SQL> select * from v$resource_limit where resource_name in ('processes','sessions');

 vi alert_DEVD1.log      

ORA-00020: maximum number of processes (400) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.

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


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


From the above output I see my processes are exceeded the value set by oracle(400) and resulting the error in alert log. Then checked the connections to the database:

Set linesize 280;
select count(*),INST_ID,username,osuser,status,machine from gv$session group by INST_ID,username,osuser,status,machine order by count(*),INST_ID;

(or) for single node:

select count(*),username,osuser,status,machine from v$session group by username,osuser,status,machine order by count(*);

Check the output from the above query and kill if find any INACTIVE sessions (idle)


According to oracle, PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.

If the database is a RAC database then each NODE can have different values for PROCESS parameter.

SQL> sho parameter spfile;

NAME                                           TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/DEVD/spfiledevd.ora

Increase the Process parameter (replace xxx with any higher value)

SQL> alter system set processes=XXX  scope=spfile;
System altered.

SQL> alter system set Sessions=XXX  scope=spfile;
System altered.

*** Bounce the database to take effect ***

If processes=x

If the database is running on pfile then follow below steps:

1. Edit the database init.ora
2. Locate and increase the processes parameter to a higher value.
3. Save the init.ora file
4. Stop and restart the database
5. Re-connect to the database instance

For more Refer:

Troubleshooting Guide - ORA-20: Maximum Number Of Processes (%S) Exceeded (Doc ID 1287854.1)


Post a Comment

Auto Scroll Stop Scroll