Broken DBLinks in Oracle after network outage or remote DB shutdown

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?
bmsandeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
running a query with table@dblink should recreate the dblink session

you could do this after app1 starts
0
bmsandeAuthor Commented:
any suggestions on automating this?
0
Geert GOracle dbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bmsandeAuthor Commented:
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
Geert GOracle dbaCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.