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:
- Edited the sqlnet.ora file as follows (both client and server):
SQLNET.EXPIRE_TIME = 1000000
SQLNET.INBOUND_CONNECT_TIMEOUT = 1000000
- 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:
- Restore "sqlnet.ora" back to normal (both client and server)
- Configure (ENABLE=BROKEN) on the "tnsnames.ora" file (just on the client)
- 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
- Reboot client