DB and Listener availability monitoring script

Tagged: 

  • This topic is empty.
Viewing 0 reply threads
  • Author
    Posts
    • #1918
      Amit Bansal
      Keymaster

      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

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

      Find below the script. Any suggestions/improvements are welcome

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

Viewing 0 reply threads
  • You must be logged in to reply to this topic.