Broken database link
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Broken database link

  1. #1
    Join Date
    Jun 2001
    Posts
    12
    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.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  3. #3
    Join Date
    Jul 2000
    Posts
    243
    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.


  4. #4
    Join Date
    Jul 2000
    Posts
    243
    Hi

    in all_objects you can see the status of the db_link.

  5. #5
    Join Date
    Jun 2001
    Posts
    12

    Broken database 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

  6. #6
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

  7. #7
    Join Date
    Jun 2001
    Posts
    12

    Broken database link

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

    Chakri

  8. #8
    Join Date
    Nov 2000
    Posts
    245

    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?

  9. #9
    Join Date
    Jun 2001
    Posts
    12
    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

  10. #10
    Join Date
    Nov 2000
    Posts
    245

    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

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