#!/usr/bin/ksh 


if [ ! -z "$1" ]
then
ORASID=$1
else
ORASID=`cat /etc/oratab|grep -v "^#" |cut -d":" -f1`
fi

. ~/.bash_profile
HOST=`hostname`

for SID in $ORASID 
do
export ORACLE_SID=$SID
export ORACLE_HOME=`dbhome $SID`
export ORACLE_BASE=`orabase $SID`

$ORACLE_HOME/bin/sqlplus -s -L '/ as sysdba'<<EOF
set linesize 900
set echo off
set pages 0
set heading off
set feed off
set term off
set verify off
spool alert_monitor.lst
select 'Error message:'||originating_timestamp, detailed_location,problem_key,message_text from sys.X\$DBGALERTEXT where message_text like '%ORA-%' and originating_timestamp > trunc(sysdate -1/24) ;
spool off;
EOF

STATUS=$?
CNT=$(cat alert_monitor.lst |grep ^'Error message:'|wc -l)

if [ $CNT -ne 0 ] && [ $STATUS -eq 0 ]; then
mailx -s "$SID@$(hostname) ORA Errors in Alert Log" Henry.Shao@xxx.com < alert_monitor.lst
fi
done