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.
To redirect program output, UNIX provides the << and >> commands.
This is very useful when the Oracle DBA wants to keep logs in the
UNIX environment. A typical
usage might be to write a notation to the Oracle alert log that the file
has been checked for errors.
The command below could be used for this purpose:
echo ‘*****Alert log checked at
2/5/2002
by Andrew Kerber ****’ >> \
$DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
The >> command can also be used to append to an existing file of errors.
A common application is to keep a running list of all Oracle
trace file names. The
following command can be used to write all new trace file names into a
list. Note that the first command line uses a single “>” to recreate the
trace_file_names.lst file, while subsequent redirects 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, it may be necessary to suppress UNIX command
output.
Redirecting Output to a NULL Device
If one does not wish to see the output of a UNIX command (typically a
batch job), use the /dev/null device to suppress the output. The
/dev/null device is the equivalent of the DD DUMMY syntax in the Cobol.
The /dev/null device is often used in scheduled tasks (using the
UNIX crontab utility) if the output from the command is not required. In
the next example, submit a large batch job and suppress the output.
The ampersand causes UNIX to run the job in the background (detached
from the starting process), and the “2>&1” syntax tells UNIX to redirect
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
The tee command can be used
to redirect multiple output streams.
Common Oracle UNIX Commands
Now that the basics have been detailed, next to be viewed are the common
UNIX commands that are used by the Oracle DBA.
Many of these commands are extremely useful to automate Oracle
reporting and make Oracle management simple.
Capturing server information with UNIX
For the Oracle DBA who manages databases on multiple servers, capturing
the server name is very important.
This is especially true in cases where several databases with the
same name exist in several servers, and the report must show the server
name to properly identify the database.
One common command to gather server information is the uname –a command.
Here is an example of the output from this command in Solaris
UNIX.
SunOS
zarda 5.8 Generic_103634-03 sun4u sparc SUNW,Ultra-80
Here is the uname –a command on a HP/UX server:
HP-UX gates B.10.20 A 9000/871 2639229148 two-user
license
In this example, first column is the UNIX dialect (SunOS, HP-UX), the
server name (gates, zarda), the version of the OS, the serial number of
the server, and miscellaneous server information. If the DBA only needs
the server name for an Oracle report, extend the uname –a command to
capture the server name by using the awk utility and parsing out the
first column of output:
root> uname -a|awk '{ print $2 }'
goofy
Also available is the hostname command to display the server name.
Next to be reviewed is how the wc command can be used to monitor the
frequency of events in the Oracle database.
Contrary to its name, it has nothing at all to do with plumbing.
The UNIX wc command
The UNIX wordcount command (wc) is used to display the number of words
or lines (or characters) in a UNIX file.
The wc command is typically used by the Oracle DBA who is looking
to monitor the contents of specific messages with the Oracle alert log.
For example, count the number of words in the Oracle alert log:
root> cat alert_envtest.log|wc
108313
741411 5334959
From the wc command, see that the Oracle alert log has 180,313 lines,
741,411 words and 5,334,959 characters.
The wc –l option is used to only display the number of lines on a
file.
root> cat alert_envtest.log|wc –l
108313
A typical usage for the wc –l command is to count the number of specific
messages in the Oracle alert log. Belowed the text of all ORA-600 errors
in the alert .log using the cat and grep commands are displayed.
root> cat alert_envtest.log|grep ORA-00600
ORA-00600: internal error code, arguments: [2655], [0], [1], [], [], []
ORA-00600: internal error code, arguments: [16365], [2208470888], [1],
[4]
ORA-00600: internal error code, arguments: [16365], [2209886568],
[0], [4]
Now add the wc –l command to count the number of
ORA-600 errors:
root> cat alert_envtest.log|grep ORA-00600|wc -l
3
