This problem can occur on any platform.
Database may be configured as DEDICATED or Shared Server (MTS).
Error in listener Log File.
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
Linux Error: 32: Broken pipe
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 (pre-9i MTS) -->
* The Dispatcher has reached maximum connections and its refusing the newer ones.
* You can verify the the Dispatcher's status by querying the Listener services as follows:
Service "test" has 1 instance(s).
Instance "test", status READY, has 4 handler(s) for this service...
"D001" established:5515 refused:7 current:245 max:2026 state:ready
* 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 database has to be bounced.
* 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)';
Note : This syntax depends on the way the Dispatcher value is given in the init.ora or Spfile.ora
While increasing the DISPATCHERS value, also check shared servers ratio.
SQL> select name, (busy / (busy + idle))*100 "Dispatcher % busy Rate" From V$DISPATCHER;
NAME Dispatcher % busy Rate
These dispatchers show little busy time. If dispatchers are busy more than 50 percent of the time, consider starting more dispatchers.