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)
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)
you won't be able to connect to your local db if it's offline.
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.
You can run that select against the remote database from sqlplus or other query tool if your local database is down.
ASKER
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.
I expect that this can be done by checking for a particular error code, but I don't know what the error code is.
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.
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.
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
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
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.
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.
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
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
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.
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.
ASKER
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.
If this is possible, can you please supply an example.
Within PL/SQL anonymous blocks you cannot change connections.
Do you really need a pl/sql block?
What are you really trying to do?
Do you really need a pl/sql block?
What are you really trying to do?
>>> 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.
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.
ASKER
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.
I want to automate this.
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
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
in ksh it might look something like this...
sqlplus yourname/yourpass@firstdb <<!
How can I do it in Windows CMD or batch file?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Will try this.
ASKER
These guided me to an eventual solution. When I have perfected it I will post it here.