The Oratab Script

I recently wrote a Python scrip for parsing the Oracle oratab file and printing out the contents in a clean, easy to read format. I’ve found the script to be very useful and more convenient than grepping through the raw oratab file for what I’m looking for. Today, I added some pretty nifty features by way of command line options and decided I’d go ahead and put it up here for anyone interested. The help (-h) option is pretty self explanatory so I won’t be going into further detail here. I’ll simply show the usage and some sample output. Take it for a spin and post some feedback.

The script is called “oratab” and can be downloaded from at DBA Scripts.

Here is the usage and a few samples…

> oratab -h

Report Contents of the Oratab File.

 -h, --help show this help message and exit
 -d report distinct oracle homes found
 -i report only invalid oracle homes
 -r show the restart flag
 -t terse report format
 -v report only valid oracle homes
 --v print version info.

Listing #1 – Default Output

> oratab

Oratab File: /etc/oratab

Oracle SID           Oracle Home
-------------------- ------------------------------------------------------------
+ASM1                /u01/app/
-MGMTDB              /u01/app/
ASCFIN               /u01/app/oracle/product/
ASCFIN1              /u01/app/oracle/product/
ASCSYM               /u01/app/oracle/product/
ASCSYM1              /u01/app/oracle/product/
CSM                  /u01/app/oracle/product/
CSM1                 /u01/app/oracle/product/
ILTESTP              /u01/app/oracle/product/
OGGBDA               /u01/app/oracle/product/
OGGBDA1              /u01/app/oracle/product/
DBM01                /u01/app/oracle/product/
DBM011               /u01/app/oracle/product/
DEMO12               /u01/app/oracle/product/
DEMO121              /u01/app/oracle/product/
ENT                  /u01/app/oracle/product/
ENT1                 /u01/app/oracle/product/
ENT11                /u01/app/oracle/product/
ENT111               /u01/app/oracle/product/
ESIPOC               /u01/app/oracle/product/
ESIPOC1              /u01/app/oracle/product/
UPONOR               /u01/app/oracle/product/
UPONOR1              /u01/app/oracle/product/

Listing #2 – Distinct Oracle Homes

> oratab -d

Oratab File: /etc/oratab

Oracle Home

Listing #3 – Terse Format

> oratab -t
+ASM1                /u01/app/
-MGMTDB              /u01/app/
ASCFIN               /u01/app/oracle/product/
ASCFIN1              /u01/app/oracle/product/
ASCSYM               /u01/app/oracle/product/
ASCSYM1              /u01/app/oracle/product/
CSM                  /u01/app/oracle/product/
CSM1                 /u01/app/oracle/product/
ILTESTP              /u01/app/oracle/product/
OGGBDA               /u01/app/oracle/product/
OGGBDA1              /u01/app/oracle/product/
DBM01                /u01/app/oracle/product/
DBM011               /u01/app/oracle/product/
DEMO12               /u01/app/oracle/product/
DEMO121              /u01/app/oracle/product/
ENT                  /u01/app/oracle/product/
ENT1                 /u01/app/oracle/product/
ENT11                /u01/app/oracle/product/
ENT111               /u01/app/oracle/product/
ESIPOC               /u01/app/oracle/product/
ESIPOC1              /u01/app/oracle/product/
UPONOR               /u01/app/oracle/product/
UPONOR1              /u01/app/oracle/product/

The DBA Scripts project is a collection of scripts useful to Oracle DBA’s. I’ll be posting a series of blogs discussing the other scripts found in this Github project. In the meantime the help (-h) option should give you a pretty good idea of what they do and how to use them.

Oracle Environment Framework

Several years ago I worked with a group of Java developers who were constantly having problems getting their applications connected to the database. Most of the time the problem turned out to be a missing or malformed CLASSPATH or something else in their environment. UNIX provides a very flexible user environment that is easily customized to suit your needs. It does this through a login script (or profile) that is executed automatically whenever you login to the system. Whatever you put in this login script becomes a part of our environment next time you login. The problem is that there is no built-in error checking to protect you from hurting yourself. For example there is nothing to tell you that the PATH you set is invalid or malformed. Consequently it is very easy to miss-configure environment variables without realizing it. This is usually followed by several unproductive hours trying to figure out why an application no longer works properly. To compound the problem, profiles are inherently messy and difficult to read which can make it difficult to spot a problem (even for the seasoned professional).

Typically login scripts export a few variables and then pass control to the user. But login scripts can be much more than that. Since they are really just another shell script, they possess all the tools and capabilities of the shell language itself. This got me to thinking… Why not leverage the strengths of the shell scripting language to create a structured framework for the environment; one that would make it easier to make changes and harder to make mistakes. To be effective this framework must:

  1. Be light weight (execute quickly)
  2. Simplify configuration
  3. Provide error checking
  4. Provide consistency
  5. Be extensible

The Oracle Environment Framework (or “the framework” as it has become known) has evolved and grown over the years. When I began writing it I was a little worried that the scripting logic and error checking would cause the login process to be sluggish. I was pleasantly surprised to find that this never became an issue. Today the framework is about 1,000 lines of code (give or take) and logging in doesn’t appear to take any longer than it did with any other garden variety login script.

At the heart of the framework is main.env script. Main.env is the engine of the framework. It contains all the logic and error checking. Main.env is sourced from your bash or korn shell login script. Changes to this script (and the login script as well for that matter) should never be necessary. The following listing shows the complete contents of my /home/oracle/.bash_profile script:

if [ -r $MAIN_ENV ]; then
   . $MAIN_ENV
   echo "Could not open $MAIN_ENV for read."

When main.env is called it parses the oratab file and presents you with a menu of the database instances it found. The following listing shows an example of this menu for a system hosting 11 databases (and one ASM instance).

                          Welcome to enkdb03
                          Env. Framework 4.3

   Available database environments:

     - ACDB1
     - ASM1
     - BOSTON
     - DBFS1
     - dbfspoc1
     - dbm1
     - DEMO1
     - DPPOC1
     - LABRAT1
     - POV1
     - RANDY1
     - VIS1

   Enter a selection from the list above to change your environment.
   Current Environment: DEMO1

[enkdb03:oracle:DEMO1] /home/oracle

Note: If the list of databases is over 15 then it is organized in up to 4 columns (allowing for many more instances than one should run on a server).

By default, main.env configures your Oracle environment for the first database instance it finds in the oratab file. You can change this behavior by providing an instance name on the call to main.env. Either way the framework configures your Oracle environment as follows:

. /usr/local/bin/oraenv

This should be very familiar to Oracle DBA’s. When the oraenv script is called it also sets your ORACLE_HOME variable and adds it to your PATH. In addition (and depending on platform, version, etc.) the framework also sets other Oracle environment variables such as:

  • ORA_NLS10
  • ORA_NLS32
  • ORA_NLS33
  • TEMP
  • TMP

The framework automatically creates an alias named after each instance. For example the following alias was created for the DEMO database:

alias DEMO1='. /home/oracle/.env/main.env DEMO1'

As you can see the alias is simply a call to main.env with a parameter of the instance name. So changing your environment from one database to another is as simple as entering the database name (instance name actually) at the prompt. Your Oracle variables are reset, your path is reset, and the menu is displayed. And since all this is driven from the oratab file, new databases are discovered and added to the menu automatically.

All of the above (and quite a bit more) comes from the main.env script. But there are a handful of special files that are used as input to main.env. As their names imply, these files are parsed by the framework to configure common variables such as CLASSPATH, LD_LIBRARY_PATH, LIBPATH, MANPATH, PATH, PERL5LIB, and PYTHONPATH. If necessary, more files may be added by modifying the main.env script. As you might expect, these files are parsed for directories to add to add to each respective path. The following example shows the PATH file. Directories are added to your PATH in the order in which they are read from this file.


Note: As you can see in the example it is perfectly okay to use environment variables in these path files.

Remember that one of the goals of the framework was error checking. So before each directory is added to your PATH the framework checks to see if it exists. If it doesn’t, a warning message will be displayed. For example if I were to add a /foo directory to my PATH file I would see the following warning message the next time it is parsed:

--------------- W A R N I N G -------------------------------------------------
 Inaccessible path specified for PATH.
    Remove the invalid path from the PATH file or ensure the directory
    exists *and* that you have sufficient permissions on it's parent directories.
    PATH file: /home/oracle/.env/PATH
    PATH Spec: /foo
--------------- W A R N I N G -------------------------------------------------

Notice that the framework warning tells you which path is invalid and which file it was found int. This makes it very easy to locate and fix the source of the problem. The framework also prevents adding duplicate directories to your PATH.  This is keeps your environment neat and tidy.

In a similar way to the path files we just discussed, the ALIAS file is used to maintain your shell aliases in a key=value format. For example the following snippet shows an example of an ALIAS file:

# Setting up useful aliases
cdump='cd $_CORE_DUMP_DEST_'
udump='cd $_USER_DUMP_DEST_'
adump='cd ${ORACLE_BASE}/admin/${_DB_UNIQUE_NAME_}/adump'
create='cd ${ORACLE_BASE}/admin/${_DB_UNIQUE_NAME_}/create'
pfile='cd ${ORACLE_BASE}/admin/${_DB_UNIQUE_NAME_}/pfile'
onet='[ -n "$TNS_ADMIN" ] && cd $TNS_ADMIN || cd $ORACLE_HOME/network/admin'
obin='cd $ORACLE_HOME/bin'
ohome='cd $ORACLE_HOME'
cd $ORACLE_HOME/dbs'
ll='ls -l'
lt='ls -ltr'
talert='tail -f $_ALERT_LOG_'
alert='view $_ALERT_LOG_'

This file is parsed and fed to the alias command to define your shell aliases dynamically. Notice that you can use environment variables in this file as well. The file is parsed *after* your Oracle environment is set, so referencing $ORACLE_HOME allows your aliases to reset as you move from database to database. These aliases are also dynamically added to your .bashrc so they will be available even from inside of an application like Sqlplus.

Requirements and configurations can vary quite a bit from one implementation to another. That is why it was so important to make the framework extensible. This is provided through the use of an envfile.list file. Envfile.list contains a list of shell scripts you specify. The framework will check for these files and source them into the environment if they exist. Each script can contain anything you want to add to your environment. For example there are always environment settings that, for one reason or another, don’t really have an obvious home inside the framework; settings like EDITOR=vi and HISTSIZE=10000.  obvious home inside the framework; things like EDITOR=vi, HISTSIZE=10000, and “set –o vi”.  So, I usually create a script called misc.env, a sort of ‘kitchen-sink’ script where I throw various unrelated settings. Another clever way to use this feature is to segregate and prioritize application specific environment variables. For example let’s say you add a new application called ToolBench to your system. You could create a file called ToolBench.env and add it as an extension to the framework. The following listings show how these extensions may be added to the framework.

> cat envfile.list
# ---------------------------------------------------------------------
# This is the environment list file. It contains a list of additional
# environment scripts you wish to have executed (dotted/sourced) into
# your current environment. This file is parsed by the main environment
# file 'main.env'.
# By convention, environment files have an extension of '.env'. this is
# NOT required but is usually the recommended naming method. As in one
# case below '' you may wish to not rename a file you
# got from someone else. This is fine. Just add the file to this list
# and it will be sourced into the environment that calls main.env.
# ---------------------------------------------------------------------

> cat misc.env
# Miscellaneous Environment Variables
export EDITOR=vi
export HISTSIZE=10000
export TERM=vt100
export NLS_DATE_FORMAT='yy-mm-dd hh24:mi'
umask 022
set -o vi

#For Oracle RAC
if [ -t 0 ]; then
   stty intr ^C

> cat ToolBench.env
# ToolBench Environment Variables
export DBTOOLS_HOME=~oracle/dbtools

The extension scripts listed in envfile.list must follow all the same rules as any other login script in that they are simply sourced into your current environment as is, one after another, no error checking done. In particular, take care to avoid variable name conflicts. Notice in the example above that the ToolBench.env script is sourced after the misc.env script. This means that any environment variable that occurs in both files will retain the value set in the ToolBench.env script (since it was sourced after misc.env).

This blog post touched on some of the most important features of the Oracle Environment Framework. There are a few more that we didn’t cover here like support for Python, Perl, and E-Business Suite databases. The complete framework including User’s Guide is available for download at

A a brief description of the GPL License follows.

Copyright 1999-2012 Randy Johnson
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <>.