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