In Oracle production database environments, it is desirable to use one Oracle installation for each database which resides on a server. The use of a separate Oracle installation for each database reduces the possibility of corruption of one ORACLE_HOME affecting all of the databases on a single server and allows for maintenance to be performed on one RDBMS without affecting the operations of the others. This is easily accommodated in a LINUX or UNIX environments through the proper setting of your .profile or .bash_profile file. In this article we will review the configuration of your profile to use multiple ORACLE_HOMEs on a single server.

1. To accomplish the setup of our profile for multiple database with separate ORACLE_HOMEs, we will have to create a .profile file with three sections: 1. request for information; 2. local variables definitions; and 3. global variables definitions. The request for information section will appear to the user upon logon or re-initialization of the .profile to determine which database the user wishes to use. The local variable section defines the variables need to connect to a particular database. The global variable section defines the variables which will be the same regardless of our connection to a certain database.

2. The request for information section defines the variable “database” which is used to set the ORACLE_SID, as seen below:

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

3. The local section defines all variables for a specific database, as seen below:

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl
export ORACLE_SID=orcl
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd
elif [[ $database = “2” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl2
export ORACLE_SID=orcl2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl2
cd
elif [[ $database = “3” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl3
export ORACLE_SID=orcl3
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd

## Ending of local variable definitions

4. The global section defines all variables for any database regardless of the specific ORACLE_HOME, as seen below:

## Beginning of global variable definitions

## Ending of global variable definitions

5. Place all profile sections into one file named .profile from UNIX or .bash_profile for linux under your /home/{username} directory. In this example, we will place the following text in the file /home/lcatt/.profile for OS user lcatt.

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

## Ending of global variable definitions

6. Change directory to your home directory with command cd, list current user with command id, and Re-initialize your .profile file as shown below:

$ orcl $ cd
orcl $ id uid=103(lcatt) gid=20(users) groups=200(dba)
orcl $ . ./.profile

7. As seen below the request for information section is displayed and we are presented with the choice of 1, 2, or 3. Select 1 to setup variables for the database ORCL.

Enter database & Oracle version to use:
1 – ORCL 10.2.0.4
2 – ORCL2 10.2.0.4
3 – ORCL3 10.2.0.4
> 1
Your ORACLE_BASE is set to ‘/opt/app/oracle/product’
Your ORACLE_SID is set to ‘orcl’
Your ORACLE_HOME is set to ‘/opt/app/oracle/product/10.2/orcl’
orcl $

This completes setup of .profile for multiple databases using the separate ORACLE_HOMEs.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com