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



--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: