Job not running and db link failure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Job not running and db link failure

  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Job not running and db link failure

    have been provided with a synonym table. Physically the synonym table reside in far away location and is connected via a db link. I am inserting and updating some records in the synonym table frequently by a procedure. I run the procedure using Job scheduled every half an hour.

    Now my problem is , whenever the physical connectivity to the remote location is affected due to physical link down or due to traffic my job get stopped and my job will not be running. Each and every time I need to recreate the job or force unbroken.

    Is there any method to check the db link or any method to handle the exception? Since the oracle wait till timeout . I am using Oracle 9i.
    Any solution is highly appreciated.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I've used block below to test if DBLink connection is available - feel free to improve by writing to a log table.

    Block assumes your DBLink name is "my_dblink".
    Code:
    DECLARE w_table_name VARCHAR2(36);
    BEGIN
       ALTER SESSION CLOSE DATABASE LINK my_dblink;
       SELECT table_name
       INTO   w_table_name
       FROM   all_tables@my_dblink
       WHERE  rownum = 1;
    EXCEPTION
       WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20999, 'my_dblink appears to be down');
    END;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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