Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


    Shell script to monitor oracle database availabilty from a remote server

    Share

    Admin
    Admin

    Posts : 17
    Join date : 2010-02-08

    Shell script to monitor oracle database availabilty from a remote server

    Post  Admin on 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

      Current date/time is Thu Nov 23, 2017 10:49 pm