5 Replies Latest reply: Jan 8, 2013 6:51 AM by leocoppens RSS

    Bash shell script to exception when database has been shutdown

    leocoppens
      Hi, I'm quite new in shell scripting. I created the below simple script to do a check on the database, it's a count so it works fine... if the database is there. Now I want to be able to catch if the database is or is not there, what I tried so far didn't work, can anybody please give me a hand with this?

      The shell script file "start_check.sh":
      -----------------------------------------------------------------------------------------------------------------------------
      #!/bin/bash
      # Set environmental variables
      . /home/oracle/env/usrdwh1.env

      TEMP_FILE=rwcnt

      runCheckQuery()
      {
      # Run query and dump result into the TEMP_FILE
      sqlplus -s "/as sysdba" > /tmp/${TEMP_FILE} << EOF
      @/u02/reports/sql/start_check.sql
      EOF
      if [ $? -eq 0 ]
      then err_num=0
      else err_num=1
      fi
      }

      ################ MAIN ####################

      runCheckQuery
      row_count=`cat /tmp/${TEMP_FILE}`

      if [ $err_num -eq 0 ]
      then
      # If no rows were found then send an email alert
      if [ $row_count -eq 0 ]; then
      echo 'No process found - Please investigate' | mailx -s "Daily check ALERT" dba@somedomain.com
      fi
      else
      # There was an error when trying to connect to the db. Need to report it
      echo 'Database connection error - Please investigate - Error Message: (' $row_count ')' | mailx -s "Daily check ALERT (Database connection error)" dba@somedomain.com
      fi

      # Remove the tmp file
      rm /tmp/${TEMP_FILE}
      -----------------------------------------------------------------------------------------------------------------------------


      The sql script file "start_check.sql":
      -----------------------------------------------------------------------------------------------------------------------------
      SET SERVEROUTPUT ON
      SET FEEDBACK OFF
      WHENEVER SQLERROR EXIT;
      DECLARE
      row_count NUMBER;
      BEGIN
      SELECT COUNT(*)
      INTO row_count
      FROM dw_ml_ba ml_ba
      WHERE sys_id = 'CCX'
      AND ml_ex_start_datetime > TRUNC(sysdate);
      DBMS_OUTPUT.PUT_LINE(row_count);
      END;
      /
      EXIT
      -----------------------------------------------------------------------------------------------------------------------------

      Edited by: leocoppens on Jan 4, 2013 4:05 PM
        • 1. Re: Bash shell script to exception when database has been shutdown
          L-MachineGun
          Why re-invent the wheel?
          Use EM dbconsole (or Grid control) to monitor your database.
          EM dbconsole is included with your db.
          :p
          • 2. Re: Bash shell script to exception when database has been shutdown
            leocoppens
            Good idea L, the only thing is that they want it in a shell script run through cron as most things are already there and they don't want to start splitting. Still I will look into that.
            Thanks
            Leo
            • 4. Re: Bash shell script to exception when database has been shutdown
              L-MachineGun
              -
              There may be a better, but here is a shell script that works:
              #!/bin/ksh
              ################################################################
              # db_check.sh
              ################################################################
              #
              # Script used to check if one or all of the databases on 
              # one server are available.
              #
              ################################################################
              # Parameter Description
              # --------- -----------
              sid=$1    # Database SID or Keyword 'all'
              
              ################################################################
              function check1db
              {
              sid=$1    # Database SID
              
              ORAENV_ASK=NO
              . /usr/local/bin/oraenv "$sid"
              ORAENV_ASK=YES
              
              if [ $(ps -ef|grep "ora_smon_$sid"|grep -v grep|wc -l) -eq 0 ]
              then
                echo "%-Error, Database $sid is NOT available - Not started\n" >>${CHKLOG}
                return 1
              fi
              dbok=$(\
              sqlplus -s / <<!
              !
              )
              if [[ $(echo $dbok|cut -d' ' -f1 ) == 'ERROR:' ]]
              then
                echo "%-Error, Database $sid is NOT available - Started with errors\n" >>${CHKLOG}
                return 1
              else  
                echo "%-Info, Database $sid is available\n" >>${CHKLOG}
              fi
              return 0
              } # end function check1db
              ################################################################
              # Set some environment variables:
              ORACFG=/etc               # Location of oratab
              ORALOG=$HOME/logs         # Location for result log
              EMAIL='dba@mydomain.com'  # E-mail to send alert
              
              sid=${sid:-'all'}
              
              echo "$0 Job started at: `date` "
              
              BDATE=$(date +%y%m%d)
              export CHKLOG=$ORALOG/db_check_${sid}_${BDATE}.log
              echo "$0 on `date`" >$CHKLOG
              if [ "$sid" = "all" ]
              then
                i=0
                stat=0
                cat $ORACFG/oratab | while read LINE
                do
                  case $LINE in
                   \#*)            ;;      #comment-line in oratab
                     *)
                      sid=`echo $LINE | awk -F: '{print $1}'`
                      check1db "$sid"
                      stat1=$?
                      ((stat += $stat1)) # Combine the Status of All Calls
                      ((i = $i + 1))     # Count Number of Databases Checked
                      ;;
                  esac
                done
                ((j = $i - $stat))  # Count Number of Databases Available
                echo "\n%-Info, `date +%c`,\n\tTotal databases checked = $i,\n\tAvailable = $j, Not available = $stat\n" >>${CHKLOG}
              else
                check1db $sid
                stat=$?
              fi
              #
              #-----------------------------------------------------------
              # Beep operator if database down.
              if [ ${stat} -ne 0 ]
              then
                SUBJ="Database(s) alert."
                mailx -s"$SUBJ" $EMAIL <$CHKLOG
              fi
              
              echo "$0 Job stoped at: `date` "
              exit $stat
              :p
              • 5. Re: Bash shell script to exception when database has been shutdown
                leocoppens
                Jeezz thank you, I've been away the last couple of days so sorry for the delay answering. Will give this a try
                Thx!