Simulating KeepAlive in Oracle with SQLNET.EXPIRE_TIME: A Solution for Firewall-Enforced Environments and Idle Sessions

One of the common issues in environments where a firewall or NAT exists between the client and the database is the termination of idle sessions due to timeout.

To solve this problem, Oracle introduced the SQLNET.EXPIRE_TIME parameter. This parameter causes the database to periodically send small packets (probes) to keep the communication path alive. This mechanism is essentially a simulation of TCP KeepAlive to prevent firewalls from dropping the connection.

Test Environment

• Database Server (VMware Workstation)

  • Address: 192.168.188.3

  • Database: Oracle 19c (Instance: orcl)

• Windows Client

  • Address: 192.168.188.1

  • Connection Tool: SQL Developer (with SYS user)

• Network: Direct connection on port 1521

Configuring the SQLNET.EXPIRE_TIME Parameter

The parameter was set in the sqlnet.ora file under the DB Home (not Grid Home):

# $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME = 1

• The value 1 minute was chosen solely for simulation in the test environment (in production, it is usually set to 10 or 15 minutes).

• After modifying the file, the following command was executed on the server:

lsnrctl reload

to make the Listener reload its configuration.

• Note that this parameter only applies to new sessions; existing sessions prior to the change will not use this mechanism unless reconnected.

Monitoring with tcpdump

To observe the exchange of probe and ACK packets between the server and the client, the following command was executed as the root user:

tcpdump -i ens160 host 192.168.188.1 and port 1521 -nn -vvv -X -s0

Sample Output

Probe and ACK Response

(This is a shortened version; the actual captured output is larger.)

05:32:41.416996 IP 192.168.188.3.1521 > 192.168.188.1.51965: Flags [.], length 0
05:32:41.417692 IP 192.168.188.1.51965 > 192.168.188.3.1521: Flags [.], length 0

• First line: the server sent a probe.

• Second line: the client replied with an ACK.

• Result: the connection is alive.

Repetition in Subsequent Intervals

05:33:42.857052 IP 192.168.188.3.1521 > 192.168.188.1.51965: Flags [.], length 0
05:33:42.858105 IP 192.168.188.1.51965 > 192.168.188.3.1521: Flags [.], length 0

This cycle repeats every one minute, ensuring that the database session remains stable.

Checking Sessions in the Database

The client session was verified with the following query (executed from SQL Developer as SYS):

select s.inst_id,
       s.status,
       s.sid||','||s.serial# sid_serial,
       s.program,
       s.osuser,
       s.username,
       s.machine,
       s.logon_time
from gv$session s
where program like 'SQL%';

Sample Output

Conclusion

• The SQLNET.EXPIRE_TIME parameter makes the server send simulated KeepAlive packets to prevent firewalls from dropping idle sessions.

• In this test, the value was set to 1 minute to clearly observe the behavior in tcpdump.

• The Listener was reloaded with the reload command to apply the changes.

• The mechanism only affects new sessions.

• Regular ACK responses in tcpdump confirm that the connection is alive.

• If the client disconnects, instead of ACK you would see an RST or ICMP unreachable, and the session will be terminated.