Pages

Wednesday, January 14, 2015

Avoiding timeout in SQLDeveloper or JDeveloper connected to remote Oracle Database

I am a remote DBA for my University. Because of that I have configured a VPN Tunnel from my home to the office that works pretty well much of the time. I might say that it works great much of the time. The only complaining I've had in years is when using sqldeveloper to connect to one of the Oracle databases at the school. As long as I kept working, doing queries, inserting, updating, etc, the connection was solid and fast. But if I directed my attention to something else. Like browsing, email reading, or any other activity, after a few minutes, around 10 minutes of idle time, the connection died.  Next query I tried, sqldeveloper greeted me with a connection failure error.

I asked some of the non-remote coworkers if they were having that issue but they did not. So I focus my interest in the Firewall (both end points). I modified the services (port 1521, or application layer oracle) to have much longer timeouts that the default the firewall was using. It did not work.

The solution to this problem is so simple. Just created a sqlnet.ora file on the $TNS_ADMIN directory with the statement:

sqlnet.expire_time = 1

And restart the listener.

Every one minute Oracle  will send a ACK package to my PC, and my PC will answer back with  another ACK.
This is a small sample on how it looks in tcpdump  (I am not using the standard 1521 port for the listener).

19:13:46.036181 IP bannerdb64.server.edu.rds2 > 192.168.40.162.49726: P 4221:4231(10) ack 162 win 295
19:13:46.298970 IP 192.168.40.162.49726 > bannerdb64.server.edu.rds2: . ack 4231 win 16570
19:14:46.049832 IP bannerdb64.server.edu.rds2 > 192.168.40.162.49726: P 4231:4241(10) ack 162 win 295
19:14:46.312593 IP 192.168.40.162.49726 > bannerdb64.server.edu.rds2: . ack 4241 win 16567
19:15:46.063600 IP bannerdb64.server.edu.rds2 > 192.168.40.162.49726: P 4241:4251(10) ack 162 win 295
19:15:46.326957 IP 192.168.40.162.49726 > bannerdb64.server.edu.rds2: . ack 4251 win 16565



Note: I also create an entry on my windows 7 registry for KeepAlive every 5 minutes. But it did not work until I configure the oracle listener as shown previously. Next thing I need to know is remove that entry and see if it still working.

I'll update this post with the result.

No comments:

Post a Comment