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

In one of my SOA project, we are running SOA Suite 11g - 11.1.1.4  on Weblogic 11g Cluster in Bladframe machine. Backend used here for SOA Schema database is Oracle 11g RAC database.

After couple of weeks of successfully execution in developement environment we hit this issue of  ORA-12516, TNS:listener. This error generally means there is no connection available to the database.We need to configure the database to allow more connection.

As suggested by Oracle, we should have number of processes setting to 200 for SOA Suite 11g. This setting is already in place and still we hit the ORA-12516, TNS:listener-issue. This happens mostly because of wrong Data sources connection settings, which allows to exapand its connection pool to hundreds of connections.

Even while restarting the weblogic server you will not be able to get the weblogic server in running mode , because all data sources will fail while trying to connect to database and you server state will go to Admin mode because of this.

Solution to ORA-12516, TNS:listener Error

1. Loging to one of the database node using sqlplus as sys or system (basically sysdba role required)
2. Increase the number processes and sessions by executing below commands
• ALTER SYSTEM SET PROCESSES=400 SCOPE=SPFILE;
• ALTER SYSTEM SET SESSIONS=400 SCOPE=SPFILE;
3. Bounce the database again to take these changes
• SHUTDOWN IMMEDIATE;
• STARTUP;
4. To Verify the settings are updated properly; execute below sql commands
1. SHOW PARAMETERS PROCESSES;
2. SHOW PARAMETERS SESSIONS;

If you are using Oracle RAC database you may want to perform these steps again on another node.