Solved

Oracle Access Remote DB is Local DB is offline

Posted on 2011-09-13
23
385 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
  • 8
  • 8
  • 7
23 Comments
 
LVL 73

Expert Comment

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

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
 
LVL 73

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 73

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 76

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 73

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 76

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 76

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 73

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
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.

 

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 73

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 73

Accepted Solution

by:
sdstuber earned 200 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 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 73

Assisted Solution

by:sdstuber
sdstuber earned 200 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

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.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now