Rotating Oracle Database's Alert Log with ADRCI

April 1, 2010

Many Oracle database administrators would not only like to report on (ORA-) errors but also manage the alert log itself--saving and/or trimming the alert log, aka alert log rotation. Oracles new ADR with command interface can easily be used to help rotate Oracles alert log.

In Alert Log Scraping with
Oracles ADRCI Utility, I looked at extracting Oracle alert errors from the alert
log; specifically the alert log log.xml within the ADRCI structures. This was
all fine and dandy but many DBAs would not only like to report on (ORA-) errors
but also manage the alert log itselfsaving and/or trimming the alert log, aka
alert log rotation.

Over the years this alert log rotation has taken
many forms. I myself have created shell scripts, Perl scripts, and PL/SQL code
that took advantage of UTL_FILE, UTL_RAW, DBMS_LOB, external DIRECTORIES, etc.
to tackle what should be a very simple task. Now, with ADRCI, Oracle has given
us yet one more method, mostly because of the XML based alert log and the whole
ADR shift in diagnostic reporting, to extract and manage the alert log. The
script below is somewhat simplistic, but useful, and because it contains the
new ADRCI utility is worthy to explain in detail. Just read through the
comments Ive provided for each section, cut and paste into your own shell
script, execute, and enjoy.

The
script will take an offset, which is the number of days in the past that you'd
like to extract the alert log for. The offset option proves useful when youre
just beginning to use this script and youve never run it before. As the script
stands, if run without supplying an offset, will extract the current days
alert log as well as yesterdays alert log. It might be suggested that a simple
set of calls be put together in another shell script that calls the
rotatealert.sh script. Something similar to the following to extract for the
last month:

The
program name will be used for various output temp files, sort of a unique
identifier.

PGM="rotatealert"

Currently,
I run this shell under the Oracle user account so $HOME would be something like
/home/oracle with an alert directory under that home directory. Also defined is
a list directory (lst) that will contain output from this shell script as well
as contain the individual alert log directories for individual days.

ALRT=$HOME/alert
LST=${ALRT}/lst
#-- Unix environment variables

This
is just my preference here as I prefer defining where Unix programs exist
explicitly.

If
nothing was entered on the command line, then we should default the offset to
today (0).

if [ -z $1 ]; then
offset=0
else
offset=$1
fi
#-- execute SQL to get some diagnostic variables from the database

This
will extract the homepath that will need to be set when calling ADRCI as well
as getting the dates of the alert log records to be extracted, less the offset
if entered. This generated script makes use of the V$DIAG_INFO view for both
ADR Home and ADR Base, both of which are required when issuing commands through
ADRCI.

The
proper timezone is needed to properly filter the alert log for date ranges. This
call to ADRCI makes use of the SHOW ALERT command with the TAIL 1 argument;
instructing ADRCI to get the last log message generated in the alert log. From
this information it is easy to strip the timezone out of the first line of
output that has the format of 2010-03-18 08:19:16.012000 -04:00 where the
third argument is the timezone.

Each
day that is extracted from the alert log will be placed in a directory that corresponds
to the date of that alert log. This section checks to see if the directory
already exists and will create directories for those days that do not have one
already created. These directories have the format of yyyy-mm-dd and will look,
with directory paths, something like the following (alert.log included):

This
sets up the call to extract yesterday & today's alert log information.
Specific to ADRCI calls, especially if you have multiple instances with
diagnostic information, would be setting the HOMEPATH. This HOMEPATH was
extracted earlier in the shell with a call to the database and is a mandatory
setting that must be made. Each days alert log information is extracted with
the SHOW ALERT command that has a pseudo WHERE clause for ORIGINATING_TIMESTAMP
between selected days.

This
section provides a way to purge entries from the alert log. Uncomment this line
of code if you wish to purge alert log entries that are older than 5 days. Depending
on your diag purge rules (SHORTP_POLICY & LONGP_POLICY) you may wish to
keep this line commented out and default to those settings.

As noted within the script, individual alert
logs (alert.log) will be generated in separate directories that are given the
name of the date that the alert log entries were extracted for. Feel free to
change this, possibly removing the directory and just having the date as part
of the alert.log filename.

My main purpose of keeping the individual
directories was to hopefully anticipate a move by Oracle to allow us to extract
the raw XML alert log data. You see, currently when we execute the SHOW ALERT
command it will strip out of all the XML around the alert log entries. This
really ISNT what Id want. Id personally like to keep the XML intact so that
I could continually use ADRCI against the raw data. Very simply, Id like to
create these directories, or have Oracle let me, maintain some form of date
structure under the diag directory itself--much like the fast recovery area
does. If I was able to do this then each date, which would include much more
than alert logs, would give me a more complete picture of what happened in the
past.

As a quick example, normally when I enter ADRCI
and enter the SHOW HOMES command Id get something like the following:

From this type of structure I could easily
extract the dates that interest me but more importantly the alert logs would
still be in the raw XML format that they should be in. Regardless, ADRCI
clearly makes rotation of the alert logs much easier. Through a few simple
commands wrapped in a simple shell script we can quickly extract, manage, and
rotate the alert log.