Oracle Client sessions keep disconnecting

Question

We recently moved our Oracle server from a remote location to a local server and now the Oracle clients keep disconnecting.

It does not seem to be a timeout issue because it happens while we are actively using it. We are using Oracle SQL Developer, PL/SQL Developer, and Toad. We are running Oracle 12 Enterprise on a VM.

We do not see any errors - our sessions just drop. Our server is running on a VM on Redhat 7. We are running the clients on Windows. Often when we run a query we have to wait for it to re-initialize before the query is executed. Hence we are assuming that since it has to re-initialize that our session dropped.

When we write a query the client (Toad, PL/SQL Developer, etc) has to reconnect in order to execute the query. I would understand if we stepped away but we are actively writing queries and executing them and then the client disconnects and has to reconnect in order to execute.

How can we solve or debug this issue?

Answer 1

Check if sqlnet_expire_time is set in any of the sqlnet.ora files on the machine that hosts the DB, using some variant of

find / -name sqlnet.ora -exec grep -i sqlnet_expire_time {} \;

If it's not set in any of them then you can quickly rule out dead connection detection. If it is set then you have DCD enabled and it's likely that which is killing your connections.

You are using three apps - my bet is that you're in one of them for more than the DCD time, so the other apps' connections are being killed, then when you switch to another app you find its connection has gone. This is because they each have their own connection, and it doesn't matter how busy connection A is - if connection B is idle for the DCD time it will be killed.

If DCD is ruled out then the next place to check will be the firewall. This needs to be SQL*Net aware; it's not just port 1521 (or whatever you've configured) that needs to be open, but the firewall needs to check the comms for the random port number the server returns to the client and open that as well - if it doesn't do that then the firewall will kill the "unauthorised/unknown" link, thus breaking the connection.

Answer 2

The name "Dead Connection Detection" (DCD) is kind of self explanatory.

DCD does not kill working connections, even if they are idle.

DCD removes sessions from Oracle when they no longer respond.

This is documented in the paper referenced in the answer from jmk, as well as in the oracle documentation.

Answer 3

In addition to Dave's answer:

Speaking of DCD, with 12c the DCD mechanism changed and does not use SQL*Net probes anymore. Our application aware firewalls were not able to detect the connections anymore and terminated connections. Only after I reverted to the old DCD mechanism by setting USE_NS_PROBES_FOR_DCD=truein sqlnet.ora my sessions continued to run.

Fore more details see the Oracle White Paper: Dead Connection Detection