2 Replies Latest reply: Jul 3, 2014 3:28 AM by spajdy RSS

    Checking alertlog file with scheduler

    Rosh_dba


      Hi all,

      I have a oracle 11gR2 db in Solaris 10 machine. I am trying to schedule a job with dbms_scheduler to check alertlog file every 5 min and email errors .The problem is that when I run the scripts at OS it sends out email correctly. If I put a dummy SID, the scheduler sends an email that db is not running but with correct SID the scheduler doesn't do anything .here are the steps I followed, please let me know what I am missing .thanks

       

      SQL> BEGIN

        2  DBMS_SCHEDULER.CREATE_JOB  (

        3  JOB_NAME   =>'DB_HEALTH_STATUS',

        4  JOB_TYPE   =>'EXECUTABLE',

        5  JOB_ACTION =>'/oracle/scripts/check_alert.sh',

        6  START_date =>SYSTIMESTAMP,

        7  REPEAT_INTERVAL =>'freq=minutely; interval=5; bysecond=0;',

        8  END_DATE => NULL,

        9  ENABLED  =>TRUE );

      10  END ;

      11  /

       

      Here's the script which is running

      #!/usr/bin/bash
      ###############################################################
      # check_alert.sh
      #
      # check Oracle alert log file and send out e-mail if error found
      #
      ################################################################
      
      cd /oracle/scripts
      # ensure environment variables set
      ORACLE_SID=db1
      MACHINE=`hostname`
      PATH=$ORACLE_HOME/bin:$PATH
      # check if the database is running, if not exit
      DB_STATUS=$(ps -ef | grep -v grep | grep -w ora_smon_${ORACLE_SID} ) 
      if [ "${DB_STATUS}" = "" ]; then 
          echo "Warning: Database ${ORACLE_SID} is not running!" |
          mailx -s "Warning: Database ${ORACLE_SID} on ${MACHINE} is not running!"  'email@email.com'  
          exit 1
      else 
          echo "Database ${ORACLE_SID} on ${MACHINE} ...  is running  " 
      fi
      #############################
      #Search the alert log, and email all of the errors
      #move the alert_log to a backup copy
      #cat the existing alert_log onto the backup copy
      ALERT_DIR=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
      ORIG_ALERT_LOG=${ALERT_DIR}/alert_${ORACLE_SID}.log
      currdate=`/bin/date '+%m_%d_%Y_%H%M%S'`
      NEW_ALERT_LOG=${ORIG_ALERT_LOG}.monitored.${currdate}
      TEMP_ALERT_LOG=${ORIG_ALERT_LOG}.temp
      cat ${ORIG_ALERT_LOG} | awk -f /oracle/scripts/check_alert.awk > /tmp/${ORACLE_SID}_check_monitor_log.log
      if [ -s /tmp/${ORACLE_SID}_check_monitor_log.log ]
         then 
           echo "Found errors in sid ${ORACLE_SID}, mailed errors"
           echo "The following errors were found in the alert log for ${ORACLE_SID}" > /tmp/${ORACLE_SID}_check_monitor_log.mail
           echo "Alert log was copied into ${NEW_ALERT_LOG}" >> /tmp/${ORACLE_SID}_check_monitor_log.mail
           echo " "
           date >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
           echo "----------------------" >>/tmp/${ORACLE_SID}_check_monitor_log.mail
           echo " "
           echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
           echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
           cat /tmp/${ORACLE_SID}_check_monitor_log.log >>  /tmp/${ORACLE_SID}_check_monitor_log.mail
      
      mailx -s "on ${MACHINE}, MONITOR of Alert Log for ${ORACLE_SID} found errors" 'email@email.com' < /tmp/${ORACLE_SID}_check_monitor_log.mail
      
           mv ${ORIG_ALERT_LOG} ${TEMP_ALERT_LOG}
           cat ${TEMP_ALERT_LOG} >> ${NEW_ALERT_LOG}
           touch ${ORIG_ALERT_LOG}
            rm /tmp/${ORACLE_SID}_check_monitor_log.log 
            rm /tmp/${ORACLE_SID}_check_monitor_log.mail 
      exit
      fi;
           rm /tmp/${ORACLE_SID}_check_monitor_log.log > /dev/null
           rm /tmp/${ORACLE_SID}_check_monitor_log.mail > /dev/null