Oracle UNIX Administration Command Line Editor Oracle UNIX/Linux Tips by Burleson Consulting

The UNIX command line editor

In UNIX, your login profile file (.profile, .kshrc) issues UNIX commands at login time to allow you to easily navigate in UNIX.  The commands in your logon profile set your terminal type, your command line editor, and other shortcuts that make UNIX navigation easy.

The most important command is the set ?o command because it defines the command line editor.  Let?s take a closer look at how the command line editor makes you life in UNIX easier.

UNIX allows you to set the type of command editor.  Once set, you can use a variety of shortcuts to quickly re-display previous UNIX commands. These shortcut commands will greatly reduce the amount of typing at the UNIX prompt, and you can always recognize a UNIX guru because of their use of these command shortcuts.  There are two common settings for the command line editor, emacs and vi.

* set ?o emacs ? This command sets the emacs editor for editing online UNIX commands.

* Command completion ? the emaces setting allows you to complete long file name by pressing the escape key twice ( <esc><esc>).  For example, to vi the file oracle_script_for_checking_permissions.ksh, you could enter vi oracle <esc><esc>, and the command line will display:

root> vi oracle_script_for_checking_permissions.ksh

* Display previous commands ? the emacs editor allow you to view prior command by pressing the <ctrl> p keys.

* set ?o vi ? This command sets the vi editor for online UNIX commands.  Once a command is displayed at the UNIX prompt you can use standard vi commands to edit the command.  In addition, the set ?o vi command allows for easy searches of the UNIX command history:

* Command completion ? the vi setting allows you to complete long file name by pressing the escape backslash ( <esc> \).  For example, to vi the file oracle_script_for_checking_permissions.ksh, you could enter vi oracle <esc> \, and the command line will display:

root> vi oracle_script_for_checking_permissions.ksh

* Display previous commands ? the vi editor allow you to view prior command by pressing the <esc> k key.

* Search the command history ? You can search for a specific command in your command history and display it on your command line by pressing the escape key and the forward slash ( <esc> / ). For example, to re-display a command that contains ksh, you would enter <esc> /, followed by ksh.  The matching command will then be displayed on your command line.

To automatically set this value, you can place the following code in your login file (.profile, .kshrc, .cshrc)

#*****************************************************************
# Backspace and Keyboard editor setting
# This setting allows the following shortcuts:
#    <esc> k                 to display command history
#    <esc> \                 for command completion
#    <esc> / searchstring    to find a command in the history file
#*****************************************************************
stty erase ^?

set -o vi

export EDITOR=vi

Once you have set your basic UNIX environment, you are ready to look at setting your UNIX environment for Oracle.

Oracle aliases for UNIX

A UNIX alias is a short command that is replaced with a larger command to save typing time.  For example, we could create an alias to allow us to count the number of connected users on our oracle server:

root> alias numuse=?who|wc ?l?
root> numuse
463

The whole point of UNIX aliases is to save typing.  Here is a list on common UNIX aliases that can be added to your UNIX logon file for the UNIX Oracle user.  These aliases 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'

The most important alias here is the alias for the rm command.  By dynamically replacing rm with rm ?i, you guarantee that you do not accidentally remove an important Oracle file from UNIX.

To illustrate how useful aliases are in Oracle administration, in the example below we can get to our pfile directory in a single command so we can view the contents of our init.ora file:

cheops*CPRO-/home/oracle
> pfile
cheops*CPRO-/u01/app/oracle/CPRO/pfile
>ls

initCPRO.ora

Aliases can also be used for 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 -100 $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

A standard UNIX prompt for Oracle users

Placing the following code snippet in your oracle user login file will give you a standard UNIX prompt that identifies your current server name, the database name you?re environment is set for (i.e. the value of your $ORACLE_SID UNIX variable), and your current working directory. This standard prompt makes it very easy to know where you are when navigating UNIX, and it also ensures that you know where you 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 features of the standard command prompt is that it also places the command prompt on the next line so you can have  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
> 

Changing your Oracle environment in UNIX

One of the confounding problems on UNIX is the bewildering number of things that must be re-set every time you change your environment from one Oracle database to another database on the same UNIX server.  Oracle provides a command script called oraenv to re-set your Oracle environment, but it has several problems, and most experienced DBAs issue the following commands 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 '

Obviously, this is a lot of typing!  Rather the re-set all of these Oracle variables, experienced 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.

The best solution to the problem of re-setting the Oracle environment is to create an alias for every Oracle database on your UNIX server.  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

Let?s take a close look at how this works. First we have a FOR loop in UNIX.  Let?s decompose this command and see what it is doing:

for DB in `cat /var/opt/oracle/oratab|\
grep -v \#|grep -v \*|cut -d":" -f1`

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, we see that the command in the graves does the following:

a) cat the var/opt/oracle/oratab file (/etc/oratab in AIX).  This lists all databases defined on the UNIX server:

root>cat /var/opt/oracle/oratab
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

b) Next, we see the grep ?v \# and the grep ?v \* commands.  These ignores any lines in the oratab file that are commented-out

root>cat /var/opt/oracle/oratab|grep ?v \#|grep ?v \*
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

c) Next we issue the cut -d":" -f1 command.  This extracts the first column in the oratab file using the colon ?:? as the column delimiter:

root>cat /var/opt/oracle/oratab|grep ?v \#|grep ?v \*|cut ?d?:? ?f1
test9i
testc1
testman

We now have a list of valid $ORACLE_SID values.  Inside the for loop, we create an alias with the value of $DB (The $ORACLE_SID name), and perform all of the required changes to re-set the UNIX environment for that database.

Next, let?s look at the processes of dissecting complex UNIX commands. It is the job of the Oracle DBA in UNIX to be able to interpret complex UNIX commands so you can understand their meaning and manage the commands.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.