• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Oracle Access Remote DB is Local DB is offline

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
weegiraffe
Asked:
weegiraffe
  • 8
  • 8
  • 7
5 Solutions
 
sdstuberCommented:
you won't be able to connect to your local db if it's offline.
0
 
slightwv (䄆 Netminder) Commented:
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
 
weegiraffeAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
weegiraffeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
>>> 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
 
weegiraffeAuthor Commented:
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
 
sdstuberCommented:
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
 
weegiraffeAuthor Commented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
weegiraffeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
weegiraffeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
weegiraffeAuthor Commented:
Will try this.
0
 
weegiraffeAuthor Commented:
These guided me to an eventual solution. When I have perfected it I will post it here.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 8
  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now