Broken database link

Can anybody on the board please let me know how to identify a broken database link??? Let me explain the scenario...We have two databases, X1 and X2. During certain point in our processing we push some data from X2 to X1. For this we are using a database link. After making the connection, for some (network) reason, the link breaks and the application hangs without throwing any error. Especially if this happens in the evening when everyone has left the office, this problem is not noticed till the next morning. I shall be glad if anyone can tell me if there is any way to identify this situation so that the problem can be immediately identified.

Thanks in advance.

Try pushing data manually from X2 to X1 and See what error message you get ??

Try spool the log when it push the data from X2 to X1 and see the log why it broken.

Hi psrchaks

first, what version of db, and what os.
second, x1 and x2 are diff or copy of x1 into x2?
third, check bacup times in x2 (where you get the data from) and look at when x2 and x1 where down.
and last but not least, look at your alert log. if you have nothing there it is strange, but i'll go for network problem. look at your network error log.

Hi

in all_objects you can see the status of the db_link.

All, thanks for your prompt responses...I shall be glad if anyone can tell me if there is a way to trap this problem from an application....I agree with you all when you say that the information can be obtained from network log....but if there is a way to trap the same via an application, then that will answer my question....Clarifying certain things, X1 and X2 are two completely different databases (not the same copy) and both reside on two different servers....

Awaiting your response.

Chakri

I always do a 'select * from dual@remotesite' to verify if the db_link is still OK.

Maybe you can put this command in a PL/SQL block, and use exceptions to verify if the db_link is OK.


eg.
declare
---dumvar dual.X%TYPE;
begin
---select X
-----into dumvar
---from dual@remotesite ;
---continue with work
exceptions
---when others then do error handling;
end;

Hope his helps
Gert

All, once again thanks for your help....

Chakri

I had the same experience. DB link always borken after working hour and back to normal on 2nd day.
Since I can not recreate the same error during the day time. Here is my assumption, in UNIX environment, if the network borken, it will try to reconnect after some time out peroid. once the network back to normal, the DB link work again.

make sense? but why it always happen in nite time?

Hi jm,
I agree with you reg. reconnecting after the timeout....However, what happens to the application that is using this broken link? (when it first made the connection, the link was fine...) Is there any way that my application can trap this broken link....Let me know if anyone any clue.....

Thanks in advance
Chakri

Chakri,

I don't know what's your application. My site is snapshot replication. The 3 places I can see the errors: trc file, alert log and "broken" job. so what I did:
schedule a cron job kick out every 10 min. check the job status, if it is ok do nothing, else run the job (dbms_job.run) to reset the status. (under the assumption: job is good, network will reconnect even it broken temporarily).

jm

yep....probably i may also have to do the same thing....n'way thanks a lot for your feedback on this issue....

Cheers
Chakri

is your application sitting on x1 or x2 server or on a 3rd server ? This may be a long shot but is it possible that the connection is lost between the client and db server because of an extended time of inactivity. In that case writing a small loop application that just pings the server every few minutes (to make the connection live) may help.

- Rajeev