• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2401
  • Last Modified:

Oracle client-server connection lost!

I am running a applicaiton in client side. It basically
connects to DB one time and exec some sqls in a time
interval.  If for some reason, connection beteen client and
server is lost, will there be any error? how to catch that
error immediately.

when the connection is lost, the client application is
just waiting (hanging..no errors reported).  Will this be
waiting till the tcp/ip time out? can we catch the
connection lost error immediately? I want the client
application to cate the connection lost error and do some
action based on it.

I am using Oracle8i in Unix platform.

1 Solution
Is SQLNET_EXPIRE_TIME parameter set in client's SQLNET.ORA file? It determines the interval for checking connection availability and it is recommended to set it approximately to 10. But read the warning from Oracle Documentatuin, since this parameter 'has some costs associated with it':
In short, you should evaluate carefully whether you would benefit from enabling the dead connection detection feature. It should only be turned on if necessary.
ramalaksAuthor Commented:
I have set SQLNET_EXPIRE_TIME parameter set in client's SQLNET.ORA file to 2. But still I didn't get any notification or erron upon connection lost.  

The software is a failover software and it will have to detect the connection lost immediately from the client side.

Lost connections are tricky. If you have a lost connection the server can't send you an error message. Your application has to detect the disconnection. Your application is talking to the SQL*Net which is talking to sockets which in turn is talking to the TCP/IP layer which then is talking to your ethernet card that actually transmits data. The first really 'smart' layer is the TCP/IP layer. The only time you get an immediate error is if the TCP/IP stack can't talk to the ethernet card. Incase of disconnection, the TCP/IP does not get an error immediately if there is no communication with an other machine because it can still see and talk to ethernet card. The only way for TCP/IP to discover that there is a disconnection is if it wait for the TIMEOUT time.

You can REDUCE the TCP/IP TIMEOUT time at the application level if you have access to the sockets code or if the client allows you to specify it. You should be careful with it because, if you set TIMEOUT too low you will get timeout before you acctually hear from another machine even if there is no disconnection    
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

ramalaksAuthor Commented:
I can not alter the TCP/IP time outs because it is going to affect the entire tcp/ip stack and will be effective on all the messages that go on the network from that particular system which we don't want.
you can't know in real time if the connection is close, you'll know if hte connection is close when you try to send a dml/ddl to Oracle.
The error you'll get is one of the following:
-1034           Oracle is not available
-12203          TNS:unable to connect to destination
-1089           immediate shutdown in progress
-1033           ORACLE initialization or shutdown in progress
-1017           invalid username/password
-1012           not logged on
-3113           end-of-file communication channel
ramalaksAuthor Commented:
Those are the error messages that I was expecting. I am not getting it when I issue any ddl/dml to oracle server.  I am trying to figure out whether my installation/configuration are set properly or not.

I installed Oracle8i client on solaris 2.6 work station.

I get one of the above error when I tried with Oracle8 on a lap-top (PC) to the server.

You can always poll the connection using a some kind of a timer mechanism.
For instance, set a thread in you program to check the tcp/ip connection to the host (using ICMP messages - aka ping or even tnsping) every x seconds.
Despite the network overhead, it seems like the only way to achieve what you're asking for(since you can't get it directly from Oracle).

ramalaksAuthor Commented:
That is what I have decided now and working on it.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now