4

We are running an Oracle 19c database on Azure Windows VM. Our config is as follows:

  • Azure Windows Virtual Machine
    • Standard B4ms (4 vcpus, 16 GiB memory)
    • Public IP address with an "idle timeout" of 30 minutes (maximum allowed).
    • Oracle Database 19c

When running from the VM no "idle SQL sessions" are disconnected, but when accessing from the public IP address, the "idle SQL connections" are disconnected at 30 minutes.

ORA-03135: connection lost contact

We want our "Idle SQL connections" to be persistent, this is what we have tried so far:

  1. Edited the sqlnet.ora file as follows (both client and server):
SQLNET.EXPIRE_TIME = 1000000
SQLNET.INBOUND_CONNECT_TIMEOUT = 1000000
  1. Edited the tnsnames.ora file as follows (both client and server):
(ENABLE=BROKEN)

Currently I'm trying to implement Oracle keep-alive packages, but I haven't had success.

Any directions or ideas on this? Thanks

[UPDATE] I found a solution, this is what I did in order to fix it:

  1. Restore "sqlnet.ora" back to normal (both client and server)
  2. Configure (ENABLE=BROKEN) on the "tnsnames.ora" file (just on the client)
  3. Setting TCP KeepAlive (only on the client) on the Regedit with the following parameters (https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-to-an-azure-sql-database?view=sql-server-ver15)
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"KeepAliveTime"=dword:00007530
"KeepAliveInterval"=dword:000003e8
"TcpMaxDataRetransmissions"=dword:00000010
  1. Reboot client

1 Answer 1

1

You need to change one or two kernel tunables - on client machine. To make TCP keepalive (ENABLE=BROKEN) useful. By default Linux kernel sends the 1st TCP keepalive probe after 2 hours:

Linux kernel:

# cat /proc/sys/net/ipv4/tcp_keepalive_time
7200
Delay before the 1st probe is sent - after 2 hours !!!

# cat /proc/sys/net/ipv4/tcp_keepalive_intvl
75
Interval between probes.

# cat /proc/sys/net/ipv4/tcp_keepalive_probes
9
The number of "lost" probes indicating that the connection is dead.

When set keepavlive time and keepalive interval to 5 sec (for test only. 30 sec should be more than enough for prod), then you should see something like this in TCP dump:

[~]# tcpdump -pni ens224 -v "tcp port 1521 and ( tcp[tcpflags] & tcp-ack != 0 and ( (ip[2:2] - ((ip[0]&0xf)<<2) ) - ((tcp[12]&0xf0)>>2) ) == 0 )"
tcpdump: listening on ens224, link-type EN10MB (Ethernet), capture size 262144 bytes
14:56:49.994675 IP (tos 0x0, ttl 64, id 12096, offset 0, flags [DF], proto TCP (6), length 52)
    192.168.8.200.37196 > 192.168.8.212.ncube-lm: Flags [.], cksum 0x9313 (incorrect -> 0x4b94), ack 2263270845, win 359, options [nop,nop,TS val 78140944 ecr 7133273], length 0
14:56:49.994829 IP (tos 0x0, ttl 64, id 55327, offset 0, flags [DF], proto TCP (6), length 52)
    192.168.8.212.ncube-lm > 192.168.8.200.37196: Flags [.], cksum 0xadcf (correct), ack 1, win 207, options [nop,nop,TS val 7138281 ecr 78110940], length 0
14:56:55.002668 IP (tos 0x0, ttl 64, id 12097, offset 0, flags [DF], proto TCP (6), length 52)
    192.168.8.200.37196 > 192.168.8.212.ncube-lm: Flags [.], cksum 0x9313 (incorrect -> 0x2474), ack 1, win 359, options [nop,nop,TS val 78145952 ecr 7138281], length 0
14:56:55.002809 IP (tos 0x0, ttl 64, id 55328, offset 0, flags [DF], proto TCP (6), length 52)
    192.168.8.212.ncube-lm > 192.168.8.200.37196: Flags [.], cksum 0x9a3f (correct), ack 1, win 207, options [nop,nop,TS val 7143289 ecr 78110940], length 0
14:57:00.010680 IP (tos 0x0, ttl 64, id 12098, offset 0, flags [DF], proto TCP (6), length 52)
    192.168.8.200.37196 > 192.168.8.212.ncube-lm: Flags [.], cksum 0x9313 (incorrect -> 0xfd53), ack 1, win 359, options [nop,nop,TS val 78150960 ecr 7143289], length 0
14:57:00.011296 IP (tos 0x0, ttl 64, id 55329, offset 0, flags [DF], proto TCP (6), length 52)
    192.168.8.212.ncube-lm > 192.168.8.200.37196: Flags [.], cksum 0x86af (correct), ack 1, win 207, options [nop,nop,TS val 7148297 ecr 78110940], length 0

6 packets captured
6 packets received by filter
0 packets dropped by kernel
3
  • Thanks for your answer, is there a configuration like that on Windows? All the clients that connect to the database are have Windows. Thanks.
    – Xim
    Sep 14, 2020 at 19:55
  • Basically Windows have the same config values(in registry). docs.microsoft.com/en-us/sql/connect/jdbc/… docs.microsoft.com/en-us/previous-versions/windows/it-pro/… NOTE: changing there values globally is safe, TCP keepalives are NOT used unless the application explicitly request it. So other clients TCP connections will NOT be affected.
    – ibre5041
    Sep 14, 2020 at 20:33
  • Thanks to you I found the solution.
    – Xim
    Sep 15, 2020 at 12:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .