TNS-12518: TNS:listener could not hand off client connection

做一个后台的压力测试发现如下问题：

数据库采用c3p0,报如下异常：
[2010/12/31 00:00:02][WARN](BasicResourcePool.java:1841) - com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@12a10be -- Acquisition Attempt Failed!!! Clearing pending acquires. While
trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (10). Last acquisition attempt exception:
java.sql.SQLException: Io exception: Got minus one from a read call
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
        at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
        at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
        at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
        at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)


然后数据库listener.log报如下：
31-DEC-2010 00:00:00 * (CONNECT_DATA=(SID=ty)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=

(PROTOCOL=tcp)(HOST=192.168.100.245)(PORT=43477)) * establish * ty * 12518
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


网上google了N遍，受http://surachartopun.com/2008/01/tns-12518-tnslistener-could-not-hand.html启示，判断是由于c3p0某些连接没有释放掉，导致数据库连接达到最大值（150）频繁包TNS-12518: TNS:listener could not hand off client connection。

原文copy一份，以供以后参考：

TNS-12518: TNS:listener could not hand off client connection
Oracle Net Services - Version: 9.2.0.5 to 10.2.0.1
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 , pid:
(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.

เขียนโดย SURACHART ที่ 12:19 PM   
ป้ายกำกับ: LISTENER
3 ความคิดเห็น:


Bharat k said...
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.