If you have multiple instances on a single machine, you need to have a way to switch from one instance to another. I have seen many systems set up where this is done manually, rather than through the scripts provided by Oracle. I would strongly suggest that you use Oracle’s scripts. Most manual processes that I have seen assume that there is only one version of oracle, and maybe even only one instance on a machine. In those cases, adding a new version or a new instance can cause problems.

VMS

You switch to a new Oracle instance by running the orauser.com script that was built for that instance when it was created. Although this script takes care of everything for you, you first have to know where it is. If you had Oracle 7.3 installed under the DISK01:[ORACLE73] directory, the orauser script for the ABC instance would be: DISK01:[ORACLE73.DB_ABC]ORAUSER_ABC.COM.

Users would point to the ABC instance by running that script.

$ @ DISK01:[ORACLE73.DB_ABC]ORAUSER_ABC.COM

The script defines logical names to point to the various Oracle directories, and defines symbols so that you can run the Oracle programs, such as sqlplus.

If you want to point to another instance, you would need to locate and run the orauser.com script for that instance.

UNIX

Oracle on UNIX provides a single script that allows you to switch from one instance to another. Actually, there are 2 scripts; one for csh users and one for ksh users. These scripts look in the oratab file to find the correct value for $ORACLE_HOME for the instance, then set environment variables to correctly configure your environment.

Although the scripts are the same on different flavors of UNIX, their location is not. For example, the scripts are in /opt/bin under Solaris and in /usr/local/bin under HP-UX.

Because these scripts set environment variables, they cannot be executed as normal scripts, since an executed script spawns a subshell, and any environment variables set in the subshell do not get passed back to its parent shell. So, these scripts must be run underneath the current shell. Here are csh and ksh examples of how this script must be executed.

csh: % source /opt/bin/coranev

ksh: $ . /opt/bin/oraenv

You will be prompted for the value of ORACLE_SID. The script will find the instance that you specify in the oratab file and:

Find and set the value for $ORACLE_HOME

Remove any old $ORACLE_HOME/bin entries from your path

Add the new $ORACLE_HOME/bin to your path so that you will be able to run appropriate version of sqlplus, exp, imp, svrmgrl, etc.

Set ORACLE_SID to the value that you entered.

If you are running a script where it is inconvenient or impossible to be prompted for the value of ORACLE_SID, you can set ORACLE_SID beforehand. You must also set ORAENV_ASK to NO so that the oraenv or coraenv script will use the current value of ORACLE_SID. Here is a c-shell example.

setenv ORACLE_SID abc

setenv ORAENV_ASK NO

source /opt/bin/coraenv

Filed under: Technical Tips

Like this post? Subscribe to my RSS feed and get loads more!