Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Switch from one database to other using shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Switch from one database to other using shell script
# 1  
Old 07-17-2010
Switch from one database to other using shell script
Hi all,

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

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:
Code:
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.....
Last edited by Franklin52; 07-17-2010 at 07:58 AM.. Reason: Please use code tags
# 2  
Old 07-17-2010
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  
Old 07-17-2010
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 SmilieSmilieSmilie
# 4  
Old 07-17-2010
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.

Code:
$ cat Test.sh
echo $X

$ export X

$ ./Test.sh

$ X=3

$ ./Test.sh
3

Code:
$ 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.
Last edited by Scott; 07-17-2010 at 09:06 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to switch user in shell script?

HI in a server we can't login with root user directly but i can login with different user and then i can switch to root user by su command Requirement is there anyway where i can write a script without mentioning password in file as mentioning the root password is not the... (3 Replies)
Discussion started by: scriptor
3 Replies

2. Shell Programming and Scripting

Shell Script for Java version switch

Hello all I am writing a shell script for unix, and I am trying to get it to ask me if instances are stopped, and then if the version of java running is correct, if not I want it to ask me to switch to the other bit version. I am newish to shell and not clear on how to to input prompts for yes/no... (3 Replies)
Discussion started by: bigbenn
3 Replies

3. UNIX for Dummies Questions & Answers

How to switch the user before executing a shell script from web page??

hi, i want to execute a shell script as a different user. the flow is like this. there is a html web page from which i have to call a shell script. web server is apache. to call the shell script from html page, a perl script is required. so the html page calls the perl script and the perl... (2 Replies)
Discussion started by: Little
2 Replies

4. UNIX for Advanced & Expert Users

Help with server switch shell script

I need a script to change server automatically after performing some operations. The command for changing server is ssh username@servername . Then a prompt comes to enter a password. Then i need to perform some opertaions on the other server. How can i do this in a script? (1 Reply)
Discussion started by: pratikm23
1 Replies

5. Shell Programming and Scripting

How to switch user using shell script ?

Hi, script1.sh script2.sh script3.sh From above, script1.sh is the main script which is executed from root user, creates installation directory, changing ownership and execution rights etc..etc.. and finally calls scripot2.sh and script3.sh to create the database as well as for post... (1 Reply)
Discussion started by: milink
1 Replies

6. Shell Programming and Scripting

Switch user inside shell script

Hi, I am trying to create one script where I have to login as another user inside the script to exeute some commands How can i achieve this? Many thanks in advance. (4 Replies)
Discussion started by: prarat
4 Replies

7. Shell Programming and Scripting

Switch User in within a Shell Script

Hi Experts, I'm trying to write a shell script to stop few things where i have to use another user to execute a command. Otherwise it will not work. Your help is really appreciated Thanks, (16 Replies)
Discussion started by: Afi_Linux
16 Replies

8. Programming

Need help on Shell script for database maintainace..

Hi All, I have to perform some tasks for database maintainace. I am using postgresql on RHEL 4.4. 1) I have to run "vacuumedb" on the database as postgres user. 2) I have to reindex the database as postgres user. Now i want to write shell script to do these tasks one... (3 Replies)
Discussion started by: ashokkumar.p
3 Replies

9. Shell Programming and Scripting

Connection to database through shell script

Hi when i am calling the shell script with two parameter like id and date it works fine.But the same shell script is call by java application it gives the error as shown below Thu Jan 8 04:13:22 EST 2009|The Get Segment Process Failed: SP2-0306: Invalid option. Usage: CONN where <logon>... (1 Reply)
Discussion started by: ravi214u
1 Replies

10. Shell Programming and Scripting

Shell Script: want to insert values in database when update script runs

Hi , I am new to linux and also also to shell scripting. I have one shell script which unpacks .tgz file and install software on machine. When this script runs I want to insert id,filename,description(which will be in readme file),log(which will be in log file) and name of unpacked folder... (1 Reply)
Discussion started by: ring
1 Replies
Login or Register to Ask a Question