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

业务反馈应用连接数据库一会成功一会失败，电话联系让协助排除一下问题，于是登录数据集群检查日志发下如下报错：

(CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=weblogic))
(SERVICE_NAME=xxx)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)
(USER=weblogic))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1xxxxxx19)(PORT=37521)) * 
establish * xxxxx* 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

第一反应应该是某一个节点监听有一些异常，于是在数据库本地各个节点上进行连接测试，结果发现1节点正常，2节点异常。继续检查2节点监听状态：

Service "xxx" has 1 instance(s).
  Instance "xxx", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3633 refused:3633  state:ready
         LOCAL SERVER
Service "e758f6c176ea1fe7e05354ec12ace33e" has 1 instance(s).
  Instance "xxx", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3633 refused:3633  state:ready
         LOCAL SERVER
The command completed successfully

发现有大量的拒绝记录。结合TNS-12518故障报错检索官方记录：
Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection (Doc ID 550859.1)

In this Document
Purpose
Troubleshooting Steps	
 	Section I: Steps to approach ORA-12518/TNS-12518 Error:
 	Section II: Commonly Known Errors:
 	Error: 32: Broken pipe
 	Error: 11: Resource temporarily unavailable
 	Error: Connection Pooling limit reached
 	Error: 24: Too many open files
 	Section III: Errors Specific to Windows
 	Error: 2: No such file or directory 
 	Error: 233: Unknown error
 	Error: 54: Unknown error
 	Error: 10022: Unknown error
References
....
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.x.x)(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.x.x 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'.
....

Copyright (c) 2022, Oracle. All rights reserved. Oracle Confidential.


Click to add to Favorites		Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection (Doc ID 550859.1)	To Bottom	

In this Document
Purpose
Troubleshooting Steps
 	
 	Section I: Steps to approach ORA-12518/TNS-12518 Error:
 	Section II: Commonly Known Errors:
 	Error: 32: Broken pipe
 	Error: 11: Resource temporarily unavailable
 	Error: Connection Pooling limit reached
 	Error: 24: Too many open files
 	Section III: Errors Specific to Windows
 	Error: 2: No such file or directory 
 	Error: 233: Unknown error
 	Error: 54: Unknown error
 	Error: 10022: Unknown error
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.2.0.1 [Release 11.2 to 12.2]
Oracle Net Services - Version 10.1.0.5 to 12.2.0.1 [Release 10.1 to 12.2]
Information in this document applies to any platform.
PURPOSE
This article discusses  how to troubleshoot the ORA-12518/TNS-12518 listener errors.

TROUBLESHOOTING STEPS
Section I: Steps to approach ORA-12518/TNS-12518 Error:
 

ORA-12518/TNS-12518 indicates a problem while listener hands off the client connection to the server process or dispatcher process.TNS-12518 is logged in the listener log. The client might receive ORA-12518 or some other disconnection errors like ORA-12537. Once TNS-12518 is noted in the listener log, follow the below steps to approach and resolve this error.

Let us have a small discussion about how actually database connections are made:

In Dedicated mode, database client contacts listener and supplies the SERVICE NAME of the database. Then listener spawns a dedicated server process and hands off the client connection to this dedicated server process. TNS-12518 indicates a problem while handing off the client connection to the server process.

In Shared Server mode, database client contacts listener and supplies the SERVICE NAME of the database. Then listener hands off the client connection to one of the dispatcher configured for that service. TNS-12518 indicates a problem while handing off the client connection to the dispatcher server process.


Though this error is logged in the listener log, the listener is just the messenger, ORA-12518/TNS-12518 is mostly related to RDBMS and OS resources.



Step 1. Is listener version compatible to the database

Check if you are using compatible listener version for your database version.

If the database is 8i then use 8i or 9i listeners only. 10g listeners are not compatible to work with 8i databases.
For 9i databases, 9i or 10g listeners can be used.
For 10g databases, only 10g listeners can be used.

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.x.x)(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.x.x 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. Are 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: <hostname>, pid: 25908>
         (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(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:
i. The database parameter processes reached its value.
ii. The database is in the process of startup or shutting down.

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 a 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:
i.   Make sure that the OS has been configured with the enough Swap memory. In case of Windows it is called as Virtual Memory.
ii.  Reduce the size of SGA, thus the newly spawned server process will have some more system memory available.
iii. Reduce the PGA size, so that the newly spawned server process would occupy less memory.
iv. If you are in DEDICATED mode, try switching to MTS mode.

To address memory issue for 32-bit Windows: Refer Note 371983.1

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:

  (SID_LIST=
     (SID_DESC =
     (GLOBAL_DBNAME = ORCL.oracle.com) 
     (SID_NAME = ORCL)
     (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)))



Section II: Commonly Known Errors:

This section lists some of the known and reported errors. It is also recommended that you refer the Section I above for a generic troubleshooting approach to the error TNS-12518.

Below shows example error stack that can be found in the listener log. The last line in the error stack shows the actual operating system name. Depending on the OS, only the operating system name would be different. For example, if you encounter error 32: Broken pipe, according to the OS, the last line in the error stack would be different only by the OS name, as shown below.

Solaris Error: 32: Broken pipe
HPUX Error: 32: Broken pipe
Linux Error: 32: Broken pipe

Error: 32: Broken pipe

Error stack in listener log:

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
       IBM/AIX RISC System/6000 Error: 32: Broken pipe


Cause:
The error 32 indicates the communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process.

Action:
1.  One of reason would be processes parameter being low, and can be verified by the v$resource_limit view.
2. In Shared Server mode, check the 'lsnrctl services' output and see if the dispatcher has refused any connections, if so, then consider increasing the number of dispatchers.
3. Check the alert log for any possible errors.
4. Memory resource is also another cause for this issue. Check the swap, memory usage of the OS.
5. If RAC/SCAN or listener is running in separate home, check the following note:

Note: 1069517.1  ORA-12537 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User

根据文档提供的进一步信息检索Note: 1069517.1，

 In environments where the listener is not run in the same ORACLE_HOME where the database resides, the listener owner (including SCAN listener) may not be able to access the oracle binary in the database home.  This is common in RAC or whenever a GRID_HOME and a database ORACLE_HOME are installed.

在监听程序未在数据库所在的同一ORACLE_HOME中运行的环境中，监听程序所有者(包括扫描监听程序)可能无法访问数据库主目录中的Oracle二进制文件。这在RAC中或在安装GRID_HOME和数据库ORACLE_HOME时很常见。
于是跟进排查：

ls -l $ORACLE_HOME/bin/oracle
-rwxr-s--x 1 oracle asmadmin 448004240 Aug 29 09:28 /u01/app/oracle/product/19.0.0.0/db_1/bin/oracle

官方提示The permission “-rwxr-x–x” is wrong as it’s missing suid bit, oracle binary should have permission of 6751:即 rwsr-s–x

chmod 6751 /u01/app/oracle/product/19.0.0.0/db_1/bin/oracle

重新加载集群监听后，测试恢复正常。
此外留意到官方还提供了另外一种方式用setasmgidwrap设置oracle对应文件权限，也解决该问题，有兴趣朋友可以自己了解一下。