#!/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