oracle application; client connection closed

[Madanagopalan S]

when user oracle application(forms)hangs on client side the corresponding server process is not get
disconnected and its still running on the server. I can able to find out the process for the IDLE connection. Is there anyway to timeout the server
process connection?

we are using oracle application 11 and oracle
database 8.0.5, unix HP-UX 11.0, N class.

[Steven Sim Kok Leong · Accepted solution]

Hi,

The orthodox method is to set tcp_keepalive_interval via ndd. I have extracted its description for your convenience.

If the user process already terminates at the Oracle client end, the server process will terminate when it reaches the tcp_keepalive_interval from the time the user process was idle/terminated.

=========================================
Interval for sending keep-alive probes.

If any activity has occurred on the connection or if there is any unacknowledged data when the time-out period expires, the timer is simply restarted. If the remote system has crashed and rebooted, it will presumably know nothing about this connection, and it will issue an RST in response to the ACK. Receipt of the RST will terminate the connection.

If the keepalive packet is not ACK'd by the remote TCP, the normal retransmission time-out will eventually exceed threshold R2, and the connection will be terminated.

With this keepalive behavior, a connection can time-out and terminate without actually receiving an RST from the remote TCP.
[10000, 10*24*3600000] Default: 2 * 3600000 (2 hours)
=========================================

The unorthodox method (highly discouraged) is to make use of tcp_discon_by_addr which allows you to terminate specific connections as reflected by tcp_status, all via ndd.

Hope this helps. Regards.

Steven Sim Kok Leong
Brainbench MVP for Unix Admin
http://www.brainbench.com

[Steven Sim Kok Leong]

Hi,

To quote some usage examples,

# ndd -set /dev/tcp tcp_keepalive_interval 1800000 # max. idle time for connection is half an hr
# ndd -set /dev/tcp tcp_keepalive_interval 900000 # max. idle time for connection is 15 mins

Hope this helps. Regards.

Steven Sim Kok Leong
Brainbench MVP for Unix Admin
http://www.brainbench.com

[Chad Molina]

There is a feature in Oracle called Dead-connection detection (DCD) which was designed specifically for this puprose. If you enter into your $ORACLE_HOME/network/admin/sqlnet.ora file SQLNET.expire_time = 10 it will detect when one side of the connection is no longer there and release the resources accordingly. This is useful for the dreaded reboot, and End Task methods of shutting down a forms app.

For more info
http://metalink.oracle.com/
Metalink Note:1013364.6
Subject: WHAT IS DEAD CONNECTION DETECTION (DCD)?

[Madanagopalan S]

Hi Chad Molina,
Is this applicable to oracle applications also?
because oracle applications are web based requests.

Regards,
Madan.

[Steven Sim Kok Leong]

Hi,

I am using Oracle database 8.0.6 in my environment. In my environment at least, SQLNET.EXPIRE_TIME in sqlnet.ora had not been effective in killing off HUNG connections.

As such, I reduced TCP_KEEPALIVE_INTERVAL from 2 hours to just 1/2 hour. Connections idling for half an hour were terminated and its corresponding process killed. The TCP_KEEPALIVE_INTERVAL value was also hardcoded in the net startup configuration script ie /etc/rc.config.d/nddconf.

Another point to note. The INACTIVE status from v$session does not necessary imply that a process is hung or not running ie. a running sqlnet connection doing a select statement is also flagged as INACTIVE much to my dismay.
Instead, v$session_wait is more accurate in identifying a true hung/idle session.

A unix script can be written to interface with svrmgrl to identify the hung/idle sessions from the v$session_wait and terminate them.

Hope this helps. Regards.

Steven Sim Kok Leong
Brainbench MVP for Unix Admin
http://www.brainbench.com

[Chad Molina]

Yes, it _should_ work for any app that connects to the DB be it a web process, forms client, SQLplus session, etc ...

Chad

[Anthony_4]

pls DO remember halt your MC-SG first before you change any TCP related parameter because MC use TCP connection to detect other nodes alive!!!!!!