Dead Connection Detection

TCP/IP is a connection-oriented protocol, and provides packet timeout and retransmission in order to guarantee the safe and sequenced order of data packets. If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out.
After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

1. Dead Connection Detection

Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including Oracle Net8. DCD detects when a partner in a SQL*Net V2 client/server or server/server connection has terminated unexpectedly, and releases the resources associated with it.

DCD is initiated on the server when a connection is established. At this time, SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a “probe” packet to the client. The probe is an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.

If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection’s resources.

The client may be running any supported SQL*Net V2 release. DCD is more resource-intensive than similar mechanisms at the protocol level.

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

To Configure Dead Connection Detection (DCD)

Implement by:

changing SQLNET.EXPIRE_TIME = 1 (Minutes) to the sqlnet.ora file

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

If the client side connection is still connected and responsive, the client sends a response packet back to the database server, resetting the timer, and another packet will be sent when the next interval expires , assuming no other activity on the connection

If the client fails to respond to the DCD probe packet:
• The Server side process is marked as a dead connection and
• The database Process Monitor (PMON) performs the clean-up of the database processes / resources

Dead Connection Detection:
• DCD initiates clean up of OS and database processes that have disconnected / terminated abnormally
• DCD will not initiate clean-up for sessions that are still connected

2. tnsnames.ora

Client side SQL*Net connections do not enable keepalive for TCP connections by default. However, it is possible to enable this by adding the ENABLE=BROKEN parameter to the SQL*Net connect string in the tnsnames.ora file on the server..

Sample TNS alias to enable keepalive (notice the ENABLE=BROKEN clause):

VIS_BALANCE =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = rh8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rh6)(PORT = 1521)))