Essentially, I was offering the script also if he were to ask. Just pointing out a couple things I didn't know if he were considering. P.S. Thanks. Joel Patterson Database Administrator 904 727-2546 -----Original Message----- From: Goulet, Richard [mailto:Richard.Goulet@xxxxxxxxxxx] Sent: Friday, August 07, 2009 2:46 PM To: Patterson, Joel; Mark.Bobak@xxxxxxxxxxxx; gheibia@xxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: Multiple Instance in Unix Whatver floats your boat and works. Dick Goulet Senior Oracle DBA PAREXEL International -----Original Message----- From: Joel.Patterson@xxxxxxxxxxx [mailto:Joel.Patterson@xxxxxxxxxxx] Sent: Friday, August 07, 2009 2:23 PM To: Goulet, Richard; Mark.Bobak@xxxxxxxxxxxx; gheibia@xxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: Multiple Instance in Unix I have a script also that uses oratab but ignores oraenv. It resides in /usr/local/bin (solaris), The main reason is that basically oraenv only sets the SID and HOME, so I just included it in 'oraenvset' which also sets the JAVA_HOME for OMS, (or unsets it for other), sets NLS_LANG (we have different ones), sets or unsets LD_LIBRARY_PATH for differences between oracle9, 10 (solaris again). So one script runs on all servers and can be used to set the environment for listeners (one for every home), agent, oms and databases. Essentially uses a case statement for SID (NLS_LANG, JAVA_HOME), version (LD_LIBRARY_PATH), and ORAENV_ASK. All scripts call it also so only changes are oratab or new version of oracle installed with different stuff. I guess I'm saying is that by treating the LISTENER(s), AGENT, and OMS as ORACLE_SIDs in oratab one can have one stop shopping. So I ignore oraenv (that's just me). Joel Patterson Database Administrator 904 727-2546 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Goulet, Richard Sent: Friday, August 07, 2009 12:34 PM To: Mark.Bobak@xxxxxxxxxxxx; 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 -- //www.freelists.org/webpage/oracle-l