Home / server / Questions / 39946
In Process
nik
Asked: 2009-07-14 07:53:58 +0800 CST

Configuring keepalives with TOAD client communication

  • 772

I have a scenario where long-idle sessions of TOADSoft client communication with the Oracle server seem to be torn down as they timeout on an intermediate state-full firewall.

These sessions could be idle for multiple hours!
It is not acceptable to increase the timeout on the firewall globally and
of course, it does not support longer timeouts for specific flows.

I have fixed this problem for PuTTY idle connections;
it allows a configurable keep-alive.

But, I have not yet identified any such support in the TOAD software.

Looking up a bit on system-wide configurations, I came up with this Microsoft TechNet detail on Configuring system wide Keep-Alives that involves working with the KeepAliveTime and related registry variables.

HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveTime

This seems like the Linux system wide configuration for using system wide keep-alive.
But, that requires programs to use the setsockopt() and request keep-alive (right?).

Remember that keepalive support, even if configured in the kernel, is not the default behavior in Linux. Programs must request keepalive control for their sockets using the setsockopt interface. There are relatively few programs implementing keepalive...

Questions

  1. Can I use this KeepAliveTime technique on the windows client machines or maybe the server?
    (has anyone tried it?)
    • Is there some other way to keep these TOAD connections active (from either end of the communication)?
firewall tcp database-administration keepalive database-connection

3 Answers

  1. Set the value of SQLNET.EXPIRE_TIME in sqlnet.ora.

    Here is what the Oracle manual says on this subject:

    Purpose

    Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

    Limitations on using this terminated connection detection feature are:

    • It is not allowed on bequeathed connections.
    • Though very small, a probe packet generates additional traffic that may downgrade network performance.
    • Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

    Default: 0

    Minimum Value: 0

    Recommended Value: 10

    Example

    SQLNET.EXPIRE_TIME=10

    • 1

  2. Do you have SSH access? You could set up an SSH tunnel & send the SQL communication over that.

    TOAD: server(or tnsnames) becomes localhost, listening port is some random port such as 12346
    PuTTY: port forwarding. source port: 12346, destination: $REMOTE_IP:1521. Leave it as a Local port.

    Make sure you enable TCP keepalives in PuTTY via the Connection section.

    • 1

  3. I found this on Dell's site, and confirmed it works (under Toad 11.5.0.56): Does Toad have a keep alive / interval time / ping setting so that the network will not timeout? (49507)

    To summarize - Turn Output On in the DBMS Output window by clicking the red button icon at the left of the DBMS Output menu bar (it will turn green). You can access this in the DBMS Output tab beside the Data Grid tab, or through View -> DBMS Output.

    This will automatically also turn on polling. In my experience, you need both DBMS Output and Polling enabled to maintain the connection.

    • 1