Wednesday, December 18, 2013

Shell script to send alert for database status check

#! /bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export HOST_NAME=`hostname`
export MAIL_FILE=/home/oracle/dba_scripts/dbstatcheck.log
export MAIL_ID_LIST="database-team-vsoft@vsoftcorp.com"
export ALERT_LOG=/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
dbchk=`sqlplus -s / as sysdba <set feed off lines 1000 pages 200 tab off space 1 head off FLUSH OFF TRIMSPOOL ON echo off verify off termout off
select status from v\\$instance;
exit
EOF`
dbchk1=`echo $dbchk|sed s/^$//`
echo $dbchk1
if [ "$dbchk1" != "OPEN" ]
then
echo "database is not open"
echo "Sending recent errors in alert log : ${ALERT_LOG} " > $MAIL_FILE
echo "############################################################" >> $MAIL_FILE
grep -A 3 -B 3 "ORA-" ${ALERT_LOG}|tail -n 20 >> $MAIL_FILE

cat $MAIL_FILE|mail -s "### Severity High : DATABASE IS DOWN : ${HOST_NAME}:${ORACLE_SID} ###" "${MAIL_ID_LIST}"
fi

No comments: