Oracle UNIX Administration Commands Oracle UNIX/Linux Tips by Burleson Consulting

Introduction to UNIX commands

The Oracle professional must know a core set of UNIX commands in order to perform database administration. It is imperative that the Oracle DBA understand how to navigate in UNIX and read and write to UNIX files. This chapter will begin with the most basic UNIX commands for the Oracle professional and subsequent chapters will get progressively more sophisticated.

UNIX command syntax

Commands in UNIX are very foreign to the Oracle professional who is accustomed to using a GUI to perform database administration.  All UNIX commands are entered from the UNIX command prompt, and there are several noteworthy considerations about UNIX command syntax

UNIX line continuation characters

In UNIX, single commands can easily reach hundreds of characters. In order to prevent command wrapping it is possible in UNIX to continue a command on subsequent lines with the backslash ?\? character.  In the following example, we have a single UNIX command that scans the last 1,000 lines of the Oracle alert log searching for ORA-600 errors and mailing them to the DBA.  We can partition this long UNIX commands onto several lines with the \ character.

mailx ?s ?list of ORA-600 errors from alert log? don@mydomain.com < \
tail -1000 $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log| \
grep ORA-00600

Getting help for UNIX commands

As we begin our foray into UNIX commands for Oracle, it is critical that you understand how to find help.  All UNIX systems provide manual pages for each and every UNIX command, and this documentation is commonly called the man pages.

To see details about the command syntax for any UNIX command, we simply preface the command with the man command.  In the example below we ask UNIX to give us details about the sort command.

root> man sort
Reformatting page.  Please Wait... done

User Commands                                             sort(1)

NAME
     sort - sort, merge, or sequence check text files

SYNOPSIS

     /usr/bin/sort [ -bcdfimMnru ]  [ -k keydef ]  [ -o output  ]
     [ -S kmem ]  [ -t char ]  [ -T directory ]  [  -y   [ kmem ]
     ]  [ -z recsz ]  [  +pos1   [ -pos2 ]  ]  [ file ... ]

     /usr/xpg4/bin/sort  [  -bcdfimMnru  ]   [  -k keydef  ]    [
     -o output  ]   [ -S kmem ]  [ -t char ]  [ -T directory ]  [
     -y   [ kmem ]  ]  [ -z recsz ]  [  +pos1   [ -pos2 ]   ]   [
     file ... ]

DESCRIPTION

The sort command sorts lines of all the named files together and writes the result on the standard output.

Comparisons are based on one or  more  sort  keys  extracted from  each line of input. By default, there is one sort key,  the entire input line. Lines are ordered  according  to  the collating sequence of the current locale.

OPTIONS

The following options alter the default behavior:

/usr/bin/sort

-c    Checks that the single input file is ordered as specified  by  the  arguments and the        collating sequence of the current locale. The exit code is set and no output is         produced unless the file is out of sort.

Tip ? Saving man pages to a file

You can use the UNIX re-direct symbol ?>? to write the syntax of the man pages into a file, that you can view at your leisure.  In the following example we write the output from the man sort command into a UNIX file called man_sort.lst.  Next we can use the grep utility to search for specific options with the stored text of the man page:

root> man sort > man_sort.lst
Reformatting page.  Please Wait... done
root> grep -i unique man*

-u    Unique: suppresses all but one in each  set  of  lines

As we see, the > symbol is used to re-direct the output from any UNIX command. Let?s take a close look at how re-direction works in UNIX.

re-directing UNIX output

UNIX uses the greater-than (>) and less than (<) symbols to allow you to re-direct the output from a command to a file location.  The placement of the > or < depends upon the placement of the initial command.  In this example, we take the command output from the ls command and re-direct it into a UNIX file called man_ls.lst:

root> man ls > man_ls.lst

Note that any prior contents of the man_ls.lst command will be obliterated because UNIX re-direction will always completely re-write the file. The re-direct command cal also use the < operator. In the following example we take the last ten lines of the Oracle alert log and mail them to the Oracle DBA.

mailx ?s ?last 10 lines of alert log? don@mydomain.com < \
tail -10 $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log

The UNIX re-direct command also allows us to append new lines of output into existing files.  Let?s take a look at how we can append command output into UNIX files.

Appending data to UNIX files

UNIX provides the << and >> commands to re-direct output onto the end of an existing file. This is very useful when the Oracle DBA wants to keep logs in the UNIX environment.  For example, suppose the Oracle DBA wants to add a notation to the Oracle alert log that he has just check the recent contents for errors. We could use the following command for this purpose:

echo ?*****Alert log checked at 2/5/2002 by Don Burleson ****? >> \
$DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log

We can also use the >> command to append to an existing file of errors.  Suppose we are keeping a running list of all Oracle trace file names.  We could use the following command to write all new trace file name into a list. Note that the first command line uses a single ?>? to re-create the trace_file_names.lst file, while subsequent re-directs use the ?>>? directive to append new entries to this file.

ls ?al $DBA/$ORACLE_SID/bdump/*.trc  > /tmp/trace_file_names.lst
ls ?al $DBA/$ORACLE_SID/udump/*.trc >> /tmp/trace_file_names.lst
ls ?al $DBA/$ORACLE_SID/cdump/*.trc >> /tmp/trace_file_names.lst

Next, let?s look at how to suppress UNIX command output. 

Re-directing output to a NULL device

When we have a batch job or other Oracle task where the output is not useful, we can use the /dev/null device to suppress the output. The /dev/null device is the equivalent of the DD DUMMY syntax in the Cobol language.  The /dev/null device is commonly used in schedule tasks (using the UNIX crontab utility) where the output from the command is not required. In this example, we submit a large batch job and suppress the output. 

The ampersand tells UNIX to submit the job in the background, and the ?2>&1? syntax tells UNIX to re-direct the standard error output to standard output.  In this example, all possible output from large_file.exe will be suppressed:

./large_job.exe & 2>&1 > /dev/null

Next, let?s look at multiple re-direction with the tee command.

Using the tee command to re-direct output to files

The tee command is used when you want to display output on the screen and also write the command output to a file.  The tee command is very useful when you want UNIX script output re-directed to several locations. In the example below we e-mail all errors in the alert log to the DBA and also re-direct the output to a filed called error.log.

mailx ?s ?Oracle Alert Log Errors? don@mydomain.com < \
tail -10 $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log | \
grep ORA-|\
tee error.log

Now that understand re-direction, let?s take a look at how we can connect the output from UNIX commands together.

Piping UNIX commands

The pipe command is one of the most important commands in UNIX because it allows us to create powerful functions in a single statement. The pipe command is represented with the | character and it is used to ?connect? the output from one command and send it as input to another command.

For example, suppose we want to list the distinct file owners in a directory. To do this, we must perform three discrete tasks (Figure 1-2):

1. We must list all files in the directory (ls ?al)

2. We must parse this output and extract the file owner from the fourth column of the output. (awk ?{ print $3 }?)

3. We must then take the list of file owners and remove duplicate entries (sort ?u)

Figure 2: Piping output from commands

Using the pipe command, we can tie these three functions together into a single UNIX command, piping the output from one command as sending it as input to the next UNIX command:

root> ls -al|awk '{ print $3 }'|sort -u
marion
oracle
root

Let?s take a closer look at how this works

First, we execute the ls ?al command to get the fill details for each file in the directory

root> ls -al
total 928188
drwxr-xr-x  21 oracle   dba         2048 Aug 22 20:47 .
drwxr-xr-x  10 root     root         512 Jul 26 08:49 ..
-rw-------   1 oracle   qmail         71 Sep  1  2000 .TTauthority
-rw-------   1 oracle   dba          254 Feb 20  2001 .Xauthority
-rw-------   1 oracle   qmail        437 Aug 12 20:43 .bash_history
drwxr-xr-x  11 oracle   qmail        512 Sep  3  2000 .dt
-rwxr-xr-x   1 oracle   qmail       5111 Sep  3  2000 .dtprofile

2 ? Next, pipe the output from this command to the awk utility to only display the owner of each file:

cheops*envtest-/export/home/oracle
root> ls -al|awk '{ print $3 }'

oracle
root
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle

3 ? Finally, we pipe the owner list to the UNIX sort command to remove duplicate entries:

root> ls -al|awk '{ print $4 }'|sort -u

dba
other
qmail
root

 

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.