Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Oracle client-server connection lost!

Posted on 1999-07-27
Medium Priority
Last Modified: 2008-03-17
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.

Question by:ramalaks
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1087822
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.

Author Comment

ID: 1087823
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.


Expert Comment

ID: 1087824
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    
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 1087825
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.

Expert Comment

ID: 1087826
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

Author Comment

ID: 1087827
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.


Accepted Solution

avico earned 300 total points
ID: 1087828
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).


Author Comment

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


Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question