?
Solved

Oracle Access Remote DB is Local DB is offline

Posted on 2011-09-13
23
Medium Priority
?
409 Views
Last Modified: 2012-05-12
I want to be able to run a select on a remote oracle database, if  the local database is not available, using PL/SQL.

For example if I run the follwing select and the DB is offline I want rerun a similar select on the remote database, but I cannot figure how to check if the local DB is offline

Select on the local db =
'SELECT * FROM test_id'

Select on the remote db =


'SELECT * FROM test_id@test' (where test is our database link)
0
Comment
Question by:weegiraffe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 7
23 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36531768
you won't be able to connect to your local db if it's offline.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36531788
PL/SQL is Oracle's procedural language.

You can run that select against the remote database from sqlplus or other query tool if your local database is down.
0
 

Author Comment

by:weegiraffe
ID: 36532101
I know I cannot connect to an offline DB. But what I hope I can do is run a PL/SQL script from which will run the remote Select if the local DB is offline SQL PLus.

I expect that this can be done by checking for a particular error code, but I don't know what the error code is.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36532107
pl/sql runs within the database,  

so, if you can't connect to the database, you can't run the pl/sql, so it can't check for local or remote access because it never gets a chance to run.


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36532121
by any chance are you confusing sql*plus scripts with pl/sql?

you could construct a shell script that tried to connect with sql*plus, if it failed then tries to connect to the other  database.

using database links doesn't work because links assume you're already connected to a database.
and, if you can't connect to the local db, then you can't use a link from within that db.
and, if you then connect to the remote db,  you don't want to use a link because your connected directly to it
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36532277
A PL/SQL script is fine from a .sql file:

declare
some_variable char(1);
begin
select 'a' into some_variable from some_table@some_database;
end;
/

That is called an anonymous pl/sql block.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36532736
that pl/sql block will only do something if it runs on the local database.


if you can't connect to local, then @some_database doesn't make any sense
because you'll simply connect to the remote "some_database" on the failover sql*plus attempt
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36533208
I apologize.  That uses a dblink.  I was in a rush.

The pl/sql block will run in whatever database you connect to.

That is a sqlplus issue.  The local DB can be down and you can still run the block from a script file if you connect to the remote database from sqlplus.
0
 

Author Comment

by:weegiraffe
ID: 36537323
Can I run an anonymous block from SQL Plus that will attempt to connect to the local DB and if that connection fails, it will then attempt to connect to the remote DB?

If this is possible, can you please supply an example.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36537367
Within PL/SQL anonymous blocks you cannot change connections.

Do you really need a pl/sql block?

What are you really trying to do?

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36537377
>>> Can I run an anonymous block from SQL Plus that will attempt to connect

of course not.
as mentioned above... pl/sql runs inside a database.  

So, you must be connected PRIOR to trying to run the pl/sql.
0
 

Author Comment

by:weegiraffe
ID: 36537392
OK is there a way of trying to connect to the local db, then if it is down to automatically connect to the remote DB?

I want to automate this.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36537397
you will need to use a script (like ksh or perl or bat or dcl depending on the os and your preference)


invoke sqlplus connecting to primary db,  run your script

if it fails  invoke sqlplus again, connecting to secondary db, run your script.

you can repeat this with as many cascading failures to other db's as you want but in every case,  your session  MUST be connected to a database BEFORE running pl/sql
0
 

Author Comment

by:weegiraffe
ID: 36537442
can you give me an example of how to do that in the form of a bat file, as this will be running on Windows.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 36537447
in ksh it might look something like this...


sqlplus yourname/yourpass@firstdb <<!
your script here
!

SQL_RETURN_CODE=$?

if  [ ${SQL_RETURN_CODE} -ne 0 ]; then
sqlplus yourname/yourpass@seconddb <<!
your script here
!
fi
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 total points
ID: 36537496
Once you connect what will you be doing?

You also realize this can be automatically configured into your tnsnames.ora file where if the first connection times out, use the second.

From:
http://download.oracle.com/docs/cd/B13789_01/network.101/b10776/glossary.htm#i432932

connect-time failover
A client connect request is forwarded to a another listener if a listener is not responding. Connect-time failover is enabled by service registration, because the listener knows if an instance is running to attempting a connection.


0
 

Author Comment

by:weegiraffe
ID: 36548490
in ksh it might look something like this...


sqlplus yourname/yourpass@firstdb <<!
your script here
!

SQL_RETURN_CODE=$?

if  [ ${SQL_RETURN_CODE} -ne 0 ]; then
sqlplus yourname/yourpass@seconddb <<!
your script here
!
fi

How can I do it in Windows CMD or batch file?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 total points
ID: 36549884
>>How can I do it in Windows CMD or batch file?

Did you investigate the TNS timeout/fail over piece?  That is a much more elegant solution to this problem.

Since you seem set on the CMD file, same basic method is sqlplus properly sets the ERRORLEVEL.

http://www.robvanderwoude.com/errorlevel.php

There are a ton of links on BAT and CMD programming.  This one will be a simple if then else.
0
 

Author Comment

by:weegiraffe
ID: 36550031
slightwv,

With respect,  just pointing me to other websites is not very helpful.

The link to the glossary on oracle terms does not tell me how to configure the tnsnames.ora.

The link to the site on DOS does not help me much either.  
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 total points
ID: 36550289
This site really isn't set up to write complete code for people.

If you have a general question about 'how' to do something please ask.

I provided the links as a reference to get you the terminology necessary for you to look through the docs.

The Oracle term "connect-time failover" leads to:
http://download.oracle.com/docs/cd/B14117_01/network.101/b10775/advcfg.htm#sthref1202

Configuring Address List Parameters
FAILOVER (Connect-Time Failover)
 
At connect time, instructs Oracle Net to fail over to a different listener if the first listener fails when set to on. The number of addresses in the list determines how many addresses are tried. When set to off, instructs Oracle Net to try one address.

>>The link to the site on DOS does not help me much either.  

It shows how to look at the return code of a command and make a decision based on that.

There are many different ways to do what you are asking, I do not know your exact requirements and what will and will not work in your situation.

I was attempting to provide the basic information/tools you will need and let you implement to proper solution for you.

If I have time later I can see if I can work on a sample CMD script you can run that shows the basics.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 36550956
I'm more of a ksh guy than windows, but maybe something like this...

@echo off
(
echo prompt "Your local script here"
echo exit
) | sqlplus -s -l testuser/pa55w0rd@localdb

if ERRORLEVEL 1 goto db1_failed
exit

:db1_failed
(
echo prompt "Your remote script here"
echo exit
) | sqlplus -s -l testuser/pa55w0rd@remotedb

Open in new window

0
 

Author Comment

by:weegiraffe
ID: 36557184
Will try this.
0
 

Author Closing Comment

by:weegiraffe
ID: 36568335
These guided me to an eventual solution. When I have perfected it I will post it here.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

762 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