How to Check if Dead Connection Detection (DCD)

TOMSYAN

How to Check if Dead Connection Detection (DCD) is Enabled in 9i ,10g and 11g [ID 395505.1]
		修改时间 23-FEB-2011     类型 HOWTO     状态 PUBLISHED

In this Document
  Goal
  Solution
     Scenario
     Methods to check DCD functionality: 
     (1) Oracle Sqlnet Client Trace
     (2) Oracle Sqlnet Server Trace
     (3) Sniffer Trace
     (4) Strace or System level trace
  References

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

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.

Methods to check DCD functionality: 
(1) Oracle Sqlnet Client Trace

Enable the client tracing at support level with following parameters in the client side sqlnet.ora

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = CLIENT
TRACE_DIRECTORY_CLIENT= <oracle_home>/network/trace
TRACE_TIMESTAMP_ CLIENT = ON
TRACE_UNIQUE_CLIENT = ON

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.

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

  (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

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

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.

(2) Oracle Sqlnet Server Trace

Enable Oracle Sqlnet server traces by appending following information to the server side sqlnet.ora

TRACE_LEVEL_SERVER = 16 
TRACE_FILE_SERVER = SERVER
TRACE_DIRECTORY_SERVER= <oracle_home>/network/trace 
TRACE_TIMESTAMP_ SERVER = ON 
TRACE_UNIQUE_SERVER = ON

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

niotns: Enabling dead connection detection (2 min) //shows dcd enabled 2min

niotns: Not enabling dead connection detection.   // shows dcd is not enabled

After the recieve NSPTAC packet (accept) , you could see the timer being initialised if DCD is enabled

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

At the close of the connection

nstimarmed: entry 
nstimarmed: no timer allocated // no dcd is enabled
nstimarmed: normal exit

 

nsclose: entry 
nstimarmed: entry 
nstimarmed: timer is armed, with value //indicates DCD was enabled
nstimarmed: normal exit 
nstimclear: entry 
nstimclear: normal exit

 

(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.

(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
Note 438923.1 How To Track Dead Connection Detection(DCD) Mechanism Without Enabling Any Client/Server Network Tracing

ReferencesBUG:5475041 - PROBE PACKETS ARE NOT SENT IF SQLNET.EXPIRE_TIME IS GREATER THAN 1
BUG:5573896 - DCD IS NOT WORKING IF SET MORE THAN 1 MINUTE
BUG:3588399 - SQLNET.EXPIRE_TIME IS DOUBLED ON FIRST PASS THRU