AskDBA.org Forum Forum for Oracle DBA's

About This Topic

• Posted 1 year ago
• By Amit Bansal
• Latest reply from Amit Bansal
• This topic is not a support question
• RSS feed for this topic

Tags

• monitoring

1. 

   Amit Bansal
   

   Oracle DBA
   Joined: Jun '08
   Posts: 73

   offline

   Reputation:96

    

   
   Posted 1 year ago
   #

   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
   

   Cheers
   Amit
   http://askdba.org/weblog/
2. 

   Anonymous
   

   
   
   Posts: 3

   offline

   Reputation:1

    

   
   Posted 1 year ago
   #

   Nice thing shared but i cant recognize the instruction of"Oracle wallet" to store password will you make this clear to me that how to store password??