oraenv on Steroids Part 1

Most DBA’s have encountered the oraenv script, the one that is generated by root.sh:

This script is just as useless as it was in 1993.  If you just have to enter the $ORACLE_SID and $ORACLE_HOME, why do you even need this script?  In 25 years of Oracle DBA experience I have never once used it.  It was bad enough then, but now?  The environment variables like $ORACLE_HOME and $ORACLE_SID are a management nightmare.   On a modern RAC system you have at least 2 Oracle homes (the grid infrastructure and the RDBMS).  And the ORACLE_SID is not just the database name, but also the node number.  So you literally need a different value for every node in your cluster.

But there is a solution to all of this that is already on your servers:  /etc/oratab.  You can use this file to configure your environment correctly for every database server you have whether it is RAC or not just by using it.  Consider a typical /etc/oratab from a RAC cluster:

$ cat /etc/oratab
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
# Entries are of the form:
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
# Multiple entries with the same $ORACLE_SID are not allowed.
dbistdby:/u01/app/oracle/product/12.1.0/db:N      # line added by Agent
+ASM1:/u01/app/12.1.0/grid:N                      # line added by Agent
dbinternals:/u01/app/oracle/product/12.1.0/db:N   # line added by Agent

From this file, we can see there are 2 databases (dbinternals and dbistdby), as well as a grid infrastructure on this server.   We can easily extract the ORACLE_HOME and ORACLE_SID from it using just the database name, i.e:

$ grep dbinternals /etc/oratab
dbinternals:/u01/app/oracle/product/12.1.0/db:N   # line added by Agent
$ export ORACLE_SID=`grep ^dbinternals /etc/oratab | cut -f1 -d":"`
$ export ORACLE_HOME=`grep ^dbinternals /etc/oratab | cut -f2 -d":"`
[oracle@oracle-linux ~]$ echo $ORACLE_SID

But there is a problem.  In a RAC environment, the $ORACLE_SID isn’t just the DB name.  It also has the node number in it too.  In our example, the actual ORACLE_SID used is dbinternals1.  Fortunately, that is easy to extract from /etc/oratab too.  You just get it from the +ASM entry, i.e:

$ export NODE=`grep ^+ASM /etc/oratab | cut -c5`
$ echo $NODE

So you can combine these two together to get the actual ORACLE_SID, i.e:

$ echo $ORACLE_SID

So now we have the correct ORACLE_SID and ORACLE_HOME based on the values in /etc/oratab, and we have the basis for a script we can deploy throughout our enterprise without modifications.  In part 2, we’ll make this script.




Comments are closed.