Rotating Oracle Database’s Alert Log with ADRCI

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.

#-- File :
#-- Usage: [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 script. Something similar to the following to extract for the last month:

./ 30
./ 29
./ 28
./ 27
#-- setup

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


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.

#-- 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
#-- 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
#-- 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 = 'ADR Home'"                                 >> ${LST}/${PGM}.sql
echo "   AND  = '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):

if [ ! -d "${ALRT}/${prevday}" ]
  ${MKDIR} -p "${ALRT}/${prevday}"
if [ ! -d "${ALRT}/${day}" ]
  ${MKDIR} -p "${ALRT}/${day}"
#-- 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:

ADR Homes: 

But if I was able to create XML alert log entries by date under the diag structure I could easily have something like this:

ADR Homes: 

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles