You try to connect with Oracle database and get Ora-12518 listener could not hand off client connection error. Now what action is to be taken to get rid of above error? Follow below steps to resolve the issue. Connect with oracle database: C:\>sqlplus / as sysdba SQL>conn testuser/test@mydb And you got the ERROR: ORA-12518: TNS: listener could not hand off client connection Solution-1: First check your database whether it is in start mode or not. SQL> shutdown immediate; SQL> Startup; Solution-2: If first is not working You have to increase PROCESSES initialization parameter in Oracle database. First use the following command to check the existing value of the PROCESSES. show parameter processes As we have already started our database normally or using spfile, then run following command to alter system processes to 450. alter system set processes=450 scope=spfile; If you have started your database using pfile, process parameter must be set in pfile. Solution-3: If both the above solutions are not working. If you are getting ORA-12518 because of a shared server issue then you first need to use the below command to shutdown the dispatcher. SQL> alter system shutdown immediate 'D001'; Then add on new dispatchers. SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)'; Resolving ORA-12518 requires you to evaluate the syntax depending on your dispatcher value in the Spfile.ora or init.ora files. When you increase DISPATCERS to resolve ORA-12518 you should also keep an eye on the shared server ratio. The general rule is that use the higher version of the listener when there is a version mismatch between database and the listener. Step 2. Gather more information from listener log The first place you would look for the TNS-12518 error is the listener log. Usually the listener log would be located under $ORACLE_HOME/network/log directory. You can use ‘lsnrctl status’ command output to look for the location of the listener log file. $lsnrctl status – – – – – – Listener Parameter File /ora10g/home_ora10g/network/admin/listener.ora Listener Log File /ora10g/home_ora10g/network/log/listener.log – – – – – – listener log gives the complete error stack and the database service name to which the client tried to connect to. 19-SEP-2007 13:55:34 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=test.oracle.com)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.3)(PORT=36030)) * establish * test.oracle.com * 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 In the above example, listener log shows the complete error stack, the bottom error being 32 is the OS error. It also shows that the jdbc client from IP 10.10.10.3 has tried to connect to the database service ‘test.oracle.com’ and failed with the error 12518. Look for the lowest error in the stack. That is the error we have to concentrate on and try to resolve it. In the above example, the lower error is ‘Linux Error: 32: Broken pipe’. Step 3. Is service handlers in blocked state Check if the handlers are in blocked state. Check the output of the ‘lsnrctl services’. Examine the status information under the database service name. From the listener log you would know which database service was affected by the 12518 error, now with the output of the ‘lsnrctl services’ under that service name gather more information. Service “test.oracle.com” has 1 instance(s). Instance “db10g”, status READY, has 2 handler(s) for this service… Handler(s): “DEDICATED” established:9 refused:0 state:ready LOCAL SERVER “D000” established:10 refused:0 current:0 max:972 state:ready DISPATCHER <machine: oid.mohan.com, pid: 25908> (ADDRESS=(PROTOCOL=tcp)(HOST=oid.mohan.com)(PORT=33487)) The highlighted state should be in ‘ready’ state for the connection to be successful. If the state is in ‘blocked’ then the connection are not possible. The state of a handler could be in blocked state in the following scenario:
In shared server mode, the number of dispatchers should be set according to the load that you expect. ‘lsnrctl services’ output shows the maximum number(max:997) of connections that the dispatcher would accept and the number connections refused (refused:0) by this dispatcher. If any connections refused by the dispatcher, then consider increasing the number of dispatchers. If you are using PFILE edit init.ora and increase the dispatchers parameter. If you are using SPFILE you can dynamically increase the dispatchers parameter by the ‘alter system set’command. Step 4. Is local BEQ connection successful Check if local BEQ connection to the database works fine. It also verifies if the database is up and in good condition to accept the connection. If the database is down or in a hung state then a connection request to the database by the listener will not be possible. Connect to the database server via telnet or ssh and check if a local bequeath SQL*Plus connection works. In other words, issue: sqlplus username/password [Enter] This connection bypasses the listener and directly connects to the database via the BEQ (bequeath) protocol. If this fails, then the TNS-12518 listener error is simply a result of the database issue. One such error is: ORA-12560: TNS:protocol adapter error A possible cause for this error on Microsoft Windows servers, is that the Windows Database Service has not yet been created (common when creating a “standby” instance). Resolution for this would be to create the Windows Service first by using the “oradim” command (see the Database Admin guide for details on oradim and service creation). Step 5. Has number of processes reached its limit? If local BEQ is successful, check the below query SQL> select * from v$resource_limit; RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU ——————– ——————- ————— ———- ———- processes 249 250 250 250 sessions 54 82 280 280 – – – – – – – – – – – – Verify if the processes or sessions reached its limit value. If these database parameters reached its limit value, then consider increasing it accordingly. In the above example, the processes parameter has been set to 250. It’s MAX_UTILIZATION has reached the limit value of 250, so the processes parameter should be increased further to accomodate the number of incoming connections. Edit the init.ora and set the processes parameter to a higher value. By default, if you just increase the processes it is enough, the sessions value would automatically be increased. Check the alert.log for a corresponding error such as “ksvcreate” process failed, etc. Check timestamp against listener.log timestamps for causal relationship. Step 6. Are OS kernel parameters configured for optimum? Database is operated by a single user, normally it would be ‘oracle‘ user. At the Operating System level, there is a limit for the number of process spawned by a user. And also there is a limit for the total number of process running on the entire OS. The Oracle Database and the newly spawned processes would be owned by the ‘oracle‘ user. And so make sure that these values are set accordingly.On Unix these values are configured through the configurable OS kernel parameters and is specific to Operating Systems. You will have check your corresponding OS documentation for your OS. For example for HP-UX the configurable kernel parameters are, maxuprc Maximum number of processes for each non-root user nproc Maximum number of processes on the system Step 7. Does alert log have any errors? Look in alert log and look for any errors related to memory or process during the time the error TNS-12518 occurred in the listener log. If the alert log has any memory related errors, there there is a potential memory resource issue at the OS level. OS memory issues can be addressed by the below:
If there are any memory or process related error in the alert log during the time the TNS-12518 is logged in the listener log, then those errors in the alert log should be focused on and should be solved at first. Because, the errors in the alert log is the base error for the TNS-12518 in the listener log. However, the errors in the alert logs are not being discussed in this article, they are out of the scope of this article. Step 8. If using a statically defined SID_DESC in the listener.ora file for your sid, ensure that it is configured properly. A common mistake is to include a (PROGRAM=EXTPROC) parameter: (SID_LIST= (SID_DESC = (GLOBAL_DBNAME = ORCL.oracle.com) (SID_NAME = ORCL) (PROGRAM=extproc) (ORACLE_HOME = D:\oracle\product\10.2.0\db_1))) This misconfiguration occurs when the PLSExtproc SID_DESC is copied, pasted and edited in the listener.ora file. The inclusion of the PROGRAM line will cause an ORA-12518 to be returned to the client. Here’s the corrected SID_DESC for our example SID: |
