Oracle Client sessions keep disconnecting

connectionsoracleoracle-12ctimeout

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?

Best Answer

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.

Related Solutions

Oracle 10XE: Stop schemas seeing the all_users table

You have a few options, some of which will make your database unsupportable by Oracle (if you care!) as you need to fiddle with SYS owned objects.

The first option is to revoke select privs from everyone:

revoke select on all_users from public;

Then grant it back to certain users, plus all other internal Oracle users.

The 2nd option is to look at the source for the view, drop it, then recreate an altered version that supports your needs.

Another option that would trick casual users would be to create a table called ALL_USERS in each schema. Unless the logged in person explicitly qualified ALL_USERS (ie: SYS.ALL_USERS) they would get the resultset from the table in their own schema. This trick could also be done with a synonym pointing to a view that does:

select * from all_users where username = (SELECT sys_context('USERENV', 'SESSION_USER') from DUAL);

Not much else you can do with XE I'm afraid.

Oracle Installation – Set Oracle Home for Toad 12.6

Here's what worked, although I don't know if it's a good answer or not. I had someone who could log in as an admin on my pc do the Oracle client install. In other words, if you're not a full admin on your machine, it may fail, and not tell you why.

I had admin rights, I could edit the registry and path, but there were still some missing rights. Logging on as administrator and doing the install worked fine.

Best Answer

Related Solutions

Oracle 10XE: Stop schemas seeing the all_users table

Oracle Installation – Set Oracle Home for Toad 12.6

Related Question