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
Cause:
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:
For Ex:
LSNRCTL> services
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
(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=
Solution:
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 database has to be bounced.
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)';
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
---- ----------------------
D000 .000021769
D001 .006658056
D002 .025966763
These dispatchers show little busy time. If dispatchers are busy more than 50 percent of the time, consider starting more dispatchers.
Posts
3 comments:
Hello reader,
I was facing the same issue of “tns-12518 listener could not hand off client connection" onto Oracle 10g R-2 on windows 2003 server (32 bit) last week.
It was identified that the issue was on Java front-end application. There was some DM files in which connection was opened but not closed, due to which this error was appearing.
When these DM files are referred using Java application we were getting this error and need to re-start Listener each time.
I thing this will help you. Best of luck to you in advance.
Hi ,
I also got this error
I have 4 GB ram on the host but It could be memory issue
DIRECT_HANDOFF_TTC_LISTENER=OFF
so added above line in listener.ora file
but problem didnt get away ...after reading many blogs I found that there is need to increase the process limit which is default set to 150 ..
SQL> select * from v$resource_limit where resource_name='processes';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
------------------------------ ------------------- --------------- ----------
LIMIT_VALU
----------
processes 84 87 150
150
use bwlow command to increase the process limit
alter system set processes=300 scope=spfile;
there is need to "shutdown immediate" and "startup" to reflect the new value.
but I have found that when the process was limited to 150 then number of processes was going up to 150 ...now when I set it to 300 ..its going up to 300 ..and .the same error is thrown. SO is there any limit of oracle processes? It keeps increasing as much as I set the limit? how can we overcome this issue?
@praveen
How about your database "DEDICATED" or "SHARED SERVER"?
How about applications?
>I have found that when the process was limited to 150 then number of processes was going up to 150 ...now when I set it to 300 ..its going up to 300 ..and .the same error is thrown.
I think you should measure How many sessions connect to your instance. How many inactive status? What wrong that make your instance have many sessions (locking)?
if you find many sessions (inactive), you should check why application don't use old connections?
or tune application about inactive timeout(if you use jdbc on application).
if you have many sessions (active) ? you should increase processes parameter to solve?
if you have many sessions (waiting), you should check about blocking + locking and solve it + tune it?
One thing, you should do... use AWR or Statspack to investigate the issue.
Post a Comment