odbc - oracle connection & wan problems

[bob hollis]

I have a user that is running an application that uses odbc to connect to an oracle database. The problem is that their wan has a tendency to go down from time to time. Right now they are forced to restart the application once the network comes up. Is there a way to "save" the connection and simply "reconnect" to the session again? If this is possible, what changes would need to be made to the database or sql*net or odbc or application to get it to work? There also would be security issues with this approach. The other thing that might be possible is to let session on the oracle side as it does now, and have the app keep retrying to connect again until the network comes back up. Any idea how to do that?
Thanks!

[Steven E. Protter]

Probably not. ODBC connects to the Oracle database through a socket and a port. If that communications path is interupted, that messes things up.

The only thing I can think of is making sure you are on the latest supported version of the ODBC software that Oracle supports.

SEP

[Tim Sanko]

Sure there is. It just takes a little socket programming.

If the program that it is written in is C or C++ I might have something like that in my bag of tricks. I could send you a keepalive "pinger" from the application server that could be modified to bring itself up via at in say 5 minutes.

If the application server has local as well as remote connections, this has to, and can be done with semaphores, and message queues. It can be done, but I don't want to be hte one to do it, unless I am getting contractor wages...

Tim

[Tim Sanko]

By the way , been there done that on UNIX, NT, OS/2 and an AS400...

I still shudder at the thought of C on an AS400....

Cringing.....

Tim

[A. Clay Stephenson]

While more robust coding is in order, I would think that loss of a database connection is only a minor inconvenience and you are addressing but one aspect of a bigger problem. Even if you get this problem "fixed" there are bound to be other applications that suffer when the WAN goes down. The "real" answer is to assume that the primary LAN is going to fail; at that point, a backup line (typically ISDN) needs to "spin up" and remain up until the primary connection is returned to service. This is handled by the routers and is invisible to the hosts and applications. The backup connection only has to have enough bandwidth to allow "limp along" service so it can be a much cheaper connection (even dialup POTS) than the primary.

[bob hollis]

Just to clarify a few points.
The oracle server is hpux 11.0 64 bit
The database is 8.1.7
The app is written in Delphi
This particular network connection is between Scotland and the US. I think the trouble is currently in the Scot piece. Our main network is quite robust. This is a new app and they may want to put in redundancy at some point - thanks for the suggestion.

Anything you want to post here in the way of coding examples would be very welcome!

Unfortunately nobody is getting contractor wages here 8-)