Wednesday, February 4, 2009

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

Error Description
With sqlplus whenever you connect to a database san it fails with error ORA-12518.
C:\>sqlplus arju/a@san

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 4 20:59:26 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

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

Cause of the Problem
ORA-12518 indicates a problem while listener handing off the client connection to the server process or dispatcher process. This problem can happen in many scenarios. Like if there is limited resource on the server computer so that it is unable to establish a new connection. Also if there is processes limit in the database initialization parameter this problem may happen. On windows if the service is unavailable then ORA-12518 problem might happen.

Solution of the Problem
Step 01: Check whether your listener version is compatible with database.
If listener version is not compatible with the database version then ORA-12518 likely to occur. The general rule is to use always higher version of listener. If you have both 9i and 10g database then always use 10g listener. If you use 9i listener then connection to 10g database may fail with 9i listener.

Step 02: Gather information from listener log file.
Make sure which version of listener you are using and check your listener.ora file as well as always check listener log file after ORA-12518 occur. By lsrnctl status command you can see log file. Check contents of the log file. Check listener parameter file and see listener version.

C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 04-FEB-2009 20:49:32

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
Start Date                04-FEB-2009 20:47:22
Uptime                    0 days 0 hr. 2 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   F:\app\Administrator\product\11.1.0\db_1\network\admin\listener.ora
Listener Log File         f:\app\administrator\diag\tnslsnr\5a0c04b78203433\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.12.233)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "san" has 1 instance(s).
Instance "san", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Below is the from listener log file,

TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 2: No such file or directory



From the log file we see No such file or directory. This windows Error appears on windows machine whenever database service is not available.

The solution is to make sure that you can connect to database as sysdba and your database service is open. You can start your database service from command propmt by,
sc start {oracle_service_name}

Alternatively, right click on my computer, then click manage> click services> find the oracle service and start then.

Step 03Check the listener service handle in block state: Issue lsnrctl services and see status information under the database service name. Check the state and see if the state is in blocked.
$lsnrctl status
.
.
Service "san" has 2 instance(s).
Instance "san", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:10 refused:4276
LOCAL SERVER
Instance "san", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:138 refused:0 state:ready
LOCAL SERVER
.
.

If state is in blocked state then connection is not possible. The state of a handler can be in blocked state if,
1)Database in startup or shutdown process.
or,
2)The initialization parameter value of PROCESSES has reached it's limit.

In this case if you are in dedicated server mode you need to increase static processes parameter.

If you are in shared server mode and your connection is refused based on limits then increase the dispatcher parameter.

Step 04 Check if local connection as sysdba is successful: Check whether you can connect to database as sys user as sysdba. If local connection is ok then check database open_state. Also check v$instance view fields like status, active_state, blocked fields.

Step 05
Check the Processes is reached it's limit:
If local connection sys as sysdba is successful then query from v$resource_limit and see the processes parameter.
SQL> select * from v$resource_limit where resource_name='processes';

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                      120             150        150        150

If max_utilization reached the limit value then consider to increase the static processes parameter.

The summary of ORA-12518 error is it may caused by many reasons and always check you listener.log file and alert log file to solve it.

3 comments:

Anonymous said...

Thank You for putting light into my problem. But still I could not solve it:

I am trying to connect to my local database, witch is running, with SQL-Developer. And I get this ORA-12518.

I checked the listener's log file as you suggested and found these:
TNS-12560 and TNS-00534.
But on TNS-00534 I could not find futher information.

Can You help me ?

Regards
Outi from Finland

Edwin said...

probably, your server is running out of memory

Anonymous said...

its a good and detail description.
but i have still have the same issue. listener log file contains following.

TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 203: Unknown error