DB and Listener availability monitoring script

AskDBA.org Forum » DBA - Wiki

DB and Listener availability monitoring script

(1 post)

Average Rating For This Topic:

Rate This Topic Yourself:

Started 4 months ago by Amit Bansal

Tags:

monitoring

Amit Bansal

Oracle DBA
Joined: Jun '08
Posts: 44

offline

Reputation:57

Hi,

Find below a simple script to monitor db and listener. It uses utl_mail procedure to connect to a mailserver db and send a mail in case of failure.
You can also use mailx function to send mail.
I have hard-coded password , but you can also use Oracle wallet to store password. Refer to my blog post here for details

You need to pass ORACLE_SID as parameter. So to schedule a cron to check every 10 mins, you need to specify like below

00,10,20,30,40,50 * * * * sh /home/oracle/monitor/db_check.sh orcl

Find below the script. Any suggestions/improvements are welcome


###################################################################
## Check Database Availability
###################################################################
##########Env Variables #############
export DBA_MAIL1='amit.bansal@askdba.org'
export DBA_MAIL2='saurabh.sood@askdba.org'
export ORACLE_HOME=/oracle/ora_db
export TNS_ADMIN=$ORACLE_HOME/network/admin
export BASE_DIR=/home/oracle/monitor
export ORACLE_SID=$1
export LOG_FILE=$BASE_DIR/$ORACLE_SID.log
export PATH=$ORACLE_HOME/bin:$PATH
export machinename=hostname
###################################
cd $BASE_DIR
echo "date   " > $LOG_FILE
echo  "Oracle Database(s) Status hostname :\n" >>  $LOG_FILE
db_cnt=ps -ef|grep pmon|grep $ORACLE_SID|wc -l
if [ $db_cnt -gt 0 ]; then
        echo "$ORACLE_SID is Up" >> $LOG_FILE
else
        echo "$ORACLE_SID is Down" >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus -s "system@maildb/oracle123" <<EOF  >> $LOG_FILE
        alter session SET smtp_out_server = 'mailserv:100';
        BEGIN
        UTL_MAIL.send(
 sender => 'webmaster@askdba.org'
 ,recipients => '${DBA_MAIL1}'
,cc => '${DBA_MAIL2}'
 ,subject => '${ORACLE_SID} is down on ${machinename}'
 ,message => '${ORACLE_SID} is Down on ${machinename}'
 ,mime_type => 'text/plain; charset=us-ascii'
 ,priority => 3);
 END;
/
exit
EOF
fi

lsn_cnt=ps -ef|grep tnslsnr|grep oracle|grep -v grep|wc -l
if [ $lsn_cnt -gt 0 ]; then
        echo "Listener is up" >>$LOG_FILE
else
echo "Listener is down" >>$LOG_FILE
$ORACLE_HOME/bin/sqlplus -s "system@maildb/oracle123" <<EOF  >> $LOG_FILE
        alter session SET smtp_out_server = 'mailserv:100';
        BEGIN
         UTL_MAIL.send(
 sender => 'webmaster@askdba.org'
,recipients => '${DBA_MAIL1}'
,cc => '${DBA_MAIL2}'
 ,subject => 'Listener is down on ${machinename}'
 ,message => 'Listener is Down on ${machinename}'
 ,mime_type => 'text; charset=us-ascii'
 ,priority => 3);
 END;
/
exit
EOF
fi

AskDBA.org Forum

Subscribe to aSkDba.org Forum Posts

AskDBA.org Forum » DBA - Wiki

DB and Listener availability monitoring script

Reply