How to Check if Dead Connection Detection (DCD)

TOMSYAN 发表于 2011-12-23 03:05

<DIV>
<TABLE cellSpacing=6 cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD vAlign=top align=left colSpan=6>How to Check if Dead Connection Detection (DCD) is Enabled in 9i ,10g and 11g </TD></TR>
<TR>
<TD vAlign=top align=left colSpan=6></TD></TR>
<TR>
<TD vAlign=top width="25%" colSpan=2> </TD>
<TD vAlign=top noWrap width="50%" colSpan=3>修改时间 23-FEB-2011     类型 HOWTO     状态 PUBLISHED</TD>
<TD vAlign=top> </TD></TR></TBODY></TABLE>
In this Document   <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#GOAL" target=_blank>Goal</A>   <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#FIX" target=_blank>Solution</A>      <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#aref1" target=_blank>Scenario</A>      <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#aref2" target=_blank>Methods to check DCD functionality: </A>      <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#aref3" target=_blank>(1) Oracle Sqlnet Client Trace </A>      <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#aref4" target=_blank>(2) Oracle Sqlnet Server Trace</A>      <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#aref5" target=_blank>(3) Sniffer Trace</A>      <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#aref6" target=_blank>(4) Strace or System level trace </A>   <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=395505.1#REF" target=_blank>References</A> 

Applies to: Oracle Net Services - Version: 9.2.0.4.0 to 11.2.0.2 - Release: 9.2 to 11.2 Information in this document applies to any platform. Checked for relevance on 9-APR-2010. <A name=GOAL></A>GoalYou have enabled Dead Connection Detection (DCD) in the SQLNET.ORA file on the database server which is of version 9i or 10g and you wish to verify whether it is working. <A name=FIX></A>Solution
We could verify whether DCD is enabled or not by any one of the following mechanism
(1) Oracle Sqlnet Client Trace      (1.a) Using Pre-9.2.0.4 Oracle clients      (1.b) Using Oracle Clients version > 9.2.0.4 (2) Oracle Sqlnet Server Trace (3) Sniffer Trace (4) Strace or System level trace<A name=aref1></A> Scenario
You have set the SQLNET.EXPIRE_TIME = x (minutes) in the server sqlnet.ora 
How Dead Connection Detection is Triggered
Let us take an example. Suppose we set the sqlnet.expire_time = 2 and the timer got armed at 10.00 am, then the DCD packets will be sent only if there is no network(I/O) activity for that connection in the next 2 min. i.e. till 10.02 am. If there is an i/o in between 10 to 10.02 then the connection is not dead one as the activity took place on that particular connection, which means the connection is alive and hence the DCD packets will not be sent for this period of time. So, at any point of time, the DCD packets will only be sent if there is no activity for the full span of specified time interval. As the first connection operation itself considered a successul I/O, the first DCD probe packet would go only after 2 * expire_time and successive one's would be sent every expiry_time provided no activity in that next span too.<A name=aref2></A>Methods to check DCD functionality:  <A name=aref3></A>(1) Oracle Sqlnet Client Trace
Enable the client tracing at support level with following parameters in the client side sqlnet.ora
<DIV class=kmcodeblock style="WIDTH: 95%">TRACE_LEVEL_CLIENT = 16 TRACE_FILE_CLIENT = CLIENT TRACE_DIRECTORY_CLIENT= <oracle_home>/network/trace TRACE_TIMESTAMP_ CLIENT = ON TRACE_UNIQUE_CLIENT = ON</DIV>
Leave the session on the client idle for atlest for x*2 minutes(twice of 'x' minutes)  ie, if x=2 in the server sqlnet.ora, then leave the client connection idle for atleast 4 minutes. At this time, if you check the client trace, the client will be on nioqrc: exit. 
The reason for the (2 * expire time) set initially that is for the first probe is due to the above logic on how DCD works that is DCD probe packets will be sent only if there is no activity for full span of specified time interval.     (1.a) Using Pre-9.2.0.4 Oracle clients For pre-9.2.0.4 oracle clients you can actually see the null packet of 10 bytes been recieved every x minutes after this period.
<DIV class=kmcodeblock style="WIDTH: 95%">nsprecv: 10 bytes from transport  nsprecv: tlen=10, plen=10, type=6  nsprecv: packet dump  nsprecv: 00 0A 00 00 06 00 00 00 |........|  nsprecv: 00 00 |.. | nsprecv: normal exit  nsrdr: got NSPTDA packet  nsrdr: NSPTDA flags: 0x0  nsrdr: normal exit  nsdo: got "null" packet ==> Got Null packet here </DIV>
   (1.b) Using Oracle Clients version > 9.2.0.4 After the idle time greater than x*2 minutes(here 4 minutes), execute any  query, like 'select * from dual'; You will see in the client trace following information after the query is sent to server Here for example the query was executed after 8 minutes of idle time
<DIV class=kmcodeblock style="WIDTH: 95%">..14:52:04:734] nioqrc: exit ..15:01:51:546] nioqsn: entry ... nsprecv: 30 bytes from transport nsprecv: tlen=30, plen=10, type=6 nsprecv: 20 bytes to leftover nsprecv: packet dump nsprecv: 00 0A 00 00 06 00 00 00 |........| nsprecv: 00 00 |.. | nsprecv: normal exit nsrdr: got NSPTDA packet nsrdr: NSPTDA flags: 0x0 nsrdr: normal exit nsdo: got "null" packet nsrdr: entry nsrdr: recving a packet nsprecv: entry nsprecv: 20 bytes from leftover nsprecv: tlen=20, plen=10, type=6 nsprecv: 10 bytes to leftover nsprecv: packet dump nsprecv: 00 0A 00 00 06 00 00 00 |........| nsprecv: 00 00 |.. | nsprecv: normal exit nsrdr: got NSPTDA packet nsrdr: NSPTDA flags: 0x0 nsrdr: normal exit nsdo: got "null" packet nsrdr: entry nsrdr: recving a packet nsprecv: entry nsprecv: 10 bytes from leftover nsprecv: tlen=10, plen=10, type=6 nsprecv: packet dump  nsprecv: 00 0A 00 00 06 00 00 00 |........|  nsprecv: 00 00 |.. | nsprecv: normal exit nsrdr: got NSPTDA packet nsrdr: NSPTDA flags: 0x0 nsrdr: normal exit  nsdo: got "null" packet</DIV>
This confirms that the DCD packet is sent to the client from the respective database server. The null packets are actually written to the socket , but they are read only when the client becomes active.<A name=aref4></A>(2) Oracle Sqlnet Server Trace
Enable Oracle Sqlnet server traces by appending following information to the server side sqlnet.ora 
<DIV class=kmcodeblock style="WIDTH: 95%">TRACE_LEVEL_SERVER = 16  TRACE_FILE_SERVER = SERVER TRACE_DIRECTORY_SERVER= <oracle_home>/network/trace  TRACE_TIMESTAMP_ SERVER = ON  TRACE_UNIQUE_SERVER = ON</DIV>
Make a client connection to the server and track the server trace which was generated for the client by looking in to the trace directory. Leave the client session idle atleast for x*2 minutes At the start of the trace we can notice whether DCD is enabled or not for the process by 
<DIV class=kmcodeblock style="WIDTH: 95%">niotns: Enabling dead connection detection (2 min) //shows dcd enabled 2min</DIV>
<DIV class=kmcodeblock style="WIDTH: 95%">niotns: Not enabling dead connection detection.   // shows dcd is not enabled</DIV>
 After the recieve NSPTAC packet (accept) , you could see the timer being initialised if DCD is enabled
<DIV class=kmcodeblock style="WIDTH: 95%">nstimini: initializing LTM in asynchronous mode nstimini: normal exit nstimstart: entry nstimstart: starting timer at <**timestamp**> nstimset: entry nstimset: normal exit nstimstart: normal exit nsconbrok: timer created for connection</DIV>
 At the close of the connection 
<DIV class=kmcodeblock style="WIDTH: 95%">nstimarmed: entry  nstimarmed: no timer allocated // no dcd is enabled nstimarmed: normal exit </DIV>
 
<DIV class=kmcodeblock style="WIDTH: 95%">nsclose: entry  nstimarmed: entry  nstimarmed: timer is armed, with value //indicates DCD was enabled nstimarmed: normal exit  nstimclear: entry  nstimclear: normal exit</DIV>
 <A name=aref5></A>(3) Sniffer Trace
Enable Sniffer trace with help of Ethreal / wireshark tools and capture the events on client side. After narrowing the communication of the oracle client in picture and server process you could actually see that 10 bytes of null packet is been sent as TNS and TCP ack is recieved. 
 
 
This confirms DCD packet is recieved by the client.<A name=aref6></A>(4) Strace or System level trace 
 You could check whether DCD is enabled or not by tracing the oracle server process with OS tracing. For example strace on oracle server process in linux platform will reveal whether dcd is enabled or not. Refer the below note for more details <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=438923.1" target=_blank>Note 438923.1</A> How To Track Dead Connection Detection(DCD) Mechanism Without Enabling Any Client/Server Network Tracing <A name=REF></A>References<A href="https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=5475041" target=_blank>BUG:5475041</A> - PROBE PACKETS ARE NOT SENT IF SQLNET.EXPIRE_TIME IS GREATER THAN 1 <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=5573896" target=_blank>BUG:5573896</A> - DCD IS NOT WORKING IF SET MORE THAN 1 MINUTE <A href="https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=3588399" target=_blank>BUG:3588399</A> - SQLNET.EXPIRE_TIME IS DOUBLED ON FIRST PASS THRU </DIV>

页: [1]

Chinaunix's Archiver

How to Check if Dead Connection Detection (DCD)