#!/bin/ksh # dbalert.sh # Script to verify Oracle Database health # Author : Biju Thomas # Created : 07/29/1997 # # # This script does the following: # # Reads all instance names from /etc/oratab file and performs the # operations on all instances: # # 01. Gives an error if the specified instance is unavailable # 02. Alerts if listener is not up # 03. Checks the alert log file for any errors # Rename current alert log file (change "log" extension to "current date") # 04. Check if any of the Oracle DB jobs are broken due to errors # 05. Check enough free space is available in all tablespaces # 06. Check if any object is growing with many extents # 07. Check if any object is sitting on the maximum extent # 08. Check for any objects/users created in the SYSTEM tablespace # 09. Check for disabled triggers # 10. Check for invalid objects # 11. Alert if any object is created/changed under SYS ownership # # # Read /etc/oratab file to get the instance names on this machine and # Oracle Home directory # # cat /etc/oratab | while read LINE do case $LINE in \#*) ;; #comment-line in oratab *) # Proceed only if third field is 'Y'. if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -` if [ "$ORACLE_SID" = '*' ] ; then ORACLE_SID="" fi export ORACLE_SID ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -` export ORACLE_HOME # # # Perform the test using the ORACLE_SID # # werr=0 wdate=`date '+%m%d'` wlogfile=/tmp/dbalert_log.$ORACLE_SID werrfile=/tmp/dbalert_err.$ORACLE_SID # # Initialize message files # echo "**********************************************************************" > $wlogfile echo "**********************************************************************" > $werrfile # # Verfiy if required Oracle Environment Variables are Available # if test `env | grep ORACLE_SID | wc -l` -ne 1 then werr=1 echo "Environment Variable ORACLE_SID Not Available \n" >> $werrfile echo "**********************************************************************" >> $werrfile fi if test `env | grep ORACLE_HOME | wc -l` -ne 1 then werr=1 echo "Environment Variable ORACLE_HOME Not Available \n" >> $werrfile echo "**********************************************************************" >> $werrfile fi # # If Environment variables not available discontinue process and inform DBAOC # if test $werr -eq 1 then echo "Date : "`date '+%m/%d/%y %X %A '` >> $werrfile echo "Database : "$ORACLE_SID >> $werrfile echo "Server : "`uname -n` >> $werrfile echo "**********************************************************************" >> $werrfile mailx -s "Errors found in routine DB checkup" "tbiju@hotmail.com" > /dev/null < $werrfile continue fi # # 01. Check for instance availability # if test -f $ORACLE_HOME/dbs/sgadef$ORACLE_SID.dbf then echo "Oracle up and running" >> $wlogfile wostatus=0 else echo "Oracle Not Available $ORACLE_SID" >> $werrfile echo "**********************************************************************" >> $werrfile wostatus=1 fi # # 03. Check the alert log file for any errors # Rename current alert log file (change "log" extension to "current date") # walertfile=/ora_dump/$ORACLE_SID/bdump/alert_$ORACLE_SID.log if test -f $walertfile then if test `grep "ORA-" $walertfile | wc -l` -ne 0 then echo "Following errors written to the Alert log file. Please verify" >> $werrfile grep "ORA-" $walertfile >> $werrfile echo "**********************************************************************" >> $werrfile else echo "No Errors in the Alert log file" >> $wlogfile fi # # Copy the alert log file to a file with date # cat $walertfile >> /ora_dump/$ORACLE_SID/bdump/alert_$ORACLE_SID.$wdate rm $walertfile touch $walertfile # fi # # # Do the following only if Oracle is available and running # if test $wostatus -eq 0 then $ORACLE_HOME/bin/sqlplus -s / \@dbalert.sql if test `cat /tmp/dbaoc.lis | wc -l` -ne 0 then cat /tmp/dbaoc.lis >> $werrfile fi fi # # # # Check if errors encountered, if yes send mail to DBAOC # if test `cat $werrfile | wc -l` -ne 1 then echo "**********************************************************************" >> $werrfile echo "Date : "`date '+%m/%d/%y %X %A '` >> $werrfile echo "Database : "$ORACLE_SID >> $werrfile echo "Server : "`uname -n` >> $werrfile echo "**********************************************************************" >> $werrfile mailx -s "Errors found in routine DB checkup - $ORACLE_SID" "tbiju@hotmail.com" > /dev/null < $werrfile continue else echo "Successful completion of routine checkup" >> $wlogfile echo "No Errors / Alerts Encountered" >> $wlogfile echo "**********************************************************************" >> $wlogfile continue fi fi esac done # # End of Script