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.
No comments:
Post a Comment