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

Problem(Abstract)
Users intermittently receive error messages, especially when there are many users using the system.

Symptom
COM Exception (0x80004005) ORA-12516: TNS listener could not find available handler with matching protocol stack

or:

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

Cause
The Oracle database server (hosting the Controller database) has run out of available 'Oracle processes'.

This means that the Oracle database server cannot accept any new connections.

There are several potential causes for the Oracle database server running out of available 'processes':

• Scenario #1 - Oracle database server's value for "PROCESSES" has been configured too low
• Scenario #2 - Bug in third party (Oracle) software, causing session leak when JDBC connections are created/used. This means that the used processes are never released when closed.
• Scenario #3 - Defect in Controller 10.1.0.
• Scenario #4 - Defect in Controller 10.2 onwards, which causes too many processes to be consumed.

Diagnosing the problem

To find out what the maximum number of Oracle processes your database server is currently configured to allow

Check the value of 'PROCESSES' on the Oracle database server.

Steps:

1. Launch a SQL command product (for example 'SQL Plus')
2. Logon as 'system'
3. Type the following command: show parameter process

 

TIP: By default, the value will be 150. 

To find out how many Oracle processes are currently in use

Run the following script:

SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER'

(or)

SELECT * from v$resource_limit where resource_name in ('processes', 'sessions', 'transactions');

Resolving the problem

Scenario #1

Increase the maximum number of possible processes by increasing the value for PROCESSES on the Oracle database server (typically by default from 150) to a sensibly larger value

In many/most customer's environments, 300 is sufficient

For other customers, they have required 500 or even 1000.

Steps:

Ask your Oracle DBA to consult official Oracle documentation to confirm the best method for your Oracle environment.

For example, some database servers may be configured to use the 'spfile' parameters, but others may not.

Example:

In one real-life example (where it used spfile), the steps to change this parameter were:

1. Launch 'SQL Plus'
2. Logon as 'system'
3. Type the following command (to check that the database is using spfile): show parameter spfile
4. Assuming that it shows that you ARE using spfile, then type the following command: alter system set PROCESSES=300 scope = spfile
5. Obtain some downtime (nobody using the databases) and restart the Oracle database server (or simply the relevant Oracle database).

Scenario #2

Apply relevant Oracle patch/upgrade. Specifically, upgrade to either:

Oracle 10.2.0.5 (Server Patch Set)

Or Oracle 11.2

Referred from : http://www-01.ibm.com/support/docview.wss?uid=swg21603472