Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/ha/hapage_pkgbodys.sql /st_emdbsa_11.2/2 2009/02/18 14:05:26 gallison Exp $
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      hapage_pkgbodys.sql
Rem
Rem    DESCRIPTION
Rem      PL/SQL procedures for the HA Page
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem     gallison   02/12/09 - Bug 8248153
Rem     gallison   01/10/09 - Port to emdbsa (acknowledged is not supported).
Rem     gallison   12/08/08 - Use string_value from mgmt_current_metrics
Rem     gallison   02/14/08 - Add adrAlertLogDataFailure
Rem     gallison   01/17/08 - Fix sql
Rem     gallison   11/21/07 - Add events
Rem     sjconnol   11/05/07 - Add next backup
Rem     gallison   08/30/07 - Add supplementalLogging
Rem     sjconnol   08/02/07 - Use new mgmt_ha_dg_target_summary info
Rem     sjconnol   04/10/07 - Fix availability pct
Rem     sjconnol   03/15/07 - Fix flashback time
Rem     sjconnol   08/20/06 - Creation
Rem

CREATE OR REPLACE PACKAGE body hapage_database AS

-- Procedure get_br_values
--
-- PURPOSE: Get summary info for B/R section of HA page.
-- IN parameters:
--   targetName: The target name
--   targetType: The target type
-- OUT parameters:
--   brSummary: Output structure containing summary info
procedure get_br_values(
        targetName      IN VARCHAR2,
        targetType      IN VARCHAR2,
        brSummary       OUT MGMT_HAPAGE_BR_OBJ
        ) IS

  flashbackEnd VARCHAR2(4000);
  flashbackEndDate DATE;
  backupStart DATE;
  backupEnd DATE;
  nextSchedBackup DATE;
  backupTime VARCHAR2(4000);
  inputSize VARCHAR2(4000);
  outputSize VARCHAR2(4000);
  backupRate VARCHAR2(4000);
  backupStatus VARCHAR2(30);
  inputType VARCHAR2(13);
  estMTTR NUMBER;

  begin
    BEGIN
      SELECT status, start_time, end_time, time_taken_display, input_bytes_display, output_bytes_display, output_bytes_per_sec_display, input_type
      INTO backupStatus, backupStart, backupEnd, backupTime, inputSize, outputSize, backupRate, inputType
      FROM mgmt_ha_backup
      WHERE target_guid = (select target_guid from mgmt_targets where target_name = targetName and target_type = targetType);      
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    BEGIN
      SELECT estimated_mttr
      INTO estMTTR
      FROM mgmt_ha_mttr
      WHERE target_guid = (select target_guid from mgmt_targets where target_name = targetName and target_type = targetType);      
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    BEGIN
      SELECT  b.string_value into flashbackEnd
       FROM mgmt_metrics a,
            mgmt_current_metrics b,
            mgmt_targets c   
       WHERE c.target_name = targetName and c.target_type = targetType
       AND a.metric_guid = b.metric_guid
       AND a.metric_name='ha_flashrecovery'
       AND a.metric_column= 'oldest_flashback_time'
       AND b.target_guid = c.target_guid
       AND a.type_meta_ver = c.type_meta_ver
       AND (a.category_prop_1 = c.category_prop_1 OR a.category_prop_1 = ' ')
       AND (a.category_prop_2 = c.category_prop_2 OR a.category_prop_2 = ' ')
       AND (a.category_prop_3 = c.category_prop_3 OR a.category_prop_3 = ' ')
       AND (a.category_prop_4 = c.category_prop_4 OR a.category_prop_4 = ' ')
       AND (a.category_prop_5 = c.category_prop_5 OR a.category_prop_5 = ' ');
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    BEGIN
      SELECT   scheduled_time
	INTO   nextSchedBackup
        FROM   MGMT_JOB_EXEC_SUMMARY jes,
               MGMT_JOB_TYPE_INFO jti,
               MGMT_JOB_TARGET jt,
               MGMT_TARGETS t
       WHERE   jes.job_id            = jt.job_id
         AND   jes.target_list_index = jt.target_list_index
         AND   jt.target_guid        = t.target_guid
         AND   t.target_name         = targetName
         AND   t.target_type         = targetType
         AND   jes.job_type_id       = jti.job_type_id
         AND   jti.job_type          = 'Backup'
         AND   jes.status            = MGMT_JOBS.SCHEDULED_STATUS
         AND   rownum = 1
    ORDER BY  scheduled_time;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    flashbackEndDate := getDate(targetName, targetType, flashbackEnd);

    brSummary := MGMT_HAPAGE_BR_OBJ(backupStart,
                                    backupEnd,
                                    backupTime,
                                    inputSize,
                                    outputSize,
                                    backupRate,
                                    inputType,
                                    flashbackEndDate,
                                    estMTTR,
			            nextSchedBackup,
                                    backupStatus);
END get_br_values;

-- Procedure get_ecm_values
--
-- PURPOSE: Get summary info for B/R section of HA page.
-- IN parameters:
--   targetName: The target name
--   targetType: The target type
-- OUT parameters:
--   ecmSummary: Output structure containing summary info
procedure get_ecm_values(
        targetName      IN VARCHAR2,
        targetType      IN VARCHAR2,
        ecmSummary       OUT MGMT_HAPAGE_ECM_OBJ
        ) IS

  logMode VARCHAR2(12);
  forceLogging VARCHAR2(3);
  flashbackOn VARCHAR2(18);
  supplementalLogging VARCHAR2(8) := 'NONE';

  begin
    BEGIN
      SELECT log_mode, force_logging, flashback_on
      INTO logMode, forceLogging, flashbackOn
--      SELECT log_mode, force_logging, flashback_on, supplemental_logging 
--      INTO logMode, forceLogging, flashbackOn, supplementalLogging 
      FROM mgmt_ha_info_ecm 
      WHERE ecm_snapshot_id in (select ecm_snapshot_id from mgmt$ecm_current_snapshots where target_name = targetName and target_type = targetType);
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    ecmSummary := MGMT_HAPAGE_ECM_OBJ(logMode,
                                      forceLogging,
                                      flashbackOn,
                                      supplementalLogging);
END get_ecm_values;

-- Procedure get_dg_prmy_values
--
-- PURPOSE: Get summary info for Data Guard primary section of HA page.
-- IN parameters:
--   targetName: The target name
--   targetType: The target type
-- OUT parameters:
--   dgPrmySummary: Output structure containing summary info.

-- Procedure get_dg_stby_values
--
-- PURPOSE: Get summary info for Data Guard primary section of HA page.
-- IN parameters:
--   targetName: The target name
--   targetType: The target type
-- OUT parameters:
--   dgPrmySummary: Output table of MGMT_HAPAGE_STBY_OBJ objects, one for each stby
procedure get_dg_prmy_values(
        targetName      IN VARCHAR2,
        targetType      IN VARCHAR2,
        dgPrmySummary   OUT MGMT_HAPAGE_PRMY_OBJ
        ) IS

  targetGuid VARCHAR2(32);
  status VARCHAR2(1024);
  stby_list VARCHAR2(1024);
  host VARCHAR2(256);
  name   VARCHAR2(64);
  prmy_name   VARCHAR2(64);
  prmy_id   NUMBER;
  prot_mode   VARCHAR2(256);
  fsfo_status VARCHAR2(256);
  role   VARCHAR2(64);
  using_broker   VARCHAR2(4);
  p_target_name VARCHAR2(256);
  p_target_type VARCHAR2(64);
  coll_timestamp DATE;

  begin
    BEGIN
      SELECT ROLE, PRMY_DB_UNIQUE_NAME, PRMY_DB_ID
        INTO role, prmy_name, prmy_id
        from MGMT_HA_DG_TARGET_SUMMARY
        WHERE target_guid = (select target_guid from mgmt_targets where target_name = targetName and target_type = targetType);      
      -- If this is a primary, get the primary summary information from this row
      IF role = 'PRIMARY' THEN
        SELECT a.target_guid, a.STATUS, a.DB_UNIQUE_NAME, a.PROTECTION_MODE, a.FSFO_STATUS, a.using_broker, a.COLLECTION_TIMESTAMP, b.host_name, a.stby_list
          INTO targetGuid, status, name, prot_mode, fsfo_status, using_broker, coll_timestamp, host, stby_list 
          FROM MGMT_HA_DG_TARGET_SUMMARY a, MGMT_TARGETS b
          WHERE b.target_name = targetName AND b.target_type = targetType AND a.target_guid = b.target_guid;      
          
          p_target_name := targetName;
          p_target_type := targetType;
      -- If this is a standby, get the primary information from row that matches
      -- this standby's PRMY_DB_UNIQUE_NAME and PRMY_DB_ID
      ELSE
        SELECT b.target_name, b.target_type, a.STATUS, a.DB_UNIQUE_NAME, a.COLLECTION_TIMESTAMP, a.using_broker, b.host_name, a.PROTECTION_MODE, a.FSFO_STATUS, a.stby_list
          INTO p_target_name, p_target_type, status, name, coll_timestamp, using_broker, host, prot_mode, fsfo_status, stby_list
          FROM MGMT_HA_DG_TARGET_SUMMARY a, MGMT_TARGETS b
          WHERE a.DB_UNIQUE_NAME = prmy_name
          AND a.DB_ID = prmy_id
          AND a.target_guid = b.target_guid order by target_type desc;
      END IF;

      IF targetType = 'rac_database' THEN
        SELECT property_value
          INTO host
          FROM MGMT_TARGET_PROPERTIES
          WHERE property_name = 'ClusterName'
          AND target_guid = (select target_guid from mgmt_targets where target_name = targetName and target_type = targetType);      
      END IF;
    
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    dgPrmySummary := MGMT_HAPAGE_PRMY_OBJ(status,
                                          name,
                                          host,
                                          prot_mode,
                                          fsfo_status,
                                          using_broker,
                                          coll_timestamp,
                                          p_target_name, 
                                          p_target_type,
                                          stby_list);

END get_dg_prmy_values;

-- Procedure get_dg_stby_values
--
-- PURPOSE: Get summary info for Data Guard primary section of HA page.
-- IN parameters:
--   targetName: The target name
--   targetType: The target type
-- OUT parameters:
--   dgPrmySummary: Output table of MGMT_HAPAGE_STBY_OBJ objects, one for each stby
procedure get_dg_stby_values(
        targetName      IN VARCHAR2,
        targetType      IN VARCHAR2,
        dgStbySummary   OUT MGMT_HAPAGE_STBY_LIST
        ) IS

  name   VARCHAR2(64);
  prmy_name   VARCHAR2(64);
  mname   VARCHAR2(64);
  role   VARCHAR2(64);
  db_id NUMBER;
  prmy_db_id NUMBER;
  m_id NUMBER;

  begin
    BEGIN
      SELECT ROLE, DB_UNIQUE_NAME, DB_ID, PRMY_DB_UNIQUE_NAME, PRMY_DB_ID
        INTO role, name, db_id, prmy_name, prmy_db_id
        from MGMT_HA_DG_TARGET_SUMMARY
        WHERE target_guid = (select target_guid from mgmt_targets where target_name = targetName and target_type = targetType);      
      -- If this is a primary, assemble standby table from rows w/matching prmy_db_unique_name and prmy_db_id
      IF role = 'PRIMARY' THEN
        mname := name;
        m_id := db_id;
      -- If this is a standby, assemble standby table from rows that match
      -- this standby's PRMY_DB_UNIQUE_NAME and prmy_db_id
      ELSE
        mname := prmy_name;
        m_id := prmy_db_id;
      END IF;

      SELECT MGMT_HAPAGE_STBY_OBJ
        (  a.db_unique_name,
           b.host_name,
           a.role,
           a.status,
           a.transport_lag,
           a.apply_lag,
	   p.property_value,
           b.target_name,
           b.target_type,
           a.collection_timestamp,
           a.stby_list
        )
        BULK COLLECT INTO dgStbySummary 
        FROM MGMT_HA_DG_TARGET_SUMMARY a, MGMT_TARGETS b, MGMT_TARGET_PROPERTIES p 
        WHERE a.prmy_db_unique_name = mname 
        AND a.prmy_db_id = m_id
        AND a.active_stby = 'YES'
        AND a.target_guid = b.target_guid      
        AND a.target_guid = p.target_guid
	AND p.property_name = 
          (case when b.target_type = 'rac_database' 
                then 'ClusterName' 
                else 'MachineName' 
          end);

        FOR i IN 1 .. dgStbySummary.count
        LOOP

          SELECT  b.value into dgStbySummary(i).apply_lag
           FROM mgmt_metrics a, mgmt_current_metrics b, mgmt_targets c   
           WHERE c.target_name = dgStbySummary(i).target_name 
           AND c.target_type = dgStbySummary(i).target_type
           AND a.metric_guid = b.metric_guid
           AND a.metric_name='dataguard_sperf'
           AND a.metric_column= 'dg_lag'
           AND b.target_guid = c.target_guid
           AND a.type_meta_ver = c.type_meta_ver
           AND (a.category_prop_1 = c.category_prop_1 OR a.category_prop_1 = ' ')
           AND (a.category_prop_2 = c.category_prop_2 OR a.category_prop_2 = ' ')
           AND (a.category_prop_3 = c.category_prop_3 OR a.category_prop_3 = ' ')
           AND (a.category_prop_4 = c.category_prop_4 OR a.category_prop_4 = ' ')
           AND (a.category_prop_5 = c.category_prop_5 OR a.category_prop_5 = ' ');

          SELECT  b.value,b.collection_timestamp into dgStbySummary(i).trans_lag, dgStbySummary(i).coll_timestamp
           FROM mgmt_metrics a, mgmt_current_metrics b, mgmt_targets c   
           WHERE c.target_name = dgStbySummary(i).target_name 
           AND c.target_type = dgStbySummary(i).target_type
           AND a.metric_guid = b.metric_guid
           AND a.metric_name='dataguard_sperf'
           AND a.metric_column= 'dg_pdl'
           AND b.target_guid = c.target_guid
           AND a.type_meta_ver = c.type_meta_ver
           AND (a.category_prop_1 = c.category_prop_1 OR a.category_prop_1 = ' ')
           AND (a.category_prop_2 = c.category_prop_2 OR a.category_prop_2 = ' ')
           AND (a.category_prop_3 = c.category_prop_3 OR a.category_prop_3 = ' ')
           AND (a.category_prop_4 = c.category_prop_4 OR a.category_prop_4 = ' ')
           AND (a.category_prop_5 = c.category_prop_5 OR a.category_prop_5 = ' ');

        END LOOP;

    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

END get_dg_stby_values;


procedure get_avail_info(targetName    IN  VARCHAR2,
                         targetType    IN  VARCHAR2,
                         availInfo OUT MGMT_HAPAGE_AVAIL_OBJ)
IS

  k_error_msg_prefix CONSTANT VARCHAR2(128) :=
   'Oracle Enterprise Manager: ERROR in hapage_database.getAvailInfo(';

  ----------------
  -- variables
  ----------------
  typeDisplayName       VARCHAR2(256);
  lastUpdatedTime       DATE;
  currentStatus         INTEGER;
  availPct              NUMBER;
  availPctDay           NUMBER;
  availPctWeek          NUMBER;
  availPctMonth         NUMBER;
  startTimestamp        DATE;
  timeZone              VARCHAR2(64);
  instanceName          VARCHAR2(4000);
  instanceStartTime     DATE;
  instanceVersion       VARCHAR2(4000);
  oracleHome            VARCHAR2(4000);
  cpuCount              NUMBER;
  openMode              VARCHAR2(64);
  targetGuid            VARCHAR2(32);
  racInstGuid           VARCHAR2(32);
  estMTTR               NUMBER;

  meta_ver              VARCHAR2(8);
  cat_prop_1            VARCHAR2(64);
  cat_prop_2            VARCHAR2(64);
  cat_prop_3            VARCHAR2(64);
  cat_prop_4            VARCHAR2(64);
  cat_prop_5            VARCHAR2(64);


BEGIN

  BEGIN
    SELECT mt.target_guid, type_display_name,
           TYPE_META_VER, CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3,
           CATEGORY_PROP_4, CATEGORY_PROP_5
      INTO targetGuid, typeDisplayName,
           meta_ver, cat_prop_1, cat_prop_2, cat_prop_3,
           cat_prop_4, cat_prop_5
      FROM mgmt_targets mt
     WHERE target_name = targetName
       AND target_type = targetType;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE mgmt_global.target_does_not_exist;
  END;

  ----------------
  -- get current status information
  ----------------
  BEGIN
    currentStatus  := mgmt_target.get_avail_current_status(targetGuid);
    startTimestamp := mgmt_target.get_current_status_timestamp(targetGuid);
    -- TODO...
    lastUpdatedTime := mgmt_target.get_current_status_timestamp(targetGuid);
    
  EXCEPTION
    WHEN OTHERS THEN
      currentStatus  := mgmt_global.g_status_unknown;
      startTimestamp := NULL;
      dbms_output.put_line(k_error_msg_prefix || targetName ||
                           ') -- unable to get current status.');
  END;

  BEGIN
    ----------------
    -- availability percentage
    ----------------
    availPct := ROUND(mgmt_target.get_avail_pct_value(targetGuid, 10000), 2);
  EXCEPTION
    WHEN OTHERS THEN
      availPct := k_metric_status_inaccessible;
      dbms_output.put_line(k_error_msg_prefix || targetName ||
                           ') -- unable to get availability pct.');
  END;

  BEGIN
    ----------------
    -- availability percentage
    ----------------
    availPctDay := ROUND(mgmt_target.get_avail_pct_value(targetGuid, 1), 2);
  EXCEPTION
    WHEN OTHERS THEN
      availPctDay := k_metric_status_inaccessible;
      dbms_output.put_line(k_error_msg_prefix || targetName ||
                           ') -- unable to get availability pct.');
  END;

  BEGIN
    ----------------
    -- availability percentage
    ----------------
    availPctWeek := ROUND(mgmt_target.get_avail_pct_value(targetGuid, 7), 2);
  EXCEPTION
    WHEN OTHERS THEN
      availPctWeek := k_metric_status_inaccessible;
      dbms_output.put_line(k_error_msg_prefix || targetName ||
                           ') -- unable to get availability pct.');
  END;

  BEGIN
    ----------------
    -- availability percentage
    ----------------
    availPctMonth := ROUND(mgmt_target.get_avail_pct_value(targetGuid, 31), 2);
  EXCEPTION
    WHEN OTHERS THEN
      availPctMonth := k_metric_status_inaccessible;
      dbms_output.put_line(k_error_msg_prefix || targetName ||
                           ') -- unable to get availability pct.');
  END;

  ----------------
  -- get database meta-information
  ----------------
  BEGIN
    getDbStringValues(targetGuid,
                      instanceName,
                      instanceStartTime,
                      instanceVersion,
                      oracleHome,
                      cpuCount,
                      openMode);
  EXCEPTION
    WHEN OTHERS THEN
      instanceName      := NULL;
      instanceStartTime := NULL;
      instanceVersion   := NULL;
      oracleHome        := NULL;
      cpuCount          := 1;
      openMode          := NULL;
      dbms_output.put_line(k_error_msg_prefix || targetName ||
                           ') -- unable to get instance attributes.');
  END;

  ----------------
  -- get database MTTR
  ----------------
  BEGIN
      SELECT estimated_mttr
      INTO estMTTR
      FROM mgmt_ha_mttr
      WHERE target_guid = (select target_guid from mgmt_targets where target_name = targetName and target_type = targetType);      
    EXCEPTION
      WHEN OTHERS THEN NULL;
  END;

  ----------------
  -- get RAC instance start time and other props for earliest started inst
  ----------------
  BEGIN
      IF targetType = 'rac_database' THEN
       select m.member_target_guid 
       INTO racInstGuid 
       from mgmt_target_properties p, mgmt_target_memberships m 
       where p.target_guid = m.member_target_guid and m.composite_target_name = targetName and m.composite_target_type = targetType and p.property_name = 'StartTime' and p.property_value = (select min(p.property_value) from mgmt_target_properties p, mgmt_target_memberships m where p.target_guid = m.member_target_guid and m.composite_target_name = targetName and m.composite_target_type = targetType and p.property_name = 'StartTime');
       IF racInstGuid IS NOT NULL THEN
         getDbStringValues(racInstGuid,
                           instanceName,
                           instanceStartTime,
                           instanceVersion,
                           oracleHome,
                           cpuCount,
                           openMode);
       END IF;
      END IF;
  EXCEPTION
    WHEN OTHERS THEN NULL;

  END;

  ----------------
  -- return information
  ----------------
  availInfo := MGMT_HAPAGE_AVAIL_OBJ(lastUpdatedTime,
                                     currentStatus,
                                     availPct,
                                     availPctDay,
                                     availPctWeek,
                                     availPctMonth,
                                     startTimestamp,
                                     instanceName,
                                     instanceStartTime,
                                     estMTTR,
                                     instanceVersion,
                                     oracleHome,
                                     cpuCount,
                                     openMode);

END;

/*
getDbStringValues
*/
  procedure getDbStringValues(targetGuid        IN VARCHAR2,
                              instanceName      OUT VARCHAR2,
                              instanceStartTime OUT DATE,
                              instanceVersion   OUT VARCHAR2,
                              oracleHome        OUT VARCHAR2,
                              cpuCount          OUT NUMBER,
                              openMode          OUT VARCHAR2) IS

    text_start_time      VARCHAR2(1024) := NULL;
    property_time_format VARCHAR2(1024) := NULL;

    CURSOR aCursor (tg IN VARCHAR2) IS
      SELECT property_name, NVL(property_value, ' ')
        FROM mgmt_target_properties
       WHERE target_guid = HEXTORAW(tg)
         AND property_type IN ('INSTANCE', 'DYNAMICINSTANCE')
         AND property_name IN ('InstanceName', 'StartTime', 'Version', 'OracleHome', 'OpenMode', 'TimeFormat', 'CPUCount');

    property_name  VARCHAR2(64);
    property_value VARCHAR2(1024);

  begin

    instanceName      := ' ';
    instanceStartTime := NULL;
    instanceVersion   := ' ';
    oracleHome        := ' ';
    cpuCount          := 1;
    openMode          := ' ';

    OPEN aCursor (targetGuid);
    LOOP
      FETCH aCursor INTO property_name, property_value;
      exit when aCursor%NOTFOUND;

      IF property_name = 'InstanceName' THEN
        instanceName      := property_value;
      ELSIF property_name = 'StartTime' THEN
        text_start_time   := property_value;
      ELSIF property_name = 'TimeFormat' THEN
        property_time_format := property_value;
      ELSIF property_name = 'Version' THEN
        instanceVersion   := property_value;
      ELSIF property_name = 'OracleHome' THEN
        oracleHome        := property_value;
      ELSIF property_name = 'CPUCount' THEN
        cpuCount          := property_value;
      ELSIF property_name = 'OpenMode' THEN
        openMode          := property_value;
      END IF;
    END LOOP;
    CLOSE aCursor;

    IF (text_start_time IS NOT NULL AND property_time_format IS NOT NULL) THEN
      instanceStartTime := TO_DATE(text_start_time, property_time_format);
    END IF;

  end;

function getDate(targetName    IN  VARCHAR2,
                 targetType    IN  VARCHAR2,
                 datestr       IN VARCHAR2) return DATE IS
 property_time_format VARCHAR2(1024) := NULL;

 BEGIN
       SELECT property_value
        INTO property_time_format
        FROM mgmt_target_properties
       WHERE target_guid = (select target_guid from mgmt_targets where target_name = targetName and target_type = targetType)
         AND property_name = 'TimeFormat';

    IF (datestr IS NOT NULL AND property_time_format IS NOT NULL) THEN
       return TO_DATE(datestr, property_time_format);
    END IF;
    return TO_DATE(datestr, 'DY MON DD HH24:MI:SS YYYY');
 EXCEPTION
    WHEN OTHERS
    THEN RETURN NULL;
 END;

PROCEDURE get_ha_events_guids(
        mgmt_guids   IN MGMT_USER_GUID_ARRAY,
        critical_sev IN NUMBER DEFAULT NULL,
        warning_sev  IN NUMBER DEFAULT NULL,
        info_sev     IN NUMBER DEFAULT NULL,
        days         IN NUMBER DEFAULT NULL,
        ha_mgmt_events  OUT MGMT_HAPAGE_EVENT_LIST
        ) IS
begin
    BEGIN

        SELECT MGMT_HAPAGE_EVENT_OBJ ( 
            h.target_name,
            h.target_type,
            h.severity_code,
            h.message,
            h.collection_timestamp,            
            h.message_nlsid, 
            h.message_params,
            0) 

        BULK COLLECT INTO ha_mgmt_events from 
        (
        select /*+CARDINALITY(guids 5)*/ distinct a.target_name, a.target_type,b.severity_code,b.message,
          b.collection_timestamp,b.message_nlsid, b.message_params
        from TABLE(CAST(mgmt_guids AS MGMT_USER_GUID_ARRAY)) guids, mgmt_targets a, 
        mgmt_current_severity b, mgmt_metrics c, mgmt_current_metrics d, mgmt_current_violation v
        WHERE 
        a.target_guid = guids.column_value and
        a.target_guid = b.target_guid and 
        b.metric_guid = c.metric_guid and 
        b.metric_guid = d.metric_guid(+) and 
        b.target_guid = d.target_guid(+) and 
        a.target_guid = v.target_guid    and 
        b.metric_guid = v.policy_guid    and 
        b.key_value = v.key_value(+)     and 
        b.key_value = d.key_value(+) 
        AND
        (c.metric_name like 'dataguard%' OR 
        c.metric_name like 'observer%' OR 
        c.metric_name like 'ha_%' OR 
        c.metric_name = 'Response' OR  
        c.metric_name = 'Availability' OR 
        c.metric_name = 'health_check' OR 
        c.metric_name = 'Recovery_Area' OR 
        c.metric_name = 'session_wait_chains' OR 
        c.metric_name = 'UserBlock' OR 
        c.metric_name = 'cardinality' OR 
        c.metric_name = 'suspended_session' OR 
        c.metric_name = 'wait_bottlenecks' OR 
        c.metric_name = 'service' OR
        c.metric_name = 'service_performance') 
        AND (c.category_prop_1 = a.category_prop_1 OR  c.category_prop_1 = ' ') 
        AND (c.category_prop_2 = a.category_prop_2 OR  c.category_prop_2 = ' ') 
        AND (c.category_prop_3 = a.category_prop_3 OR  c.category_prop_3 = ' ') 
        AND (c.category_prop_4 = a.category_prop_4 OR  c.category_prop_4 = ' ') 
        AND (c.category_prop_5 = a.category_prop_5 OR  c.category_prop_5 = ' ') 
        and b.load_timestamp > (SYSDATE - days) 
        and b.severity_code IN (critical_sev,warning_sev,info_sev)) h
        ;

    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

end get_ha_events_guids;

procedure get_ha_events_targets(
        targetName    IN  VARCHAR2,
        targetType    IN  VARCHAR2,
        critical_sev   IN NUMBER DEFAULT NULL,
        warn_sev       IN NUMBER DEFAULT NULL,
        info_sev       IN NUMBER DEFAULT NULL,
        days           IN NUMBER DEFAULT NULL,
        haguids_out    OUT MGMT_USER_GUID_ARRAY,
        ha_mgmt_events OUT MGMT_HAPAGE_EVENT_LIST
        ) AS

  l_target_guid mgmt_targets.target_guid%TYPE;
  l_guid_list        EMD_MNTR_USER.cursorType;
  l_guid_counter     INTEGER := 1;
  prmy_name   VARCHAR2(64);
  db_name   VARCHAR2(64);
  m_name   VARCHAR2(64);
  role   VARCHAR2(64);
  db_id NUMBER;
  prmy_db_id NUMBER;
  m_id NUMBER;

BEGIN
  haguids_out := MGMT_USER_GUID_ARRAY();

    BEGIN

    SELECT target_guid INTO l_target_guid
      FROM mgmt_targets tgt
      WHERE tgt.target_name=targetName
      AND tgt.target_type=targetType;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        l_target_guid := NULL;
    END;

    BEGIN 
     SELECT ROLE, DB_UNIQUE_NAME, DB_ID, PRMY_DB_UNIQUE_NAME, PRMY_DB_ID
      INTO role, db_name, db_id, prmy_name, prmy_db_id
      FROM MGMT_HA_DG_TARGET_SUMMARY
      WHERE target_guid = l_target_guid;
 
      IF role = 'PRIMARY' THEN
        m_name := db_name;
        m_id := db_id;

      -- Add the primary guid here. If standby, the guid will be added by the 
      -- standby query  
      haguids_out.extend(1);
      haguids_out(l_guid_counter) := l_target_guid;
      l_guid_counter := l_guid_counter + 1;

       OPEN l_guid_list FOR
         SELECT a.target_guid
           FROM MGMT_HA_DG_TARGET_SUMMARY a, MGMT_TARGETS b
           WHERE a.prmy_db_unique_name = m_name
           AND a.prmy_db_id = m_id 
           AND a.active_stby = 'YES'
           AND a.target_guid = b.target_guid;      


      -- If this is a standby, assemble standby table from rows that match
      -- this standby's PRMY_DB_UNIQUE_NAME and prmy_db_id
      ELSIF (instr(role,'STANDBY') > 0)  THEN
        m_name := prmy_name;
        m_id := prmy_db_id;

        OPEN l_guid_list FOR
        SELECT UNIQUE a.target_guid
          FROM MGMT_HA_DG_TARGET_SUMMARY a, MGMT_TARGETS b
          WHERE ((a.DB_UNIQUE_NAME = m_name AND a.DB_ID = m_id) 
          OR (a.prmy_db_unique_name = m_name AND a.prmy_db_id = m_id AND a.active_stby = 'YES'))
          AND a.target_guid = b.target_guid;      
      END IF;

      LOOP
        FETCH l_guid_list INTO l_target_guid;
          EXIT WHEN l_guid_list%NOTFOUND;
        haguids_out.extend(1);
        haguids_out(l_guid_counter) := l_target_guid;
        l_guid_counter := l_guid_counter + 1;
      END LOOP;
      CLOSE l_guid_list;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    -- make sure the initial guid has been added
    if (haguids_out.count < 1) then
        haguids_out.extend(1);
        haguids_out(l_guid_counter) := l_target_guid;
        l_guid_counter := l_guid_counter + 1;
    end if;

    OPEN l_guid_list FOR
    select /*+CARDINALITY(guids 5)*/
  	unique t.ASSOC_TARGET_GUID from 
        table(cast(haguids_out as mgmt_user_guid_array)) guids,
         mgmt_target_assocs  t
	  where t.SOURCE_TARGET_GUID = guids.column_value ;
      -- Not support in EMDBSA...
-- 	  and t.assoc_guid = mgmt_assoc.g_contains_guid ;	 
      -- Note: to get the host/agent guids, remove the assoc_guid = 'contains'...
    LOOP
      FETCH l_guid_list INTO l_target_guid;
        EXIT WHEN l_guid_list%NOTFOUND;
      haguids_out.extend(1);
      haguids_out(l_guid_counter) := l_target_guid;
      l_guid_counter := l_guid_counter + 1;
    END LOOP;
    CLOSE l_guid_list;

    OPEN l_guid_list FOR
      select /*+CARDINALITY(guids 5) INDEX(t) */ t.target_guid
      from  TABLE(CAST(haguids_out AS MGMT_USER_GUID_ARRAY)) guids, mgmt_targets t, mgmt_target_properties p
      where t.target_type    = 'osm_instance' 
      and   p.property_name  = 'SID' 
      and   t.target_guid    = p.target_guid 
      and   upper(p.property_value) in (select upper(property_value) 
                                       from   mgmt$target_properties  tp
                                       where  tp.property_name = 'OSMInstance'
                                       AND tp.target_guid = guids.column_value
                                       AND tp.property_value != ' ') 
      and   t.host_name in (select host_name from mgmt_targets where target_guid = guids.column_value);

      LOOP
        FETCH l_guid_list INTO l_target_guid;
          EXIT WHEN l_guid_list%NOTFOUND;
        haguids_out.extend(1);
        haguids_out(l_guid_counter) := l_target_guid;
        l_guid_counter := l_guid_counter + 1;
      END LOOP;

      CLOSE l_guid_list;

   get_ha_events_guids(haguids_out,  critical_sev, warn_sev, info_sev, days, ha_mgmt_events);

end get_ha_events_targets;

procedure get_ha_events(
        targetName    IN  VARCHAR2,
        targetType    IN  VARCHAR2,
        haguids_out    OUT MGMT_USER_GUID_ARRAY,
        ha_mgmt_events  OUT MGMT_HAPAGE_EVENT_LIST
        ) AS
        
begin

    get_ha_events_targets(targetName,targetType,25,20,18,9999, haguids_out, ha_mgmt_events);
    
end;

procedure dump_ha_info(
                haguids MGMT_USER_GUID_ARRAY,
                events mgmt_hapage_event_list
                ) IS

    l_target_name varchar2(256);
    l_target_type varchar2(256);
    maxCount number;
    ha_targets       EMD_MNTR_USER.cursorType;
  BEGIN

    DBMS_OUTPUT.PUT_LINE(' ********************** ');
    DBMS_OUTPUT.PUT_LINE('----');
    DBMS_OUTPUT.PUT_LINE('---- GOT ' || haguids.COUNT || ' targets -----');
    DBMS_OUTPUT.PUT_LINE(' ');
    open ha_targets for 
    select  /*+CARDINALITY(guids 5)*/ t.target_name,t.target_type from 
      TABLE(CAST(haguids AS MGMT_USER_GUID_ARRAY)) guids, mgmt_targets t
    WHERE t.target_guid = guids.column_value;

    LOOP  

      FETCH ha_targets INTO l_target_name, l_target_type;
        EXIT WHEN ha_targets%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(l_target_name || ':' || l_target_type);

    END LOOP;
    CLOSE ha_targets;
    
    if events is not null then 

    DBMS_OUTPUT.PUT_LINE(' ********************** ');
    DBMS_OUTPUT.PUT_LINE('---- GOT ' || events.COUNT || ' events ----');
    DBMS_OUTPUT.PUT_LINE(' ');

    maxCount := 5;
    if (events.count < 5) then
      maxCount := events.count;
    end if;
    FOR i IN 1 .. maxCount
    LOOP
      DBMS_OUTPUT.PUT_LINE('event:' || events(i).target_name || ', ' || events(i).msg || ' : ' || events(i).creation_date || ' sev: ' || events(i).severity || ' ack: ' || events(i).acknowledged);
    END LOOP;
    end if;
end;

end hapage_database;
/