2 Replies Latest reply: Jun 14, 2012 9:17 AM by EastBay_Oracle RSS

    OEM Query Results Question

    EastBay_Oracle
      Hi..

      I am working on a query to display the last 30 days of uptime for our databases. This is the query:

      select availability_status, target_name, target_type, end_time - start_time from (select availability_status, target_name, target_type,
      nvl(end_timestamp, sysdate) end_time,
      case when start_timestamp < sysdate -31 then sysdate -31
      else start_timestamp end start_time
      from MGMT$AVAILABILITY_HISTORY
      where nvl(end_timestamp, sysdate) > sysdate -31
      and target_name ='FSPERF11')

      I get the results below:

      AVAILABILITY_STATUS TARGET_NAME TARGET_TYPE END_TIME-START_TIME
      Agent Down FSPERF11 oracle_database 0.006
      Target Down FSPERF11 oracle_database 0.003
      Target Down FSPERF11 oracle_database 0.001
      Target Up FSPERF11 oracle_database 2.91
      Target Up FSPERF11 oracle_database 10.803
      Target Up FSPERF11 oracle_database 0
      Target Up FSPERF11 oracle_database 17.27



      The question I have is why do I have multiple results when I am only looking for database uptime/downtime? Why do I have 3 Target Up results?

      Thanks...
        • 1. Re: OEM Query Results Question
          Venkata Thiruveedhi-Oracle
          Hi,

          You can compare the results with the "availability status" report for the same target.

          Navigation in 12c:-

          Enterprise/reports/Information publisher reports/scroll down to "availability history" /target

          Navigation in 11g:-

          reports/availability history/target

          You can see the sql associated with any of the reports, by doing a 'create like' on it, click on "elements" tab, next to "table from sql" click on "set parameters" - you can see the sql associated with the query.

          This "availability history" report, shows overall ability and the history details of each uptime/downtime during the period, plus the duration.

          Best Regards,
          Venkat
          • 2. Re: OEM Query Results Question
            EastBay_Oracle
            Thanks! When I collect the SQL it gives me an invalid SQL statement error when I go to run it. This is the SQL listed:

            SELECT sum(Avail) OVERALL_AVAILABILITY_PER_ID , sum(T_Uptime) TOTAL_UPTIME_HRS_ID , sum(T_Downtime) TOTAL_DOWNTIME_HRS_ID , sum(T_Blackouttime) TOTAL_BLACKOUT_TIME_HRS_ID, sum(T_Unmonitoredtime) TOTAL_UNMONITORED_TIME_HRS_ID from(
            select 0 Avail, 0 T_Uptime, 0 T_Downtime, 0 T_Blackouttime, 0 T_Unmonitoredtime from dual UNION ALL
            SELECT round( st_dur.tgt_up/(decode(st_dur.tgt_up+st_dur.agent_down+st_dur.tgt_down,0,1,st_dur.tgt_up+st_dur.agent_down+st_dur.tgt_down))*100,2) Avail
            ,round((st_dur.tgt_up*24),2) T_Uptime, round((st_dur.tgt_down*24),2) T_Downtime, round((st_dur.blackout*24),2) T_Blackouttime, round(((st_dur.agent_down+st_dur.metric_error+st_dur.pend_unknown+st_dur.unreach)*24),2) T_Unmonitoredtime
            FROM (
            SELECT SUM( decode(AVAI_STATUS,'agent down',DURATION,0)) agent_down,
                 SUM(decode(AVAI_STATUS,'blackout',DURATION,0)) blackout,
                 SUM(decode(AVAI_STATUS,'metric error',DURATION,0)) metric_error,
                 SUM(decode(AVAI_STATUS,'pending/unknown',DURATION,0)) pend_unknown,
                 SUM(decode(AVAI_STATUS,'target down',DURATION,0)) tgt_down,
                 SUM(decode(AVAI_STATUS,'target up',DURATION,0)) tgt_up,
                 SUM(decode(AVAI_STATUS,'unreachable',DURATION,0)) unreach FROM(
                      SELECT LOWER(A.AVAILABILITY_STATUS) AVAI_STATUS
                      ,ROUND(least(nvl(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))), MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) )
                      -greatest(A.start_timestamp,MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) ),4) DURATION
                      FROM mgmt$availability_history A,MGMT$TARGET B WHERE A.target_guid=??EMIP_BIND_TARGET_GUID?? AND A.TARGET_GUID=B.TARGET_GUID
                 and A.start_timestamp>=(select min(NVL(end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))) from mgmt$availability_history where target_guid=??EMIP_BIND_TARGET_GUID?? )
                 and ((A.start_timestamp>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) AND NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))<MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION))
                      OR(MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between A.start_timestamp AND NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))))OR
                      (MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between A.start_timestamp and NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))))
                 )
            )st_dur)

            What do the "??" represent and how do I get it to run?

            Thanks again...