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`