Script to monitor ORA errors in alert log

=> Below script grep's for ORA- errors from alert log and sends an email alert if new ORA- error is recorded in alert log file.

=> Modify the highlighted contents in the script as necessary

=> Update the script to ignore certain ORA- errors as per the requirement

=> Provide correct alert log location in the script 

=> Update the directory structure for script and log location as per the environment

=> create an error counter before scheduling the script and add 0 to it using below command.

# echo 0 > /home/oracle/scripts/logs/alertlog_error_counter

=> Script alert_log_monitor.sh

#!/bin/bash
###############################################################################
# FILE NAME               : alert_log_monitor.sh
# USAGE                   : ./alert_log_monitor.sh
# LANGUAGE                : Bash
# RESPONSIBILITY          : DBA
# PARAMETERS              : NONE
#
# REQUIREMENTS
#
# DESCRIPTION
#
#
# MAINTENANCE HISTORY
# Issue #   DATE          AUTHOR                        DESCRIPTION
# --------  -----------   ---------------------------   -----------------------
# 1.                   
#
################################################################################

#define variables

ALERTLOGFILE=/u01/app/oracle/diag/rdbms/db_unique_name/sid/trace/alert_PRODCDB1.log
vDate=$(date +"%d-%b-%Y-%H-%M")
run_counter=`cat /home/oracle/scripts/logs/alertlog_error_counter`
vLogFile="/tmp/alertlog_error_check$vDate.txt"
vHostName=`hostname`

error_counter=`cat $ALERTLOGFILE | grep ORA- | egrep -v 'ORA-609|ORA-3136|ORA-30687|KKQTSETOP' | wc -l`

if [ $error_counter -eq 0 ]; then
echo -e "Previous Error count : $run_counter \n" >> $vLogFile
echo -e "Current Error count : $error_counter \n" >> $vLogFile
echo -e "Resetting Error count in run counter..........\n" >> $vLogFile
echo $error_counter > /home/oracle/scripts/logs/alertlog_error_counter
echo -e "Error count reset to $error_counter\n" >> $vLogFile
fi

if [ $error_counter -ne $run_counter -a $error_counter -ne 0 ]; then
echo -e "Previous Error count : $run_counter \n" >> $vLogFile
echo -e "Current Error count : $error_counter \n" >> $vLogFile
echo -e "Resetting Error count in run counter..........\n" >> $vLogFile
echo $error_counter > /home/oracle/scripts/logs/alertlog_error_counter
echo -e "Error count reset to $error_counter\n" >> $vLogFile
echo -e "Latest Errors in alert Log File : \n" >> $vLogFile
error_1=`cat $ALERTLOGFILE | grep ORA- | egrep -v 'ORA-609|ORA-3136|ORA-30687|KKQTSETOP' | tail -1`
echo -e "$error_1\n" >> $vLogFile
echo " " >> $vLogFile

#Send Email Here 

mailx -r "myemail@mysmtpserver.com" -s "$(echo -e "$vHostName alert log error $vDate")" -S smtp="smtp.office365.com:587" -S smtp-use-starttls -S smtp-auth=login -S smtp-auth-user="myemail@mysmtpserver.com" -S smtp-auth-password='email_account_password' -S ssl-verify=ignore -S nss-config-dir="/etc/pki/nssdb/" -c "myemail@mydomain.com" "myemail@mydomain.com" < $vLogFile

else
echo -e "No Errors in alert Log \n" >> $vLogFile
echo -e "Previous Error count : $run_counter \n" >> $vLogFile
echo -e "Current Error count : $error_counter \n" >> $vLogFile
fi

exit;

=> Schedule the script in crontab to run every 10 minutes 

# monitor ORA- errors in alert log
*/10 * * * * /home/oracle/scripts/mon_scripts/alert_log_monitor.sh