联系：手机/微信(+86 17813235971) QQ(107644445)
标题：TNS-12518: TNS:listener could not hand off client connection
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户反馈业务经常性出现数据库连接异常,通过工具访问ORACLE进程报ORA-12170
通过分析发现lsnrctl status几乎hang住，tnsping延迟特别大
进一步分析监听日志发现TNS-12518: TNS:listener could not hand off client connection错误
12-MAR-2024 15:34:50 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)
(HOST=__jdbc__)(USER=Administrator))(SERVICE_NAME=ilas)) * (ADDRESS=(PROTOCOL=tcp)
(HOST=ip)(PORT=52854)) * establish * ilas * 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
根据经验和Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection描述,检查监听文件配置
[oracle@xff admin]$ cat listener.ora
# listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:/home/u01/app/oracle/product/11.2.0/dbhome_1/bin/oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/u01/app/oracle
根据经验和客户的业务进行分析,确认他们不会使用 external procedures方式访问数据库,直接修改监听配置
[oracle@xff admin]$ cat listener.ora
# listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = xff)
(ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1)
(GLOBAL_DBNAME = xff)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/u01/app/oracle
然后reload配置,再使用lsnrctl status查看结果秒出,tnsping也非常快
让客户测试应用也恢复正常,一切ok,问题在最小修改的情况下解决,和最初供应商建议的重装系统,双机,数据库等解决方案大大简化
