This discussion is archived
5 Replies Latest reply: Jan 8, 2013 4:51 AM by leocoppens RSS

Bash shell script to exception when database has been shutdown

leocoppens Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    -
    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 Newbie
    Currently Being Moderated
    Jeezz thank you, I've been away the last couple of days so sorry for the delay answering. Will give this a try
    Thx!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points