Switch from one database to other using shell script

Shell Programming and Scripting
1

Hi all,

This is my first ever post to any forum so, dont let this go in vain...........:slight_smile:

Here is the scenario........

I have logged into the unix where oracle_sid is initialized for some X database in the .profile.
I have a unix script where some sql query which fetches data from X database.

Now, i need to connect to some other database say Y, I have set the ORACLE_SID to point to Y. But, when i try to connect to that database i get the following error "ORA-12154: TNS:could not resolve service name".

Here is the code that i am using:

ORACLE_SID = �X� ;      export  ORACLE_SID    
ORAENV_ASK="NO"
 
. /opt/bin/oraenv
. /var/opt/oracle/<abc>/$ORACLE_SID/<xyz>
 
<some sql queries............................>       #successfull
 
ORACLE_SID = �Y�;     export  ORACLE_SID   #switching database
ORAENV_ASK="NO"
 
. /opt/bin/oraenv
. /var/opt/oracle/<abc>/$ORACLE_SID/<xyz>      
 
<some sql queries............................>      #Here i am getting connection failed

Any help would be appreciated.....

2

This is more an Oracle related question. The message comes from you TNS Listener that he doesn't know where to find the requested SID. They should be specified in tnsnames.ora AFAIK.

3

I am able to solve it by myself..........

I was using export ORACLE_SID statement at the wrong place...
It should have been after i call . /opt/bin/oraenv

like below....

ORACLE_SID = �Y�;
ORAENV_ASK="NO"

. /opt/bin/oraenv
export ORACLE_SID :):D:b:

4

Hi.

It's great that you fixed it, but it doesn't matter, actually, where you export ORACLE_SID. You can even export it before setting it.

$ cat Test.sh
echo $X

$ export X

$ ./Test.sh

$ X=3

$ ./Test.sh
3

$ export | grep ORACLE_SID

$ export ORACLE_SID
$ export | grep ORACLE_SID
ORACLE_SID

$ echo $ORACLE_SID

$ ORAENV_ASK=NO
$ ORACLE_SID=DB1
$ . oraenv

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Jul 17 11:44:58 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

SQL>

Perhaps you did something else to fix it?

Edit: In fact, just looking at the oraenv script, it exports ORACLE_SID for you.