Admin- Admin
- Posts : 17
Join date : 2010-02-08
Admin Fri Nov 11, 2011 6:40 am
- Code:
#!/bin/ksh
##############################################################################
# A simple script to monitor oracle database availabilty from a remote server.
# Script name : monitor_database.ksh
# Usage monitor_database.ksh <db name>
##############################################################################
if [ $# -ne 1 ]
then
echo "Usage : "`basename $0`" <INSTANCE_SID>"
exit 1
fi
log()
{
echo "$(date '+%d/%m/%Y %H:%M:%S') $1" >>$LOGFILE
}
export ORA_MSG="ORA-|Error|error|ERROR|WARNING|Warning|warning|fail|Fail|FAIL|cannot|Cannot|CANNOT"
DB_SID=$1; export DB_SID
# Source the oracle environmnet.
. ~/.profile
HLOGFILE=/tmp/monitor_database_hist_${DB_SID}.log ; export HLOGFILE
LOGFILE=/tmp/monitor_database_${DB_SID}.log ; export LOGFILE
cat /dev/null > $LOGFILE
#Check if previous run has not completed
prev_run=$(ps -aef | grep "sqlplus -smonitor_database_${DB_SID}" |grep -v grep|wc -l )
if [ $prev_run -gt 0 ]; then
echo "There are hanging SQL*Plus sessions of $(basename $0) at `hostname' for instance $DB_SID." > $LOGFILE
mailx -s "ACTION REQUIRED : There are hanging SQL*Plus from $(basename $0) at `hostname` for instance $DB_SID." youremailid@yourorg.com < $LOGFILE
exit 1
fi
########################
# START OF MAIN SCRIPT #
########################
(
MONUSER=dbsnmp; export MONUSER
MONPWD=dbsnmppwd; export MONPWD
ConnectString="${MONUSER}/${MONPWD}@${DB_SID}"
# Try connect to database in background ( & ).
DBOK=""
DBOK=$(
print "
connect $ConnectString
set head off
set feedback off
set echo off
set pagesize 0
select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||' '||INSTANCE_NAME||' - Connected' from v\$instance;
" | $ORACLE_HOME/bin/sqlplus -smonitor_database_${DB_SID} /nolog >> ${LOGFILE}) 2>&1 &
# Sleep 30 seconds and then check if the connect attempt is not completed in 30 Secs.
sleep 30
still_running=$(ps -aef | grep "sqlplus -smonitor_database_${DB_SID}" |grep -v grep|wc -l )
if [ $still_running -gt 0 ]; then
log "ERROR : Connection not established after 30 secs."
fi
) > $LOGFILE 2>&1
cat $LOGFILE >> $HLOGFILE
# Check error in log file and send email
errorcnt=`egrep "${ORA_MSG}" $LOGFILE |wc -l`
if [ $errorcnt -gt 0 ]
then
mailx -s "ACTION REQUIRED : $(basename $0) for instance $DB_SID Failed. Please check the database " youremailid@yourorg.com < $LOGFILE
fi