Oracle aliases for UNIX

A UNIX alias is a short command that is replaced with a larger command to save typing. A common alias used to count the number of connected users on the oracle server is numuse:

Here is a list of common UNIX aliases that can be added to the UNIX logon file for the Oracle user. These perform common Oracle functions such as checking the Oracle alert log and transferring quickly between directories.

#*******************************
# UNIX aliases for Oracle DBAs
#*******************************
   alias alert='tail -100 \
           $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more'
   alias errors='tail -100 \
           $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more'
   alias arch='cd $DBA/$ORACLE_SID/arch'
   alias bdump='cd $DBA/$ORACLE_SID/bdump'
   alias cdump='cd $DBA/$ORACLE_SID/cdump'
   alias pfile='cd $DBA/$ORACLE_SID/pfile'
   alias rm='rm -i'
   alias sid='env|grep ORACLE_SID'
   alias admin='cd $DBA/admin'

To illustrate the usefulness of aliases in Oracle administration, in the example below, get to the pfile directory in a single command so the contents of the init.ora file can be viewed:

Aliases can also be used for more sophisticated Oracle commands. For example, the following alias can be used to display all Oracle errors in the last 400 lines of the alert log:

cheops*testsid-/u01/app/oracle/admin/envtest/pfile
>alias errors='tail -400 $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|\
grep ORA-'

cheops*testsid-/u01/app/oracle/admin/envtest/pfile
>errors

ORA-00604: error occurred at recursive SQL level 1
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-00604: error occurred at recursive SQL level 3
ORA-01089: immediate shutdown in progress - no operations are permitted

The following code snippet in the oracle user login file will configure a standard UNIX prompt that identifies the current server name, the value of the current $ORACLE_SID UNIX variable, and the current working directory. This standard prompt makes it very easy to know where the DBA is when navigating UNIX, and it also ensures that the DBA knows where they are located at all times.

#*****************************************************************
# Standard UNIX Prompt
#*****************************************************************
PS1="
`hostname`*\${ORACLE_SID}-\${PWD}
>"

This standardized Oracle UNIX prompt has the advantage of displaying the server name, the ORACLE_SID and the current directory. The best feature of the standard command prompt is that it also places the command prompt on the next line so that there are a full 80 characters to type UNIX commands:

cheops*CCPRO-/home/oracle
>pwd

/home/oracle

cheops*CCPRO-/home/oracle
>cd /u01/oradata/CPRO

cheops*CCPRO-/u01/oaradata/CPRO
>

A typical problem on any UNIX server is quickly setting the many environment variables necessary to change the Oracle environment to a different database.

Oracle provides a command script called oraenv to reset the Oracle environment, but it often does not work properly, and most experienced DBAs know that the following commands must be issued to change from one ORACLE_SID to another:

       export ORAENV_ASK=NO;\
       export ORACLE_SID='$DB';\
       .TEMPHOME/bin/oraenv;\
       export ORACLE_HOME;\
       export ORACLE_BASE=\
         `echo ORACLE_HOME | sed -e 's:/product/.*::g'`;\
       export DBA=$ORACLE_BASE/admin;\
       export SCRIPT_HOME=$DBA/scripts;\
       export PATH=$PATH:$SCRIPT_HOME;\
       export LIB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib:/usr/lib '

Most DBAs create a UNIX alias with the same name as the ORACLE_SID. When the ORACLE_SID is entered at the command prompt, all of the required commands are executed by means of a shell script. Below is the login profile code to perform this function.

   *****************************************************************
   # For every Oracle_SID in /var/opt/oracle/oratab,
   # create an alias using the SID name.
   # Now, entering the ORACLE_SID at the UNIX prompt will completely set the
   # UNIX environment for that SID
   #*****************************************************************

   for DB in `cat /var/opt/oracle/oratab| \
   grep -v \#|grep -v \*|cut -d":" -f1`
   do
      alias $DB='export ORAENV_ASK=NO; export ORACLE_SID='$DB'; .
TEMPHOME/bin/oraenv; export ORACLE_HOME; export ORACLE_BASE=`echo
ORACLE_HOME | sed -e 's:/product/.*::g'`; export DBA=$ORACLE_BASE/admin;
xport SCRIPT_HOME=$DBA/scripts; export PATH=$PATH:$SCRIPT_HOME; export
IB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib:/usr/lib '
   done

Next, examine how this works. First there is a FOR loop in UNIX. Decompose this command and see what it is doing:

The for DB in command means that the script will loop once for each value of $DB. The argument to the for DB in command is enclosed in graves (pronounced “gra-vees”), which is the back-tick character (directly above the tab key on a PC keyboard). Arguments enclosed in graves tell UNIX to execute the command enclosed in the graves and return the result set to UNIX. In this case, the command in the graves does the following:

test9i:/u01/app/oracle/product/8.1.7_64:Y
testc1:/u01/app/oracle/product/8.1.7_64:Y
#testc2:/u01/app/oracle/product/8.1.7_64:Y
testman:/u01/app/oracle/product/8.1.7_64:Y

test9i:/u01/app/oracle/product/8.1.7_64:Y
testc1:/u01/app/oracle/product/8.1.7_64:Y
testman:/u01/app/oracle/product/8.1.7_64:Y

root>cat /var/opt/oracle/oratab|grep –v \#|grep –v

\*|cut –d”:” –f1

There is now a list of valid $ORACLE_SID values. Inside the for loop, create an alias with the value of $DB (The $ORACLE_SID name), and perform all of the required changes to reset the UNIX environment for that database.

Now turn to the processes of dissecting complex UNIX commands. An Oracle DBA must be able to interpret complex UNIX commands.

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.