#!/bin/sh
#-- File : rotatealert.sh
#-- Usage: rotatealert.sh [offset]
#-- where offset is the number of days in the past you'd like to extract the alert log for
#-- The offset option proves useful when you’re just beginning to use this script. 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 as 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
#-- 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 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 here. 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}'`
#-- 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
#-- This section sets up the call to ADRCI; extracting yesterday’s & 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 and 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
#-- 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 actually extract the alert log days.
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null