Technical Tip: Switching Oracle instances
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.
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.
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
Filed under: Technical Tips
Like this post? Subscribe to my RSS feed and get loads more!