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.
#!/bin/sh
#-- File : rotatealert.sh
#-- Usage: rotatealert.sh [offset]
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:
./rotatealert.sh 30
./rotatealert.sh 29
./rotatealert.sh 28
./rotatealert.sh 27
.etc.etc.etc.
#-- setup
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.
GREP=/bin/grep; export GREP
AWK=/bin/awk; export AWK
HEAD=/usr/bin/head; export HEAD
MKDIR=/bin/mkdir; export MKDIR
#-- Oracle environment variables
Very
simply, if you don’t have the environment setup properly specify it here. This
section can easily be changed depending on your environment, most likely the
ORACLE_SID will have to be changed.
ORACLE_SID=db11; export ORACLE_SID
ORACLE_HOME=`${GREP} ${ORACLE_SID}: /etc/oratab | ${AWK} -F: '{print $2}'`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
#-- check the offset entered
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.
echo "set echo off" > ${LST}/${PGM}.sql
echo "set feedback off" >> ${LST}/${PGM}.sql
echo "set heading off" >> ${LST}/${PGM}.sql
echo "set linesize 40" >> ${LST}/${PGM}.sql
echo "set pagesize 55" >> ${LST}/${PGM}.sql
echo "set verify off" >> ${LST}/${PGM}.sql
echo "set linesize 300" >> ${LST}/${PGM}.sql
echo "SELECT 'homepath:'||replace(homepath.value,adrbase.value||'/','')" >> ${LST}/${PGM}.sql
echo " FROM v\$diag_info homepath, v\$diag_info adrbase" >> ${LST}/${PGM}.sql
echo " WHERE homepath.name = 'ADR Home'" >> ${LST}/${PGM}.sql
echo " AND adrbase.name = 'ADR Base';" >> ${LST}/${PGM}.sql
echo "SELECT 'day:'||to_char(sysdate-${offset} ,'yyyy-mm-dd')" >> ${LST}/${PGM}.sql echo " FROM dual;" >> ${LST}/${PGM}.sql" >> ${LST}/${PGM}.sql
echo "SELECT 'nextday:'||to_char(sysdate-${offset}+1,'yyyy-mm-dd')" >> ${LST}/${PGM}.sql
echo " FROM dual;" >> ${LST}/${PGM}.sql" >> ${LST}/${PGM}.sql
echo "SELECT 'prevday:'||to_char(sysdate-${offset}-1,'yyyy-mm-dd')" >> ${LST}/${PGM}.sql
echo " FROM dual;" >> ${LST}/${PGM}.sql" >> ${LST}/${PGM}.sql
echo "exit" >> ${LST}/${PGM}.sql
sqlplus -s '/as sysdba' @${LST}/${PGM}.sql > ${LST}/${PGM}.lst
#-- Put the diag information variables just queried from the database into shell variables.
homepath=`${GREP} homepath ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
day=`${GREP} "^day" ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
nextday=`${GREP} nextday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
prevday=`${GREP} prevday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
#-- Get the timezone from the alert log (safest place to get)
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.
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.tmp" >> ${LST}/${PGM}.adrci
echo "show alert -tail 1" >> ${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null
timezone=`${HEAD} -1 ${LST}/${PGM}.tmp | ${AWK} -F" " '{print $3}'`
#-- create directories for daily alert logs
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):
/home/oracle/alert/2010-03-11/alert.log
/home/oracle/alert/2010-03-12/alert.log
/home/oracle/alert/2010-03-13/alert.log
/home/oracle/alert/2010-03-14/alert.log
/home/oracle/alert/2010-03-15/alert.log
if [ ! -d "${ALRT}/${prevday}" ]
then
${MKDIR} -p "${ALRT}/${prevday}"
fi
if [ ! -d "${ALRT}/${day}" ]
then
${MKDIR} -p "${ALRT}/${day}"
fi
#-- set up the call to ADRCI
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.
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${ALRT}/${day}/alert.log" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${day} 00:00:00.000000 ${timezone}' AND '${nextday} 00:00:00.000000 ${timezone}'\" -term" >> ${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
echo "spool ${ALRT}/${prevday}/alert.log" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${prevday} 00:00:00.000000 ${timezone}' AND '${day} 00:00:00.000000 ${timezone}'\" -term" >> ${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
#-- purge the alert log of old data
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.
#echo "PURGE -AGE 7200 -TYPE ALERT;" >> ${LST}/${PGM}.adrci
#-- Call to ADRCI to extract the daily alert log
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null
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:
adrci> SHOW HOMES
ADR Homes:
diag/rdbms/db11/db11
diag/tnslsnr/ludwig/listener
But if I was able to create XML alert log
entries by date under the diag structure I could easily have something like
this:
adrci> SHOW HOMES
ADR Homes:
diag/rdbms/db11/2010-03-13
diag/rdbms/db11/2010-03-11
diag/rdbms/db11/2010-03-15
diag/rdbms/db11/2010-03-14
diag/rdbms/db11/2010-03-12
diag/rdbms/db11/2010-03-10
diag/rdbms/db11/db11
diag/tnslsnr/ludwig/listener
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.
Download the script.
Additional Resources
Oracle ADRCI: ADR Command Interpreter
Oracle FAQ Adrci
OracleBrains ADR Command Interpreter (ADRCI) a new tool in Oracle 11g
»
See All Articles by Columnist
James Koopmann
» 
Comment and Contribute
