文档章节

TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection

rootliu
 rootliu
发布于 2017/08/18 09:23
字数 2571
阅读 8
收藏 0
点赞 0
评论 0
TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection [ID 550859.1]  
 
  修改时间 12-DEC-2008     类型 TROUBLESHOOTING     状态 PUBLISHED  

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
     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: 12: Not enough space
     Error: Connection Pooling limit reached
     Error: 2: No such file or directory
     Section III: Errors Specific to Windows
     Error: 233: Unknown error
     Error: 54: Unknown error

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 10.2.0.1.0
Information in this document applies to any platform.

Purpose

This article discusses about how to approach ORA-12518/TNS-12518 and troubleshoot it for the resolution.

Last Review Date

January 29, 2008

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details


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.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 3Is 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:
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 4Is 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 5Is 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.


Step 6Is OS kernal 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 kernal 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 kernal parameters are,

maxuprc Maximum number of processes for each non-root user
nproc      Maximum number of processes on the system






Step 7Does 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.

______________________________________________________________________________________________________________________________________

Error: 11: Resource temporarily unavailable

Error stack in listener log:
TNS-12518: TNS:listener could not hand off client connection
TNS-12549: TNS:operating system resource quota exceeded
TNS-12560: TNS:protocol adapter error
   TNS-00519: Operating system resource quota exceeded
    IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable


Cause:
As the error indicates operating system resource has exceeded.

Action:
1. Increase the appropriate OS kernal parameters for 'maximum number of processes allowed per user'.
For example for HP-UX the parameters are maxuprc and nproc.


______________________________________________________________________________________________________________________________________


Error: 12: Not enough space

Error stack in listener log:
TNS-12518: TNS:listener could not hand off client connection
   TNS-12549: TNS:operating system resource quota exceeded
     TNS-12560: TNS:protocol adapter error
       TNS-00519: Operating system resource quota exceeded
         IBM/AIX RISC System/6000 Error: 12: Not enough space


Cause:
This is a memory related issue. The error indicates that there is not enough memory available to spawn and hand off the client connections.
Typical problems are:
- Out of system memory / swap
- Out of process slots in the process table
- Streams resources depleted
- Out of File Handles
- sga memory usage


Action:
1. Check in the alert log for any possible memory related error.
2. Increase swap/Virtual memory if possible the available memory.
3. SGA and PGA can be reduced to address the memory consumption.
4. MTS mode can be used to reduce the amount of process and memory consumption.

________________________________________________________________________________________________________________________________________


Error: Connection Pooling limit reached

Error stack in listener log:
TNS-12518 TNS:listener could not hand off client connection
TNS-12564 TNS:connection refused
   TNS-12602 TNS: Connection Pooling limit reached


Action:
1. Try increasing initial number of dispatcher.

_______________________________________________________________________________________________________________________________________

Error: 2: No such file or directory

Error stack in listener log:
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:proto adapter error
   TNS-00530: Proto adapter error
    32-bit Windows Error: 2: No such file or directory


Error Description:
ERROR_FILE_NOT_FOUND
2
The system cannot find the file specified.

Cause:
This indicates the database service is not actually available


Ation:
1.Verify if the inteneded database really up and accepting local BEQ connections.

________________________________________________________________________________________________________________________________________
=========================================================================================================================

Section III: Errors Specific to Windows

It is also recommended that you refer the Section I above for a generic troubleshooting approach to the error TNS-12518.

This section briefly describes about the errors that are encountered on Windows Operating System. TNS-12518 most commonly occurs on 32-bit OS due to its memory constraint, however TNS-12518 can occur on 64-bit OS as well.


_______________________________________________________________________________________________________________________________________  

Error: 233: Unknown error

Error stack in listener log:
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: 233: Unknown error


Error Description:
ERROR_PIPE_NOT_CONNECTED
233
No process is on the other end of the pipe.

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


Action:
Refer Note 371983.1

_______________________________________________________________________________________________________________________________________

Error: 54: Unknown error

Error stack in listener log:
TNS-12518: TNS:listener could not hand off client connection
TNS-12571: TNS:packet writer failure
   TNS-12560: TNS:protocol adapter error
    TNS-00530: Protocol adapter error
      32-bit Windows Error: 54: Unknown error


Error Description:
ERROR_NETWORK_BUSY
54
0x36
The network is busy.


Cause:
This indicates a bottleneck at the network layer(TCP/IP).


Action:
1.Try increasing dispatchers and shared servers.

本文转载自:http://blog.sina.com.cn/s/blog_53a1165e0100nisv.html

共有 人打赏支持
rootliu
粉丝 2
博文 233
码字总数 2796
作品 0
海淀
数据库管理员
【故障|监听】TNS-12518、TNS-00517和 Linux Error:32:Broken pipe

【故障|监听】TNS-12518、TNS-00517和 Linux Error:32:Broken pipe 1.1 BLOG文档结构图 1.2 前言部分 1.2.1 导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到...

小麦苗best
06/29
0
0
使用java连接oracle数据库

直接上代码: <%! public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver" ; public static final String DBURL = "jdbc:oracle:thin:@localhost :1521:yangyihong" ; pu......

ydww
2013/09/17
420
2
oracle 数据库网络连接 配置文件Tnsnames.ora

TNSNAMES.ORA is a SQLNet configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOMENETWORKADMIN dire......

Oscarfff
2016/04/29
87
0
ORA-12537 TNS-12560 TNS-00530 ora-609解决

oracle 11g不能连接,卡住,ORA-12537 TNS-12560 TNS-00530 TNS-12502 tns-12505 ora-609 Windows Error: 54: Unknown error 解决方案。 今天折腾了一下午,为了查这个问题。。找了N多方案,...

lanybass
06/19
0
0
写了一条for循环测试 自己的项目框架,能顶得住多少查询,结果是 查询到了 第 191 次、171次查询的时候,报了这个错误,有没有什么优化的建议

用的是 S2SH 的框架 [WARN] 2014-08-21 16:32:46,593 org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null [ERROR] 2014-08-21 16:32:46,593 org.hibernate.util.JDBC......

Baclk5
2014/08/21
405
7
jboss数据源创建连接失败:ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Throwable while attempting to get a new connection: null org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: List......

刘洋
2012/04/25
1K
1
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor The Connection descriptor used by the client was:...

ivan1989
2015/07/09
540
1
centos7更改机器名后oracle无法使用

1、更改机器名称  ~]# hostnamectl --static set-hostname oracle 2、 ~]# su - oracle Last login: Tue Apr 12 10:11:34 CST 2016 from 192.168.30.250 on pts/0 [oracle@oracledb ~]$ l......

lcpljc
2016/04/12
0
0
centos oracle11g dataguard备库开启只读时报错

主库ip:192.168.186.128 备库ip:192.168.186.129 备库 SQL> alter database open read only; alter database open read only * ERROR at line 1: ORA-10458: standby database requires reco......

熊英
2015/01/07
460
0
Oracle常见故障——Listener类:Hang、Crash及连接风暴的判断

目录 监听状态正常,应用反馈时断时连 Listener进程crash ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务 11G SCAN LISTENER无法注册服务故障 Listener hang TNS-12535 TNS-00505处...

杨志洪 胡杰
2015/12/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

JavaEE——JavaScript

声明:本栏目所使用的素材都是凯哥学堂VIP学员所写,学员有权匿名,对文章有最终解释权;凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。 JavaScript 内置对象 String对象方法: date对象...

凯哥学堂
11分钟前
0
0
Git 远程代码回滚master

方式一(推荐): git revert commit-id 方式二(不推荐):不推荐原因:推送到线上后,其它开发人员需要重新clone git reset --hard commit-id git push origin HEAD --force...

浮躁的码农
11分钟前
0
0
Elasticesearch学习(7)—— ES查询与Java API的对应关系

1、普通查询 类型 ES查询语句 Java查询实现 结果 查询格式 { "query": { "bool": { "must": [], "must_not": [], "should": [], "filter": [] } }, "from": 0, "size": 10, "sort": [] } Que......

叶枫啦啦
11分钟前
15
0
getElementsByClassName()与getElementById()区别

1.document.getElementsByClassName() 返回的是数组 使用:document.getElementsByClassName("className")[0].innerText='具体内容' 2.document.getElementById() 返回的是单个元素 使用:d......

botkenni
22分钟前
0
0
MyBatis入门

一、安装 <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>x.x.x</version></dependency> 二、从 XML 中构建 SqlSessionFactory String r......

一个yuanbeth
23分钟前
0
0
聊聊spring cloud的LoadBalancerAutoConfiguration

序 本文主要研究一下spring cloud的LoadBalancerAutoConfiguration RibbonAutoConfiguration spring-cloud-netflix-ribbon-2.0.0.RC2-sources.jar!/org/springframework/cloud/netflix/ribb......

go4it
25分钟前
0
0
【转】使用Lombok来优雅的编码

前言 Lombok 是一种 Java™ 实用工具,可用来帮助开发人员消除 Java 的冗长,尤其是对于简单的 Java 对象(POJO)。它通过注解实现这一目的。 正文 添加依赖 在 pom.xml 文件中添加相关依赖:...

HAVENT
27分钟前
0
0
Dubbo 源码解读 —— 可支持序列化及自定义扩展

一、概述 从源码中,我们可以看出来。目前,Dubbo 内部提供了 5 种序列化的方式,分别为 fastjson、Hessian2、Kryo、fst 及 Java原生支持的方式 。 针对不同的序列化方式,对比内容如下: 名...

Ryan-瑞恩
35分钟前
0
0
MySQL内存设置—— MySQL server has gone away

set global max_allowed_packet=268435456

一梦心草
44分钟前
0
0
推导式

列表、集合和字典推导式 列表推导式是Python最受喜爱的特性之一。它允许用户方便的从一个集合过滤元素,形成列表,在传递参数的过程中还可以修改元素。形式如下: [expr for val in collect...

火力全開
50分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部