How to know if db link is down?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to know if db link is down?

  1. #1
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304

    How to know if db link is down?

    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
    Agasimani
    OCP(10g/9i/8i/8)

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    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?
    Agasimani
    OCP(10g/9i/8i/8)

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you do what he just said, select from the link regularly

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What error do you most commonly see, and what is the cause?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    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?
    Agasimani
    OCP(10g/9i/8i/8)

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  8. #8
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Thank you for your reply.

    Unfortunately this is not our database. I have no control over this database/server.
    Agasimani
    OCP(10g/9i/8i/8)

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    then you are stuffed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width