DB and Listener availability monitoring script

Home Forums DBA – Wiki DB and Listener availability monitoring script

Tagged: 

This topic contains 0 replies, has 0 voices, and was last updated by  Amit Bansal 7 years, 6 months ago.

  • 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 />
    

You must be logged in to reply to this topic.