#!/bin/ksh

#

# Crontab-Based Error Paging Script for Oracle Database Errors

# ------------------------------------------------------------

# This script is used to send an alpha-text page to the system admin

# when an error is detected in the oracle alert_sid.log file.

#

# Its best to run this script every 5 to 10 minutes, especially if

# the system that is under support is critical or production based.

#

# Variables

#

# Edit these for your alpha-numeric pager and e-mail address

#

# Important: SET xx FOR THE INSTANCE ON WHICH YOU WILL RUN THIS SCRIPT !!

#

xx=$1

SCRIPT_HOME=/home/orapf"$xx"/scripts

PAGE_CONFIG="$SCRIPT_HOME"/paging/config

PAGE_ALERTS="$SCRIPT_HOME"/paging/pages

PAGE_DATA="$SCRIPT_HOME"/paging/rawdata

PAGE_LOG="$SCRIPT_HOME"/paging/log

PAGE_TEMP="$SCRIPT_HOME"/paging/temp

DBADMIN=/mnt/pfcs/pfcs"$xx"/pfcs"$xx"db/8.1.7/admin/pfcs"$xx"

PAGER1="3142450893@archwireless.net"

CONTACT2="gs1114@stl.rural.usda.gov"

CONTACT3="lb1088@stl.rural.usda.gov"

CONTACT4="lm1076@stl.rural.usda.gov"

CONTACT5="Mitchell.Clem@unisys.com"

FDATE=`date "+%c"`

ERRLOG="$PAGE_LOG"/oracle_errors.log # Edit this for your log holding directory

ext=`date +%y%m%d%H%M`

let i=1

#

# Oracle Alert Log Error Paging

#

# Detect oracle errors in alert log and create mfx [x=1,2,3,...] (messagefile) paging files

#

/home/orapf"$xx"/scripts/alert_log_check_pr_v7.sh $1 $ext

#/home/orapf"$xx"/scripts/alert_log_check_v3.sh $1 $ext

#

# Process the alert log so that subsequent scans will not trap the same errors

#

ls -lrt "$PAGE_TEMP"/mf[0-9]* | wc -l | read anyhits

if [ $anyhits -gt 0 ]

then

wc -l "$DBADMIN"/bdump/alert_pfcs"$xx".log | cut -f1 -d' ' | read trim

cp "$DBADMIN"/bdump/alert_pfcs"$xx".log "$DBADMIN"/bdump/alert_pfcs"$xx"."$ext"

if [ $? -eq 0 ]

then

sed "$i","$trim"d "$DBADMIN"/bdump/alert_pfcs"$xx".log > "$DBADMIN"/bdump/alert_pfcs"$xx".log

else

mailx -m -s "Alert Log Trim Problem - DBA please check to avoid duplicate pages" $CONTACT $CONTACT2 < "$PAGE_TEMP"/optdisk_error

fi

#

# Process the mfx paging files and send them to pagers/email/cell (any device with an email address)

#

ls -l "$PAGE_TEMP"/mf[0-9]* | wc -l | read error_count

let i=1

while [ $i -le $error_count ]

do

more "$PAGE_TEMP"/mf"$i" | head -2 | tail -1 | cut -f2 -d':' | cut -f1 -d' ' | read error_number

more "$PAGE_TEMP"/mf"$i" | head -3 | tail -1 | cut -f2 -d':' | sed -e 's/^[ \t]*//' | read server

more "$PAGE_TEMP"/mf"$i" | head -3 | tail -1 | cut -f2 -d'_' | cut -f1 -d'.' | read instance

more "$PAGE_TEMP"/mf"$i" | head -2 | tail -1 | cut -f3 -d':' | sed -e 's/^[ \t]*//' | read error

echo $error_number'.'$instance | read subject

echo $error > "$PAGE_TEMP"/optdisk_error

echo $FDATE' Found on '$server'.'$instance' during scan:' >> $ERRLOG

echo 'Oracle '$error_number':'$error >> $ERRLOG

echo 'Check in '"$PAGE_ALERTS"'/sent.'"$ext"'.mf'"$i"' for the alert_pfcs'$xx'.'"$ext"' line numbers' >> $ERRLOG

mailx -m -s "$subject" $PAGER1 < "$PAGE_TEMP"/optdisk_error

mailx -m -s "$subject" $CONTACT2 $CONTACT3 $CONTACT4 $CONTACT5 < "$PAGE_TEMP"/mf"$i"

rm "$PAGE_TEMP"/optdisk_error

mv "$PAGE_TEMP"/mf"$i" "$PAGE_ALERTS"/sent."$ext".mf"$i"

echo "$PAGE_ALERTS"/sent."$ext".mf"$i" >> "$PAGE_ALERTS"/sent."$ext".mf"$i"

let i=$i+1

done

fi

exit