In test and development database environments, it is desirable to use one Oracle installation for multiple databases. The use of a single Oracle installation for multiple databases reduces the amount of space being used and creates a single place for update of binary files. This is easily accommodated in a LINUX or UNIX environment through the proper setting of your .profile or .bash_profile file. In this article we will review the configuration of your profile file to use a single Oracle home with multiple databases.
1. To accomplish the setup of our profile for multiple databases on a single ORACLE_HOME, we will have to create a .profile 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 and determines 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 variable, 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 variable section defines all variables for a specific database, as seen below.
##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export ORACLE_SID=orcl
elif [[ $database = “2” ]]
then
export ORACLE_SID=orcl2
elif [[ $database = “3” ]]
then
export ORACLE_SID=orcl3
else
echo “Invalid options ”
fi
## Ending of local variable definitions
4. The global variable section defines all variables for any database using this specific ORACLE_HOME, as seen below.
## Beginning of global variable definitions
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2
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
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220
cd
echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”
umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin
## 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, I will place the following text in the file /home/lcatt/.profile for the 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
##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export ORACLE_SID=orcl
elif [[ $database = “2” ]]
then
export ORACLE_SID=orcl2
elif [[ $database = “3” ]]
then
export ORACLE_SID=orcl3
else
echo “Invalid options ”
fi
## Ending of local variable definitions
## Beginning of global variable definitions
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2
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
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220
cd
echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”
umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin
## 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. Upon re-initialization of the profile, you will be able to select the database environment you wish, as seen below. In this example we have select 1 to setup the 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 $
This completes setup of .profile for multiple databases using the same oracle home.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com