How to know if db link is down?

[agasimani]

Hi All

I have couple of dblinks. Sometimes due to some other reason, I loose the dblink. The reason could be anything, like database down, listener down, server down, IP change, port closed etc. Since these dblinks are on prodution, users start calling and complaining about the dblink down. Is there a way to know immediately if any db link goes down, so that I can take pro-active measure?

Any suggestion highly appreciated.

Regards

[slimdave]

Set up a DBMS_JOB or DBMS_SCHEDULER task to query across the link regularly.

Depending on the nature of the problem that takes down the link, you might also have luck with using tnsping to see if the local server can reach the remote listener -- this may be all you need.

[agasimani]

Thanks for the reply.

tnsping is OS command. I know I can use DBMS_JOB or DBMS_SCHDULER. But what happens is if the dblink is down, then it takes forever to throw the error. Is it possible that if the db link is down, then the exception throws the errror immediately?

[davey23uk]

you do what he just said, select from the link regularly

[slimdave]

What error do you most commonly see, and what is the cause?

[agasimani]

Here is the test:

11:38:10 SQL> declare
11:38:19 2 n1 number(1);
11:38:19 3 db_connect number(1);
11:38:19 4 BEGIN
11:38:19 5 db_connect := 1;
11:38:19 6
11:38:19 7 BEGIN
11:38:19 8 select 1 into n1 from dual@health;
11:38:19 9 EXCEPTION
11:38:19 10 WHEN OTHERS THEN db_connect := 0;
11:38:19 11 END;
11:38:19 12
11:38:19 13 if db_connect = 1
11:38:19 14 THEN
11:38:19 15 DBMS_OUTPUT.PUT_LINE('Link available');
11:38:19 16 ELSE
11:38:19 17 DBMS_OUTPUT.PUT_LINE('Link not available');
11:38:19 18 END IF;
11:38:19 19 END;
11:38:19 20 /
db_connect number(1);
*
ERROR at line 3:
ORA-04052: error occurred when looking up remote object DM_USER.DUAL@HEALTH
ORA-00604: error occurred at recursive SQL level 1
ORA-12170: TNS:Connect timeout occurred

11:47:04 SQL>

As you can see, it takes 9 minutes to show this error. It doesnt consider the exception.

Is there a way where as soon as this trigger is fired, if the db link is down for any reason, immediately it executes the exception?

[davey23uk]

change your network timeout settings to be what you want, then it wont take 9 minutes

put an exception clause in which handles the error and does something with it

[agasimani]

Thank you for your reply.

Unfortunately this is not our database. I have no control over this database/server.

[davey23uk]

then you are stuffed