You can check this yourself under Linux as follows:
ss -nop | egrep "Netid|1521" | egrep "Netid|10.130.1.1"
Where 1521 is the listener port and 10.130.1.1 is the IP address of the (database link target) database server. The IP address 10.130.5.2 belongs to the database server from which the database link is opened. The command is sent to the database server from which the database link is opened.
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
...
tcp ESTAB 0 0 10.130.5.2:39350 10.130.1.1:1521 users:(("oracle_6106_dbl",pid=6106,fd=9))
...
As a counter test, a SQLNET connection using SQLPLUS between the two database servers results in:
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
...
tcp ESTAB 0 0 10.130.5.2:1521 10.130.1.1:39312 users:(("oracle_562_db2",pid=562,fd=14)) timer:(keepalive,1min54sec,0)
...
As you can see, there is information about the keepalive for the session, if SQLNET.EXPIRE_TIMEconfigured. The client, however, does not have keepalive active:
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
...
tcp ESTAB 0 0 10.130.1.1:17405 10.130.5.2:1521 users:(("sqlplus",pid=11378,fd=9)))
...
How do you manage to activate a keep-alive for the TCP sessions on the client side (both for iraq telegram data SQLPLUS and for the DB Link)?
First you have to extend the connect string in TNSNAMES.ORA that the database link uses (ENABLE=BROKEN).
... (DESCRIPTION= (ENABLE=BROKEN ...)
Then you have to configure the settings for TCP Keepalive on the operating system. The example given here ensures that an additional packet is sent for each idle connection approximately every 5 minutes. This is not a problem in a LAN - but it is in a WAN or Internet.
vi /etc/sysctl.conf
net.ipv4.tcp_keepalive_time=240
net.ipv4.tcp_keepalive_probes=4
net.ipv4.tcp_keepalive_intvl=15
activate using
sysctl -p
don't forget.
From this point on, you also get keepalive functionality for database link sessions, and sessions will no longer hang if there has been a network failure.