1 Reply Latest reply: Mar 6, 2013 5:51 AM by 946212 RSS

    OEM - User defined Metric for DG log gap detection - SQL error

    946212
      Hi,
      We have Windows 2008 11g R2 database with Active DataGuard setup. As we dont use DataGuard Broker I like to use OEM user-defined metric to create alert,if any GAP's detections happened and send an email.

      So when setting up we get error "SQL Query - The number of columns specified in "SQL Query" does not match the value specified in "SQL Query Output"

      Metric Type - String
      SQL Query Output - Two Columns
      SQL Query:
      ################################
      SELECT APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or
      (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or
      ((LOG_ARCHIVED-LOG_APPLIED) > 5))
      then 'Error! Log Gap is '
      else 'OK!'
      end) Status
      FROM
      (
      SELECT MAX(SEQUENCE#) LOG_ARCHIVED
      FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1
      ),
      (
      SELECT MAX(SEQUENCE#) LOG_APPLIED
      FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
      ),
      (
      SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
      FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
      )
      /
      ##########################

      Comparison Operator - CONTAINS Warning 2 Critical 10

      Appreciate any help as to where i'm going wrong.

      Many thanks!