5 Replies Latest reply on Jan 8, 2013 12:51 PM by leocoppens

    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
          1 person found this helpful
          • 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
            • 3. Re: Bash shell script to exception when database has been shutdown
              Mark Malakanov (user11181920)
              WHENEVER SQLERROR EXIT *1*
              1 person found this helpful
              • 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
                1 person found this helpful
                • 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!