RE: Multiple Instance in Unix

From: Goulet, Richard <>
Date: Fri, 7 Aug 2009 12:33:52 -0400
Message-ID: <>


        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 - 11:33:52 CDT

Original text of this message