Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: target_data_upgrade.sql 08-sep-2005.01:33:42 neearora Exp $
Rem
Rem target_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      target_data_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    neearora    09/08/05 - bug 4597635. compare emd_url on basis of name 
Rem                           and port 
Rem    pmodi       08/24/05 - Bug:4569754 use is_fatally_broken function 
Rem    pmodi       07/22/05 - Created
Rem

-- Bug:4365302 - Log metric error for al broken target
--Script to log metric error for broken target. Only for agent side target.
DECLARE
  l_agent_guid mgmt_targets.target_guid%TYPE;
  l_response_metric_guid mgmt_metrics.metric_guid%TYPE;
  l_coll_name mgmt_coll_items.coll_name%TYPE;
  l_avail_marker_ts mgmt_availability.start_collection_timestamp%TYPE;
  l_start_position NUMBER ;
  l_end_position NUMBER ;
  l_emd_url mgmt_targets.emd_url%TYPE;
BEGIN

  FOR i IN (SELECT *
              FROM mgmt_targets
             WHERE emd_url IS NOT NULL
               AND  broken_reason != MGMT_GLOBAL.G_NOT_BROKEN) LOOP

    IF EMD_LOADER.is_fatally_broken(i.broken_reason) THEN

      l_start_position := INSTR(i.emd_url,':');    
      l_end_position := INSTR(i.emd_url,'/',l_start_position + 3);
      l_emd_url := SUBSTR(i.emd_url,l_start_position + 3, l_end_position - l_start_position - 3);
      l_emd_url := '%'|| l_emd_url ||'%';

    SELECT target_guid
      INTO l_agent_guid
      FROM mgmt_targets
     WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
       AND emd_url like l_emd_url;

    BEGIN
      SELECT m.metric_guid
        INTO l_response_metric_guid
        FROM MGMT_METRICS m
       WHERE m.target_type = i.target_type
         AND m.type_meta_ver = i.type_meta_ver
         AND (m.category_prop_1 = i.category_prop_1 OR m.category_prop_1 = ' ')
         AND (m.category_prop_2 = i.category_prop_2 OR m.category_prop_2 = ' ')
         AND (m.category_prop_3 = i.category_prop_3 OR m.category_prop_3 = ' ')
         AND (m.category_prop_4 = i.category_prop_4 OR m.category_prop_4 = ' ')
         AND (m.category_prop_5 = i.category_prop_5 OR m.category_prop_5 = ' ')
         AND m.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
         AND m.metric_column = ' '
         AND ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_response_metric_guid := NULL;
    END;

    IF l_response_metric_guid IS NOT NULL THEN

      BEGIN
        SELECT mci.coll_name
          INTO l_coll_name
          FROM mgmt_coll_items mci, mgmt_coll_item_metrics mcim
         WHERE mcim.metric_guid = l_response_metric_guid
           AND mcim.type_meta_ver = mci.type_meta_ver
           AND mcim.type_meta_ver = i.type_meta_ver
           AND mcim.target_type = mci.target_type
           AND mcim.target_type = i.target_type
           AND mcim.coll_name = mci.coll_name
           AND (mci.category_prop_1 = i.category_prop_1 OR mci.category_prop_1 = ' ')
           AND (mci.category_prop_2 = i.category_prop_2 OR mci.category_prop_2 = ' ')
           AND (mci.category_prop_3 = i.category_prop_3 OR mci.category_prop_3 = ' ')
           AND (mci.category_prop_4 = i.category_prop_4 OR mci.category_prop_4 = ' ')
           AND (mci.category_prop_5 = i.category_prop_5 OR mci.category_prop_5 = ' ');
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
           l_coll_name := MGMT_GLOBAL.G_AVAIL_METRIC_NAME ;
       END;

       SELECT marker_timestamp INTO l_avail_marker_ts
        FROM mgmt_availability_marker
       WHERE target_guid = i.target_guid;


      BEGIN
        INSERT INTO MGMT_METRIC_ERRORS
         (target_guid, metric_guid, coll_name,
          agent_guid, collection_timestamp, metric_error_message, metric_error_type)
         VALUES
         (i.target_guid, l_response_metric_guid, l_coll_name,
          l_agent_guid, GREATEST(MGMT_TARGET.sysdate_target(i.target_guid), l_avail_marker_ts),
          'Target is in broken state. Reason - '||i.broken_str, MGMT_GLOBAL.G_METRIC_ERROR_TYPE_ERROR) ;
      EXCEPTION
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR('LOADER', 0,
            'Unable to log metric error for broken target (upgrade) : ' ||
            '(target_guid : '||i.target_guid || ')' ||
            '(agent_guid : '||l_agent_guid || ') ( broken_msg : '|| i.broken_str || ')' ||
            '(Error : '||sqlerrm ||')' );

      END;

    END IF;
    END IF;
  END LOOP;
  COMMIT;
END;
/