Hi Richard, I'm not sure I see how that script helps. If you want short aliases to switch between, say db1 and db2, why not just do: alias db1='export ORAENV_ASK=NO;export ORACLE_SID=db1;. Oraenv' alias db2='export ORAENV_ASK=NO;export ORACLE_SID=db2;. oraenv' Wouldn't that achieve the same, without having to deal w/ the complexity of the chgsid.sh shell script? -Mark -----Original Message----- From: Goulet, Richard [mailto:Richard.Goulet@xxxxxxxxxxx] Sent: Friday, August 07, 2009 12:34 PM To: Bobak, Mark; Amir Gheibi Cc: ORACLE-L Subject: RE: Multiple Instance in Unix Mark, I run lots of database instances on the same box and have bumped into the same problems, but have a solution. First put the following script somewhere where you can access it when you log in (your sysadmin may have a good idea): Start script #!/usr/local/bin/bash export OLD_SID="$ORACLE_SID" if [ "$1" != "" ] ; then export ORACLE_SID="$1" if [ `cat /etc/oratab | grep "$ORACLE_SID" | wc -l` == "1" ] ; then echo " Found database "$ORACLE_SID" in oratab" . oraenv else printf " Oracle SID %s does not exist in /etc/oratab cannot determine Oracle_Home\n" "$ORACLE_SID" export ORACLE_SID="$OLD_SID" #exit; fi; else sd=( $(cat "/etc/oratab")) for element in $(seq 1 $((${#sd[@]} - 1))) do db=`echo "${sd[$element]}" | awk -F: '{print $1}' -` printf " %2.0d %s\n" "$element" "$db" done printf "\n Select database (1-%d): " "$element" read ans; if [ `echo "${sd[$ans]}" | awk -F: '{print $1}' -` != "#" ] ; then ORACLE_SID=`echo "${sd[$ans]}" | awk -F: '{print $1}' -` export ORACLE_SID if [ `cat /etc/oratab | grep "$ORACLE_SID" | wc -l` == "1" ] ; then echo "Found "$ORACLE_SID" in oratab" export ORAENV_ASK=NO . oraenv else export ORACLE_SID="$OLD_SID" echo "Oracle SID "$ORACLE_SID" is undefined" #exit; fi fi Fi End script I save it under the name chgsid.sh. Make sure the permissions are 755. Second add alias commands to you login script as follows: alias db1='. /home/users/oracle/src/chgsid.sh db1' The rest takes care of itself. Dick Goulet Senior Oracle DBA PAREXEL International -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark Sent: Friday, August 07, 2009 7:57 AM To: Amir Gheibi Cc: ORACLE-L Subject: RE: Multiple Instance in Unix Yes, exactly. But also, keep in mind, if, for example, you have an /etc/oratab that looks like this: db1:/oracle/product/10.2.0:Y db2:/oracle/product/10.2.0:Y Then, you can do: . oraenv and enter 'db1' or 'db2' to switch ORACLE_HOME and ORACLE_SID to appropriate values for the instance you want to work on. Then, if you do: sqlplus / as sysdba You'll be connected as SYS to whatever instance is running w/ that ORACLE_HOME and ORACLE_SID. But, keep in mind, once both instances are up and running, if you just want to connect to one or the other as a non-SYS user, you don't need to keep running '. oraenv'. You can just do 'sqlplus yourusername@db1' or 'sqlplus yourusername@db2' to get to whichever instance you need. (Assuming of course a proper listener.ora and tnsnames.ora setup.) Hope that helps, -Mark ________________________________________ From: Amir Gheibi [gheibia@xxxxxxxxx] Sent: Friday, August 07, 2009 4:44 To: Bobak, Mark Cc: ORACLE-L Subject: Re: Multiple Instance in Unix Hi Mark, Thanks for the swift reply. Appreciate it. But if re-set ORACLE_SID to the new SID and keep the ORACLE_HOME, how would I access to the first instance? Change ORACLE_SID back to the old SID whenever is needed? ~ Amir On Fri, Aug 7, 2009 at 4:25 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>> wrote: Hi Amir, This is a common practice. There is no need to create a new OS user. You can even create a new database under the same Oracle home. The thing to keep in mind is that the combination of ORACLE_SID and ORACLE_HOME need to be unique. So, that will uniquely identify an instance on a host, and then that instance's pfile/spfile will point to your database's control file(s). In that way, your new instance will clearly identify which database it will mount and open. Hope that helps, -Mark ________________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> [oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On Behalf Of Amir Gheibi [gheibia@xxxxxxxxx<mailto:gheibia@xxxxxxxxx>] Sent: Friday, August 07, 2009 4:18 To: ORACLE-L Subject: Multiple Instance in Unix Hi listers, I have a HP Unix box that has Oracle 10g R2 installed on it. There is already an instance running and a database attached to it. I need to create another instance and database. There is no X Server installed on the OS. I created the first database through command line. My question is whether I have to create another OS user in order to create the new instance? Or I can use the same OS user (oracle:dba) to create the new instance? As I need to set environment variables then if I use the same OS user, what would happen to the first instance? Is there a guideline on how to do this? Thanks. ~ Amir Gheibi -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l