Hi,This is Halim,working as a Sr. Oracle DBA and PL/SQL developer in Atlanta, USA. I very much like to play with SQL, PL/SQL and scripting languages along with my DBA job. I got a recognition for PL/SQL. Please see here at 16th position(http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html). I am maintaining this blog for helping myself while in work and sharing my knowledge with others. If you have any question. you can reach me at infohalimdba@gmail.com Thanks.

Sunday, September 20, 2009

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

Cause of the ProblemORA-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-------------------------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.

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.

03. Check 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.

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.

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 If max_utilization reached the limit value then consider to increase the static processes parameter.