Home » RDBMS Server » Networking and Gateways » How often should I take statspack if I were to detect TNS-12518: TNS:listener could not hand off cli (11.2.0.1, Redhat Enterprise Level 5.8)
How often should I take statspack if I were to detect TNS-12518: TNS:listener could not hand off cli [message #649822] Thu, 07 April 2016 09:11 Go to next message
juniordbanewbie
Messages: 170
Registered: April 2014
Senior Member
Dear all,

I encounter the following error

<msg time='2016-03-22T20:09:32.447+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='cluster1.mes.solar.com'
 host_addr='10.10.20.1'>
 <txt>22-MAR-2016 20:09:32 * (CONNECT_DATA=(SID=o11g)(CID=(PROGRAM=emagent)(HOST=cluster1.mes.solar.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.20.3)(PORT=58379)) * establish * o11g * 12518
 </txt>
</msg>
<msg time='2016-03-22T20:09:32.473+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='cluster1.mes.solar.com'
 host_addr='10.10.20.1'>
<msg time='2016-03-22T20:09:32.473+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='cluster1.mes.solar.com'
 host_addr='10.10.20.1'>
 <txt>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
 </txt>


I discover that my client uses only dedicated connection

SERVER      COUNT(0)
--------- ----------
DEDICATED         62



when I did a lsnrctl service

[oracle@cluster1 ~]$ lsnrctl service

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "o11g.berlin.solar.com" has 1 instance(s).
  Instance "o11g", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:874221 refused:34
         LOCAL SERVER
The command completed successfully



one reason could be parameter processes has been set too low, but in order to be sure that processes is in fact low at the time of incident, the only way to verify is to have frequent collection of v$resource_limit, so how often should I snap v$resource_limit through statspack.snap?

many thanks
Re: How often should I take statspack if I were to detect TNS-12518: TNS:listener could not hand off cli [message #649823 is a reply to message #649822] Thu, 07 April 2016 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65207
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no relation between this error and PROCESSES database parameter.
StatsPack is irrelevant for this problem.

ORA-12518: TNS:listener could not hand off client connection
 *Cause: The process of handing off a client connection to another process
 failed.
 *Action: Turn on listener tracing and re-execute the operation. Verify
 that the listener and database instance are properly configured for
 direct handoff.  If problem persists, call Oracle Support.
 *Comment: The problem can be worked around by configuring dispatcher(s)
 to specifically handle the desired presentation(s), and connecting
 directly to the dispatcher, bypassing the listener.

You can read direct handoff

Re: How often should I take statspack if I were to detect TNS-12518: TNS:listener could not hand off cli [message #649824 is a reply to message #649822] Thu, 07 April 2016 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 25720
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/search?q=Linux+Error%3A+32%3A+Broken+pipe
Re: How often should I take statspack if I were to detect TNS-12518: TNS:listener could not hand off cli [message #649826 is a reply to message #649822] Thu, 07 April 2016 09:52 Go to previous message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
with regard to your question (irrelevant though it may be Smile ) I would never run a statspack snapshot more frequently than every 15 minutes. It queries all those V$ views, which puts a terrible strain on the instance. Querying v$ views, particularly those populated from SGA, is expensive.
Previous Topic: Monitoring Network speed between Target and Catalog Servers.
Next Topic: how to connect database in vmware
Goto Forum:
  


Current Time: Fri Nov 24 04:49:00 CST 2017

Total time taken to generate the page: 0.06482 seconds