Oracle UNIX Scheduling and Customizing Alert Reports Administration

Oracle UNIX/Linux Tips by Burleson Consulting

Scheduling and Customizing Oracle Alert Reports

In a UNIX environment, the Oracle DBA can easily control the times when the STATSPACK alert reports are executed. Let?s begin by examining a UNIX crontab file that schedules these reports.

A Sample UNIX Crontab to Schedule Oracle Reports and Alerts

The following is a sample of a UNIX crontab file that is used to schedule STATSPACK reports and alert scripts:

#****************************************************************
# This is the weekly table and index analyze job for the CBO
#****************************************************************
30 7 1 * * /home/analyze.ksh > /home/analyze.lst
#****************************************************************
# This is the weekly (Monday) object analyze and report for management
#****************************************************************
30 7 * * 1 /home/oracle/obj_stat/get_object_stats.ksh prodb1
00 8 * * 1 /home/obj_stat/run_object_report.ksh prodb1
#****************************************************************
# This is the daily STATSPACK exception report for the DBAs
#****************************************************************
30 7 * * * /home/statspack/statspack_alert.ksh prodsid
#****************************************************************
# This is the daily generic alert report for the DBAs
#****************************************************************
00 7 * * * /home/mon/oracheck.run prodsid > /home/mon/o.lst
#****************************************************************
# This is the daily vmstat collector & report for the DBAs and SAs
#****************************************************************
00 7 * * * /home/vmstat/run_vmstat.ksh > /home/vmstat/r.lst
05 7 * * * /home/vmstat/run_vmstat_alert.ksh prodsid > /home/vmstat/v.lst
09 7 1 * * /home/vmstat/run_vmstat_weekly_alert.ksh prodb1
****************************************************************
# This is the daily iostat collector & report for the DBAs and SAs
#****************************************************************
#00 7 * * * /home/iostat/run_iostat_solaris.ksh > /home/iostat/r.lst
#00 7 * * * /home/iostat/run_iostat.ksh prodsid > /home/iostat/v.lst
#****************************************************************
# This is the every 5 min. trace file alert report for the DBAs
#****************************************************************
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,
5,57,59 * * * * /home/mon/trace_alert.ksh prodsid > /dev/null 2>&1
#****************************************************************
# This code ensures that the daemon to check for buffer busy waits
# is always running.
#****************************************************************
30 7 * * * /home/mon/run_busy.ksh > /dev/null 2>&1

We also need to modify these reports to send the information via e-mail to the appropriate person. The alert reports are designed to spool the output to a known filename, and the DBA just needs to modify the commands that send the e-mail alert to customize the recipients? e-mail addresses.

Let?s look at how this works. The Korn shell code below checks the size of the alert report and mails the report to the DBA if alerts were detected by the script:

var=`cat /tmp/statspack_alert.lst|wc -l`

if [[ $var -gt 1 ]];
   then
   echo
   "**********************************************************************"
   echo "There are alerts"
   cat /tmp/statspack_alert.lst|mailx -s "Statspack Alert" \
   don@oracle.com \
   larry_ellison@us.oracle.com \
   james@us.oracle.com
   echo
   "**********************************************************************"
exit
fi

Next, let?s look at how we can identify and e-mail Oracle trace files to the DBA and developers.

Oracle Trace file Alert Report

This is a great script for instantly notifying the DBA and developers of the presence of trace files. In a production environment, this script can be used to alert the DBA to production aborts, and it is also useful in development environments, where developers can be e-mailed their trace file dumps when a program aborts. This script is generally executed every five minutes.

The trace_alert.ksh script interrogates the Oracle datafile systems to find the locations of all trace and dump files. It then checks these directories and e-mails any trace files to the appropriate staff member. Let?s take a close look at the steps in this script.

Set the Environment

The first part of the script ensures that a valid ORACLE_SID is passed to the script:

#!/bin/ksh

#******************************************************
# Exit if no first parameter $1 is passed to script
#******************************************************
if [ -z "$1" ]
then
echo "Usage: trace_alert.ksh <ORACLE_SID>"
exit 99
fi

#******************************************************
# First, we must set the environment . . . .
#******************************************************
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
ORACLE_BASE=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`
export ORACLE_BASE
export DBA=$ORACLE_BASE/admin;
export DBA
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/mon`
export MON

Get Environment Information

Next, we get the name of the database server and the current date:

#******************************************************
# Get the server name & date for the e-mail message
#******************************************************
SERVER=`uname -a|awk '{print $2}'`

MYDATE=`date +"%m/%d %H:%M"`

#******************************************************
# Remove the old file list
#******************************************************
rm -f /tmp/trace_list.lst
touch /tmp/trace_list.lst

Get the Names of Any Recent Trace or Dump Files

This section issues the UNIX find command to locate any Oracle trace or dump files that were created in the past day:

#******************************************************
# list the full-names of all possible dump files . . . .
#******************************************************
find $DBA/$ORACLE_SID/bdump/*.trc   -mtime -1 -print >> /tmp/trace_list.lst
find $DBA/$ORACLE_SID/udump/*.trc   -mtime -1 -print >> /tmp/trace_list.lst
find $ORACLE_HOME/rdbms/log/*.trc   -mtime -1 -print >> /tmp/trace_list.lst

(3)Exit Immediately if No Files Found

This section exits right away if there are no files to e-mail to the DBA and developers:

#******************************************************
# Exit if there are not any trace files found
#******************************************************
NUM_TRACE=`cat /tmp/trace_list.lst|wc -l`
oracle_num=`expr $NUM_TRACE`
if [ $oracle_num -lt 1 ]
then
exit 0
fi

#echo $NUM_TRACE files found
#cat /tmp/trace_list.lst

E-Mail the Trace Files

This section of the code extracts the first 100 lines of each trace and dump file and e-mails them to the DBA and developer staff.

#******************************************************
# for each trace file found, send DBA an e-mail message
# and move the trace file to the /tmp directory
#******************************************************
cat /tmp/trace_list.lst|while read TRACE_FILE
do

   #***************************************************
   # This gets the short file name at the end of the full path
   #***************************************************
   SHORT_TRACE_FILE_NAME=`echo $TRACE_FILE|awk -F"/" '{ print $NF }'`
   #***************************************************
   # This gets the file location (bdump, udump, log)
   #***************************************************
   DUMP_LOC=`echo $TRACE_FILE|awk -F"/" '{ print $(NF-1) }'`

   #***************************************************
   # send an e-mail to the administrator
   #***************************************************

   head -100 $TRACE_FILE|\
mailx -s "$ORACLE_SID Oracle trace file at $MYDATE."\
      don@remote-dba.net\
      terry@oracle.net\
      tzu@oracle.com

Move the Trace File

The final step is to move the trace or dump file from its current location to the UNIX /tmp directory. This keeps the dump file locations from getting clogged and ensures that the trace file is periodically deleted. This is because most UNIX administrators remove files from the /tmp directory after they are seven days old.

   #***************************************************
   # Move the trace file to the /tmp directory
   # This prevents multiple messages to the developers
   # and allows the script to run every minute
   #***************************************************

cp $TRACE_FILE /tmp/${DUMP_LOC}_${SHORT_TRACE_FILE_NAME}
rm ?f $TRACE_FILED

done

Next, let?s look at a generic alert script that can be used on non-database servers to e-mail alerts when a program on a Web server aborts.