When you are running Real Application Clusters, the distinction between the instance and the database becomes very clear. You now have an instance running on each server in the cluster that accesses a common database on shared storage. If you run oraenv with a newly installed RAC database, you will notice that you still have to make adjustments to environment variables to access your instance. We are going to continue the theme from my last post (using Oracle’s oraenv), and take a look at using Oracle oraenv with RAC.

As we discussed before, running oraenv to choose which database to access sets the ORACLE_SID to the database name. But when you run RAC, your ORACLE_SID is usually your database name plus the instance number. Unfortunately, oraenv does not automatically add the instance number, so we can add a little code to our custom oraenv script to find out which instance is running on the node and have the number added to the ORACLE_SID.

I will step through and explain the sections of the script and I will make the entire script available as a download at the end.

Like many other companies, we make customizations to our Oracle environment to fit our needs. In our oraenv, we create a new OS variable called ORACLE_DB that matches the database name and it is used throughout our custom database scripts.

# ===================================================================================
# Set the ORACLE_DB so that the scripts will run for RAC and non-RAC
# ===================================================================================
export ORACLE_DB=$ORACLE_SID

We run our databases on Linux and HP-UX, so most of our scripts have to be adaptable for the OS differences. This section determines the OS and checks the first 4 characters of the instance that we are accessing to see if it is an ASM instance.

# ===================================================================================
# Get the first few characters of the sid according to OS
# ===================================================================================
export HOST_OS=`uname -s`
if [ $HOST_OS = "Linux" ]; then
   export ORA_SID_PRE=${ORACLE_SID:0:4}
elif [ $HOST_OS = "HP-UX" ]; then
   export ORA_SID_PRE=`expr substr $ORACLE_SID 1 4`
fi

If it is an ASM instance, we are not going to do anything since ASM already includes the instance number in /etc/oratab.

This is the heart of the script and you see another one of our in-house custom environment variables, GI_HOME. This always points to the Grid Infrastructure home on the node. Here we check to see if that variable exists, if it does, execute the cemutlo utility to find out if there is a cluster. If there is a cluster, execute the olsnodes utility to find out the number of the instance on this node. We take that number and append it to the current ORACLE_SID from orenv to get the correct instance name.

# ===================================================================================
# Append the node number to the instance name for RAC and add nothing for non-RAC
#  Note: Will not append node number for ASM since it is already included.
# ===================================================================================
if [ ${ORA_SID_PRE} != +ASM ]
then
   if [ ! -z "${GI_HOME}" ]; then
    $GI_HOME/bin/cemutlo -n > /dev/null 2>&1
        if [ $? -eq 0 ]; then
      export ORACLE_SID=${ORACLE_SID}`$GI_HOME/bin/olsnodes -n -l |awk '{print $2}'`
    fi
   fi
fi

The checks in this section also allow us to skip changing the ORACLE_SID on a non-RAC database.

Now at the end, display the database name and the instance name to give the user visual feedback.

echo ======================================
echo Oracle DB : $ORACLE_DB
echo Oracle SID: $ORACLE_SID
echo ======================================

There really is not a lot to it but it is a great tool to have when you run multiple databases on a standalone server or in a cluster. Try it out, make changes and customize it for your environment.

Share your customizations in the comments, maybe it will inspire someone else or help them to solve an issue that they have!

Here is the script: oraenv_custom_code