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

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

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

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

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