To generate an email if more than 10 archives are generated in an Hour

SDATE=`date +”%d%b%y %H:%M”`;
sqlplus -s ‘/ as sysdba’ @/home/oracle/COOL/archcounthr.sql > /home/oracle/COOL/archcounthr.log;
cnt=`cat /home/oracle/COOL/archcounthr.log`
if [ $cnt -gt 10 ]
then
echo “$cnt Archives generated in QTYDB at $SDATE” | mailx -s “QTYDB:Archives Generating >10” dba@xyz.com
fi
exit;


 SQL File
=======
 archcounthr.sql


set head off;
set feed off;
select to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),to_char(sysdate,’HH’),1,0)),’99’) “HH24”
from v$log_history
where trunc(first_time)=trunc(sysdate)
group by to_char(first_time,’YYYY-MM-DD’)
order by to_char(first_time,’YYYY-MM-DD’) desc;
exit;
Recent Posts