Dead Connection Detection is a useful feature of the Oracle database: it allows for the cleanup of “dead” sessions so they don’t linger around consuming memory and other system resources. The idea is simple: if the database detects that a client process is no longer connected to its server process, it cleans up. This can happen in many ways, in most cases this kind of problem is triggered by an end user.
UPDATE 260928: updated the article to consider client-side enhancements
A dead connection shouldn’t be confused with idle connections: an idle connection still maintains the network link between client and server process, except that there is no activity. Idle connections aren’t maintained/controlled via DCD, there are other tools in the database handling such cases.
As a by product, DCD can also help with overly eager firewalls forcibly removing seemingly idle network connections. I found the following posts and the references therein very useful:
- http://sve.to/2016/04/07/dead-connection-detection-oracle-database-12c/
- http://sve.to/2016/03/31/oracle-tns-12535-dead-connection-detection/
With Oracle 12c Release 1 Oracle changed the way DCD works by no longer solely relying on its network layer but rather pushing the functionality into the TCP stack on platforms that support it. This change in behaviour is also explained in an Oracle white paper from 2014.
For the first part of this post I set sqlnet.expire_time to 1 as per a MOS note I found, your value is probably different. The parameter is documented in the Net*8 reference, please use it to work out what the best value is for you. As others have pointed out, this parameter has to go into the RDBMS home, more specifically $ORACLE_HOME/network/admin/sqlnet.ora. It defaults to 0, which disables DCD.
I am using Oracle 19.4 on Oracle Linux 7.7 for this blog post. I have seen the same behaviour in 12.1.0.2 as well in my lab. Note that the described problem related to lsof might have been solved by now.
Background
In addition to the proven methods of checking whether TCP_KEEPALIVE is enabled for a given session I wanted to show another one. Using the ss(8) utility it is possible to show socket options. I also tried lsof(8) but on my system I couldn’t get it to print the options. Let’s get the address of a background process on my system and pass it to lsof:
SQL> select spid from v$process where addr = (select paddr from v$session where username = 'MARTIN');
SPID
------------------------
13656
SQL> exit
...
[root@server2 ~]# lsof -nP -p 13656 -T f
lsof: unsupported TCP/TPI info selection: f
lsof 4.87
Although the man-page reads:
-T [t] controls the reporting of some TCP/TPI information, also reported by net‐
stat(1), following the network addresses. In normal output the information
appears in parentheses, each item except TCP or TPI state name identified by
a keyword, followed by `=', separated from others by a single space:
<TCP or TPI state name>
QR=<read queue length>
QS=<send queue length>
SO=<socket options and values>
SS=<socket states>
TF=<TCP flags and values>
WR=<window read length>
WW=<window write length>
Not all values are reported for all UNIX dialects. Items values (when avail‐
able) are reported after the item name and '='.
When the field output mode is in effect (See OUTPUT FOR OTHER PROGRAMS.)
each item appears as a field with a `T' leading character.
-T with no following key characters disables TCP/TPI information reporting.
-T with following characters selects the reporting of specific TCP/TPI infor‐
mation:
f selects reporting of socket options,
states and values, and TCP flags and
values.
So let’s try something else: ss – another utility to investigate sockets.
Revealing socket options
I have used ss in the past when I didn’t have netstat available, which is more and more common now that netstat is deprecated and its designated successor is ss 😀
As far as I know you can’t limit ss to show information just for a PID, I use grep to limit the output. The output is in fact very wide, which is why this might not look pretty on the blog depending on whether the renderer decides to wrap output or not.
[root@server2 ~]# ss -nop | egrep 'NetidState|13656'
NetidState Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,4.412ms,0)
[root@server2 ~]#
I used the following options:
-nfor “Do not try to resolve service names”-ofor “Show timer information” and finally-pto “Show process using socket”.
The main option here is -o. As per the man page:
-o, --options
Show timer information. For tcp protocol, the output format is:
timer:(<timer_name>,<expire_time>,<retrans>)
<timer_name>
the name of the timer, there are five kind of timer names:
on: means one of these timers: tcp retrans timer, tcp early retrans timer and tail loss probe timer
keepalive: tcp keep alive timer
timewait: timewait stage timer
persist: zero window probe timer
unknown: none of the above timers
<expire_time>
how long time the timer will expire
<retrans>
how many times the retran occurs
With a little shell loop I can show how that timer is decrementing:
[root@server2 ~]# for i in $(seq 1 5); do ss -nop | grep 13656; sleep 1 ; done
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,20sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,19sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,18sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,17sec,0)
tcp ESTAB 0 0 [::ffff:192.168.100.22]:1521 [::ffff:192.168.100.21]:25780 users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,16sec,0)
[root@server2 ~]#
I think this is pretty neat.
Client Side Enhancements
This article has popped up in my top-hits section recently, and thus is a perfect candidate for an update.
A useful enhancement to DCD arrived on the client side. Beginning with Oracle Client 19c, EXPIRE_TIME can also be specified in a tnsnames.ora connect descriptor. This enables keepalive handling for an individual client connection and can help prevent firewalls from silently removing idle connections. Oracle recommends selecting a value slightly below half of the firewall’s idle-connection timeout. Older Oracle clients can use ENABLE=BROKEN for similar TCP keepalive behaviour. Current documentation shows EXPIRE_TIME as a parameter within the DESCRIPTION section of a connect descriptor. You can read more about EXPIRE_TIME and ENABLE=BROKEN in Oracle’s High Availability Overview and Best Practices.
Summary
Using the ss utility it is possible to check if a keepalive timer is implemented as a means to support DCD with 12.1 and later releases. Invoking ss hasn’t caused any problems on my system, but as with every such tool you need to ensure it’s safe to use before attempting to look at an important system.
