Rem drv: <migrate type="data_upgrade" version="10.1.0.4"/> 
Rem
Rem $Header: severity_data_upgrade.sql 13-jan-2005.00:57:57 ktlaw Exp $
Rem
Rem severity_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_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    ktlaw       01/13/05 - add repmgr header 
Rem    rpinnama    11/24/04 - rpinnama_bug-4016430
Rem    rpinnama    11/24/04 - Created
Rem

-- Fix 4016430 : This script fixes a bug in the availability purge
-- This script adds a record that the availability purge would have deleted because of bug 4016430

DECLARE
  l_resp_stat_metrics NUMBER;
  l_curr_status mgmt_availability.current_status%TYPE;
  l_start_collection_timestamp mgmt_availability.start_collection_timestamp%TYPE;
  l_cnt NUMBER;
BEGIN

  l_cnt := 0;
  -- Targets with only one availability record are 
  -- candidates that need to be fixed.
  FOR avail_rec IN (SELECT target_name, target_type, t.target_guid, count(*)
                      FROM mgmt_targets t, mgmt_availability a
                     WHERE a.target_guid = t.target_guid
                     GROUP BY target_name, target_type, t.target_guid
                    HAVING count(*) = 1)
  LOOP

    -- Check to see if there is Response/Status for this target
    SELECT count(*) into l_resp_stat_metrics
      FROM mgmt_targets t, mgmt_metrics m
     WHERE t.target_type = m.target_type
       AND t.type_meta_ver = m.type_meta_ver
       AND ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
       AND ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
       AND ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
       AND ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
       AND ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
       AND t.target_name = avail_rec.target_name
       AND t.target_type = avail_rec.target_type
       AND m.metric_name = 'Response'
       AND m.metric_column = 'Status';

    IF (l_resp_stat_metrics > 0) THEN
      -- If response/stauts metric exists, 
      -- get the current status and timestamp for the only availability record
      SELECT current_status, start_collection_timestamp
        INTO l_curr_status, l_start_collection_timestamp
        FROM mgmt_availability
       WHERE target_guid = avail_rec.target_guid;

      IF (l_curr_status in (0, 1, 2, 3, 5) ) THEN

        -- If current status is one of DOWN, UP, ERROR, AGENTDOWN, BLACKOUT
        -- Insert an unknown record before that
        l_cnt := l_cnt + 1;

        -- Add pending avail record
        INSERT INTO MGMT_AVAILABILITY
          (target_guid, current_status, start_collection_timestamp, end_collection_timestamp)
         VALUES
          (avail_rec.target_guid, 6, l_start_collection_timestamp - 30, l_start_collection_timestamp);

        -- DBMS_OUTPUT.PUT_LINE('Idx -' || l_cnt || ' Target type - ' || avail_rec.target_type ||
        --     ' Name - ' || avail_rec.target_name ||
        --     ' Status - ' || l_curr_status);


      ELSIF (l_curr_status = 4) THEN

        -- If current status is UNREACHABLE
        -- Insert an UP record before that (as it would have been deleted as part of avail purge)
	-- 
        l_cnt := l_cnt + 1;

        -- Add pending avail record
        INSERT INTO MGMT_AVAILABILITY
          (target_guid, current_status, start_collection_timestamp, end_collection_timestamp)
         VALUES
          (avail_rec.target_guid, 1, l_start_collection_timestamp - (1/(24*60)), l_start_collection_timestamp);

        -- DBMS_OUTPUT.PUT_LINE('Idx -' || l_cnt || ' Target type - ' || avail_rec.target_type ||
        --     ' Name - ' || avail_rec.target_name ||
        --     ' Status - ' || l_curr_status);

      ELSE -- l_curr_status = 6, UNKNOWN

        -- Do nothing..
        NULL;

      END IF; -- If curr_status = 1

    END IF;

  END LOOP;

END;
/