RE: Multiple Instance in Unix

From: Goulet, Richard <>
Date: Fri, 7 Aug 2009 14:45:45 -0400
Message-ID: <>

Whatver floats your boat and works.

Dick Goulet
Senior Oracle DBA
PAREXEL International

-----Original Message-----
From: [] Sent: Friday, August 07, 2009 2:23 PM
To: Goulet, Richard;; Cc:
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-----
[] On Behalf Of Goulet, Richard
Sent: Friday, August 07, 2009 12:34 PM
To:; Amir Gheibi Cc: ORACLE-L
Subject: RE: Multiple Instance in Unix


        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
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
      printf "        Oracle SID %s does not exist in /etc/oratab cannot
determine Oracle_Home\n" "$ORACLE_SID"
      export ORACLE_SID="$OLD_SID"


   sd=( $(cat "/etc/oratab"))
   for element in $(seq 1 $((${#sd[_at_]} - 1)))    do

      db=`echo "${sd[$element]}" | awk -F: '{print $1}' -`
      printf "      %2.0d %s\n" "$element" "$db"
   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" ] ;
         echo "Found "$ORACLE_SID" in oratab"
         export ORAENV_ASK=NO
         . oraenv
         export ORACLE_SID="$OLD_SID"
         echo "Oracle SID "$ORACLE_SID" is undefined"

End script

I save it under the name Make sure the permissions are 755.

        Second add alias commands to you login script as follows:

alias db1='. /home/users/oracle/src/ db1'

The rest takes care of itself.

Dick Goulet
Senior Oracle DBA
PAREXEL International

-----Original Message-----
[] On Behalf Of Bobak, Mark
Sent: Friday, August 07, 2009 7:57 AM
To: Amir Gheibi
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:

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_at_db1' or 'sqlplus yourusername_at_db2' to get to whichever instance you need. (Assuming of course a proper listener.ora and tnsnames.ora setup.)

Hope that helps,


From: Amir Gheibi [] Sent: Friday, August 07, 2009 4:44
To: Bobak, Mark
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 <<>> 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,


[<>] On
Behalf Of Amir Gheibi [<>] Sent: Friday, August 07, 2009 4:18
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?


~ Amir Gheibi



Received on Fri Aug 07 2009 - 13:45:45 CDT

Original text of this message