asked on

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.

thanks
ramalaks@peri.com

dda

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.
---

ramalaks

ASKER

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.

mshaikh

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

ramalaks

ASKER

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.

urim

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

ramalaks

ASKER

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 8.1.5.0.0 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.

ASKER CERTIFIED SOLUTION

avico

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ramalaks

ASKER

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

thanks
ramalaks@peri.com