Solved

Broken DBLinks in Oracle after network outage or remote DB shutdown

Posted on 2014-09-29
5
553 Views
Last Modified: 2014-10-03
We have two enterprise apps each with their own Oracle 11g database.  App2 has a dblink to App1.  When App1 is taken offline for scheduled maintenance, and then restored - our dblink is broken.  Users run a report and will receive ORA-03114.  The error only occurs once because when the user runs the report again there are no issues.

We are positive the issue is caused when App1 goes offline during a reboot or network outage.

Is there a way to restore the dblink once App1 is brought online after maintenance?
0
Comment
Question by:bmsande
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40350539
running a query with table@dblink should recreate the dblink session

you could do this after app1 starts
0
 

Author Comment

by:bmsande
ID: 40350556
any suggestions on automating this?
0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 40350627
yes... use scheduled job at db start with a delay
and run procedure from app1 across a dblink to app2 ... :)

and vice versa for the other

or if you use a startup script for the database, add a script for such a query on the other database

example on windows: start.cmd
net start OracleServiceAPP1
set oracle_sid=APP1
sqlplus "/ as sysdba" @startup.sql

sqlplus user/pass@APP2 @checkdblink.sql

startup.sql:
 startup open
exit

checkdblink.sql
select * from table@dblink
where 1 = 0;
exit;

Open in new window

0
 

Author Comment

by:bmsande
ID: 40350891
Thanks for the additional info.  Is the behavior we receive with the ORA error expected when the remote db is shutdown or restarted?  Is this common with db links?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40360506
dblinks are not very stable

they sometimes just fail and then you have to find the session on the remote db and kill them to fix the problem
the db will automatically make new dblink session after they were killed
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now