Unix script to monitor the alert log for errors using x$dbgalertext

A simple script to monitor the alert log for errors via x$dbgalertext rather than the text error log. This script assumes a four node RAC. The script runs on each of the four nodes via CRON. The instance name is passed in as a parameter.

Call with
Check_alert_log.ksh instance_name1/2/3/4

The script will mail out the last 24 hours of the alert log at the 15:00 hour. All other executions will only look back 90 minutes and mail only if an error is found.

#!/bin/ksh
#
#-----------------------------------------------------------------------------------------
#
# Name       : Monitor_alert_log.ksh
# Purpose    : check the alert log
#
#-----------------------------------------------------------------------------------------
export PATH=$PATH:Oracle_Home/bin
export ORACLE_BASE=/opt/oracle/product
export ORACLE_HOME=Oracle_Home

function run_sql
{
	echo "Entering function run_sql `date`"	
	sqlplus -s / as sysdba << EOF > $ReportFile
	
	  set pagesize 100
	  set linesize 200
	  set tab off
	  set wrap off
	            
        col mt              for a16 head 'Time (MT)'
        col gmt             for a16 head 'Time (GMT)'
        col problem_key     for a10 head 'Error'
        col message_text    for a40 head 'Message Text' wrap
		select        TO_CHAR(new_time(originating_timestamp,'GMT','MDT'), 'MM-DD-YYYY HH24:MI') as MT
		             ,TO_CHAR(originating_timestamp,'MM-DD-YYYY HH24:MI') as GMT
		             ,problem_key
		             ,MESSAGE_TEXT
		from          x\$dbgalertext a
		where         originating_timestamp > sysdate - interval '$look_back' minute
		  and         MESSAGE_TEXT LIKE '%ORA-%'
		order by      originating_timestamp desc
		;
    exit
EOF

	echo "Exiting  function run_sql `date`"		
}


function decide_to_email
{
	egrep -i "no rows selected" $ReportFile > /dev/null
	if  [ $? -eq 0 ]
	then
	    if  [ $mandatory_mail == "Y" ]
	    then
	        echo 'Mailing out report based on time'
	    	mail -s "Alert Log monitoring $ORACLE_SID" "Email_id" < $ReportFile
	    fi
    else
	        echo 'Mailing out report based on errors found'
	   		mail -s "Alert Log monitoring $ORACLE_SID" "Email_id" < $ReportFile
	fi
}

#
## start of script
#
sid_parm=$1
export ORACLE_SID=$sid_parm
case $ORACLE_SID in
	 instance_name1)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name1/database_name_monitor_alert_log.report
 		;;
	 instance_name2)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name2/database_name_monitor_alert_log.report
 		;;
	 instance_name3)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name3/database_name_monitor_alert_log.report
 		;;
	 instance_name4)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name4/database_name_monitor_alert_log.report
 		;;
esac

echo 'Oracle base is : ' $ORACLE_BASE
echo 'Oracle home is : ' $ORACLE_HOME
echo 'Oracle SID is  : ' $ORACLE_SID
echo 'Path is        : ' $PATH
echo 'Report file is : ' $ReportFile

#
## If the hour is "15" then look back 24 hours and mail out unconditionally 
# 
TimeOfDay=`date +"%H"` if  [ $TimeOfDay == 15 ] 
	then
		mandatory_mail="Y"
		look_back=1440
	else
		mandatory_mail="N"
		look_back=90
fi		
echo 'Mandatory mail is set to   : ' $mandatory_mail
echo 'Look back period is set to : ' $look_back

#
## Call routine to run SQLs
#
echo 'Script started at ' `date`
run_sql

#
## Decide if an email is required or not # decide_to_email

#
## End of script
#
echo 'Script ended   at ' `date`