alert - liamlamth/blog GitHub Wiki

if [ $# -ne 1 ];then
	echo "Usage: $(basename $0) <SID>"
	exit
fi

export ORACLE_SID=${1};

(
date "+%Y-%m-%d %H:%M:%S"

#### check if Oracle is running
if [ $(ps -fu oracle | grep -c "pmon_${ORACLE_SID}" | grep -vc grep) -gt 0 ] ; then
	export ORAENV_ASK=NO;
	echo "-->${ORACLE_SID} is running"

	alertLog=/u01/app/oracle/diag/rdbms/${ORACLE_SID,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log;
	alertLine=$(grep -ai "ORA-\|corrupt\|deadlock" ${alertLog})
	
	### check if the alert log contains ORA- errors
	errCtORA=$(echo "${alertLine}" | grep ORA- | grep -v "ORA-0$\|ORA-30$\|ORA-31$\|ORA-235 \|ORA-609$\|ORA-01013\|ORA-3136")
	if [ $(echo "${errCtORA}" | wc -l) = 0 ] ; then
		echo "-->${ORACLE_SID} alert log ok"
	else
		echo "${errCtORA}" | mutt -s "*** ERROR: ${ORACLE_SID} alerts on $(date +%Y-%m-%d) ***" db_adm
		echo "-->${ORACLE_SID} has alert log error"
	fi

	### check if the alert log contains Corruption error
	errCtBlock=$(echo "${alertLine}" | grep -i corrupt)
	if [ $(echo "${errCtBlock}" | wc -l) = 0 ] ; then
		echo "-->${ORACLE_SID} alert log ok"
	else
		echo "${errCtBlock}" | mutt -s "*** ERROR: ${ORACLE_SID} alerts (block corruption) on $(date +%Y-%m-%d) ***" db_adm
		echo "-->${ORACLE_SID} has alert log error"
	fi

	### check if the alert log contains Deadlock error
	errCtDL=$(echo "${alertLine}" | grep -i lock)
	if [ $(echo "${errCtDL}" | grep -ci "deadlock") = 0 ] ; then
		echo "-->${ORACLE_SID} alert log ok"
	else
		echo "${errCtDL}" | mutt -s "*** ERROR: ${ORACLE_SID} alerts (deadlck) on $(date +%Y-%m-%d) ***" db_adm
		echo "-->${ORACLE_SID} has alert log error"
	fi
	
else
	echo '.'| mutt -s "*** ERROR: Oracle ${ORACLE_SID} is not running on $(date +%Y-%m-%d) ***"   db_adm
	echo "-->Oracle ${ORACLE_SID} not running"
fi

) >> /log/checkOracle_${ORACLE_SID}.log 2>&1