IBM Support

** Troubleshooting ** Intermittent "(0x80004005) ORA-12516: TNS listener could not find available handler with matching protocol stack" when using Controller

Troubleshooting


Problem

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

Symptom

English:

  • 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

Spanish:


COM Exception (0x80004005) ORA-12516: TNS listener no ha encontrado
ningin manejador disponible con una pila de protocolos coincidente

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.
    • For more details, see separate IBM Technote #1611403.
  • Scenario #4 - Defect in Controller 10.2 onwards, which causes too many processes to be consumed.
    • For more details, see separate IBM Technote #1978834.


More Information on Oracle 'PROCESSES':
One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.
  • PMON is responsible for updating the listener with information about a particular instance such as load and dispatcher information. Maximum load for dedicated connections is determined by the PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE information varies according to the workload of the instance. The maximum interval between these service updates is 10 minutes.
  • The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated. Only when PMON updates the listener via SERVICE_UPDATE is the listener informed of current load. Since this can take as long as 10 minutes, there can be a difference between the current instance load according to the listener and the actual instance load.
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

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:
      4. 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'

For example:


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
TIP: For more information, see official third party (Oracle) documentation, such as Oracle's article 6857474.

Scenario #3
Upgrade to Controller 10.1.1 or later.
  • See separate IBM Technote #1611403.

Scenario #4
See separate IBM Technote #1978834.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.1;10.2.0;10.1.1;10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21603472

Page Feedback