New Connections to the Database lead to ORA-12518 or TNS-12518

The Problem

ORA-12518 errors in the listener log when new users are connecting to the Database. Example is as follows:

TNS-12518: TNS:listener could not hand off client connection 
  TNS-12547: TNS:lost contact 
   TNS-12560: TNS:protocol adapter error 
    TNS-00517: Lost contact 
     IBM/AIX RISC System/6000 Error: 32: Broken pipe

This is an intermittent problem that would likely occur at peak database usage times.

The Solution

There are two possible causes:

If using DEDICATED connections:

– With 10g, the default PROCESSES value in the database is set to 150. This may be too low for a production system.

If using SHARED SERVER:

– The Dispatcher has reached maximum connections and it’s refusing the newer ones.
– You can verify the Dispatcher’s status by querying the Listener services as follows:

For E.g.:

LSNRCTL> services [listener name]

Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 4 handler(s) for this service...
    Handler(s):
      "D001" established:5515 refused:7 current:245 max:2026 state:ready
         DISPATCHER , pid: >
         (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=))

DEDICATED

Increase the PROCESSES parameter to handle the number of processes that may be needed. Monitor for the ORA-12518 errors in the listener log and increase the value again if necessary. See the Oracle Database Administrator’s Guide 10.2 for these and database tuning details. PROCESSES is a static parameter so the instance will need to be restarted after a change.

SHARED SERVER

Shutdown the Dispatcher using the following command:

SQL> alter system shutdown immediate 'D001';

Later on add new dispatchers as follows:

SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)';

While increasing the DISPATCHERS value, also check shared servers ratio.