Rem drv: <migrate type="data_upgrade" version="10.2.0.0" pos="failover/failover_data_upgrade.sql+"/> 
Rem
Rem $Header: group_data_upgrade.sql 26-jul-2005.19:32:12 chyu Exp $
Rem
Rem group_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      group_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    chyu        07/26/05 - modifying the migrate header position 
Rem    chyu        07/18/05 - updating the new rep manager header 
Rem    rpatti      07/14/05 - fix
Rem    pmodi       06/27/05 - Bug:4428988 - Use DISTINCT in SQL qry 
Rem    rpatti      06/27/05 - disallow templates for grps 
Rem    pmodi       06/20/05 - Bug:4305824 -Uncomment drop table 
Rem                           mgmt_target_comp_def 
Rem    jpyang      05/11/05 - add missing type properties 
Rem    pmodi       04/19/05 - Exception handling while adding chart 
Rem    neearora    03/14/05 - Corrected the typo 
Rem    eujang      03/03/05 - commenting out the dropping of the 
Rem                           mgmt_comp_target_def to sync up with the 
Rem                           creation 
Rem    pmodi       02/21/05 - update of user privilege/grants 
Rem    pmodi       02/17/05 - pmodi_em_group_migration1
Rem    pmodi       02/10/05 - chart migration 
Rem    ktlaw       01/13/05 - add repmgr header 
Rem    ramalhot    01/10/05 - ramalhot_goup_migration
Rem    ramalhot    12/30/04 - Created
Rem

DECLARE
  l_member_targets    SMP_EMD_NVPAIR_ARRAY;
  l_new_target_guid   mgmt_targets.target_guid%TYPE;
  l_targets_to_add    SMP_EMD_NVPAIR_ARRAY;
  l_targets_to_remove SMP_EMD_NVPAIR_ARRAY;
  l_charts            MGMT_GRP_CHART_ARRAY;
  l_mgmt_guid_array   MGMT_GUID_ARRAY := MGMT_GUID_ARRAY();
  metric_not_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(metric_not_exists, -20216);
BEGIN

  -- Migrate Database Group to Generic groups 
  FOR rec IN ( SELECT a.target_name prev_target_name,
                      a.target_guid prev_target_guid,
                      a.timezone_region tzr, 
                      CASE 
                        WHEN B.target_name IS NULL THEN
                          a.target_name 
                        ELSE 
                          'database_' || a.target_name 
                      END new_target_name 
                 FROM (SELECT *
                         FROM mgmt_targets
                        WHERE target_type = 'oracle_database_group'
                      ) a,
                      (SELECT *
                         FROM mgmt_targets
                        WHERE target_type = 'composite'
                      ) b
                WHERE a.target_name = b.target_name(+)
             )
  LOOP
    l_member_targets := new SMP_EMD_NVPAIR_ARRAY();

    SELECT SMP_EMD_NVPAIR(member_target_name, member_target_type) BULK COLLECT
      INTO l_member_targets 
      FROM mgmt_target_memberships
     WHERE composite_target_guid = rec.prev_target_guid; 

    -- Create Group 
    MGMT_TARGET.add_group(p_group_name => rec.new_target_name, 
                          p_group_type => 'composite', 
                          p_member_targets => l_member_targets,
                          p_timezone_rgn => rec.tzr);


    l_new_target_guid := MGMT_TARGET.get_target_guid(rec.new_target_name,'composite');
    -- Register Charts 
    l_charts := new MGMT_GRP_CHART_ARRAY();
    l_charts.extend;
    l_charts(l_charts.LAST) := new MGMT_GRP_CHART('oracle_database',
                                                  'wait_bottlenecks',
                                                  'user_wait_time_pct',
                                                   MGMT_GROUP.CHART_PEAK_TARGETS,
                                                   1,3,NULL,NULL,NULL,NULL,NULL,NULL ); 	 
    BEGIN
      EM_GROUP.create_charts(rec.new_target_name,'composite',l_charts);
    EXCEPTION 
      WHEN metric_not_exists THEN
      NULL;
      WHEN OTHERS THEN
      raise_application_error(-20001, sqlerrm);
    END;
               
    l_targets_to_add := new SMP_EMD_NVPAIR_ARRAY();
    l_targets_to_add.extend;
    l_targets_to_add(l_targets_to_add.LAST) := new SMP_EMD_NVPAIR(rec.new_target_name,'composite');  
    l_targets_to_remove := new SMP_EMD_NVPAIR_ARRAY();
    l_targets_to_remove.extend;
    l_targets_to_remove(l_targets_to_remove.LAST) := new SMP_EMD_NVPAIR(rec.prev_target_name,'oracle_database_group'); 	 

 
    -- Modifiy	Parent Targets 
    FOR parent IN (SELECT composite_target_name , composite_target_type, composite_target_guid 
                     FROM mgmt_target_memberships 
                    WHERE member_target_guid = rec.prev_target_guid
                  )
    LOOP
     
      MGMT_TARGET.modify_group(parent.composite_target_name, 
                               parent.composite_target_type,
                               l_targets_to_add,
                               l_targets_to_remove);
     
    END LOOP;
  
    -- resubmit the jobs here 
    UPDATE mgmt_job_target
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;

    UPDATE mgmt_job_ext_targets
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;
 
    UPDATE mgmt_job_step_targets
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;

    -- modify the blackouts here 
    UPDATE mgmt_blackout_target_details
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;

    UPDATE mgmt_blackout_flat_targets
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;
 

    -- Update user privilege/grants for oracle_database_group

    UPDATE mgmt_priv_grants
       SET guid = l_new_target_guid
     WHERE guid = rec.prev_target_guid ;

    --Delete the original database group 
    MGMT_ADMIN.delete_target_sync(rec.prev_target_name,'oracle_database_group');
  END LOOP;


  -- Migrate Host Group to Generic groups 
  FOR rec IN ( SELECT a.target_name prev_target_name,
                      a.target_guid prev_target_guid,
                      a.timezone_region tzr, 
                      CASE 
                        WHEN B.target_name IS NULL THEN
                          a.target_name 
                        ELSE 
                          'host_' || a.target_name 
                      END new_target_name 
                 FROM (SELECT *
                         FROM mgmt_targets
                        WHERE target_type = 'host_group'
                      ) a,
                      (SELECT *
                         FROM mgmt_targets
                        WHERE target_type = 'composite'
                      ) b
                WHERE a.target_name = b.target_name(+)
             )
  LOOP
    l_member_targets := new SMP_EMD_NVPAIR_ARRAY();

    SELECT SMP_EMD_NVPAIR(member_target_name, member_target_type) BULK COLLECT
      INTO l_member_targets 
      FROM mgmt_target_memberships
     WHERE composite_target_guid = rec.prev_target_guid; 
          
    -- Create Group 
    MGMT_TARGET.add_group(p_group_name => rec.new_target_name, 
                          p_group_type => 'composite', 
                          p_member_targets => l_member_targets,
                          p_timezone_rgn => rec.tzr);

    l_new_target_guid := MGMT_TARGET.get_target_guid(rec.new_target_name,'composite');

    -- Register Charts 
    l_charts := new MGMT_GRP_CHART_ARRAY();
    l_charts.extend;
    l_charts(l_charts.LAST) := new MGMT_GRP_CHART('host',
                                                  'Load',
                                                  'cpuUtil',
                                                   MGMT_GROUP.CHART_PEAK_TARGETS,
                                                   1,3,NULL,NULL,NULL,NULL,NULL,NULL ); 	
    l_charts.extend; 
    l_charts(l_charts.LAST) := new MGMT_GRP_CHART('host',
                                                  'Load',
                                                  'totIO',
                                                   MGMT_GROUP.CHART_PEAK_TARGETS,
                                                   3,3,NULL,NULL,NULL,NULL,NULL,NULL ); 
    l_charts.extend;
    l_charts(l_charts.LAST) := new MGMT_GRP_CHART('host',
                                                  'Load',
                                                  'memUsedPct',
                                                   MGMT_GROUP.CHART_PEAK_TARGETS,
                                                   2,3,NULL,NULL,NULL,NULL,NULL,NULL );
       
    EM_GROUP.create_charts(rec.new_target_name,'composite',l_charts);
               
    l_targets_to_add := new SMP_EMD_NVPAIR_ARRAY();
    l_targets_to_add.extend;
    l_targets_to_add(l_targets_to_add.LAST) := new SMP_EMD_NVPAIR(rec.new_target_name,'composite');  
    l_targets_to_remove := new SMP_EMD_NVPAIR_ARRAY();
    l_targets_to_remove.extend;
    l_targets_to_remove(l_targets_to_remove.LAST) := new SMP_EMD_NVPAIR(rec.prev_target_name,'host_group'); 	 

 
    -- Modifiy	Parent Targets 
    FOR parent IN (SELECT composite_target_name , composite_target_type, composite_target_guid 
                     FROM mgmt_target_memberships 
                    WHERE member_target_guid = rec.prev_target_guid
                  )
    LOOP
      MGMT_TARGET.modify_group(parent.composite_target_name, 
                               parent.composite_target_type,
                               l_targets_to_add,
                               l_targets_to_remove); 
    END LOOP;  
    -- resubmit the jobs here 
    UPDATE mgmt_job_target
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;

    UPDATE mgmt_job_ext_targets
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;
 
    UPDATE mgmt_job_step_targets
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;

    -- modify the blackouts here 
    UPDATE mgmt_blackout_target_details
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;

    UPDATE mgmt_blackout_flat_targets
       SET target_guid = l_new_target_guid 
     WHERE target_guid  = rec.prev_target_guid;

    -- Update user privilege/grants for oracle_database_group

    UPDATE mgmt_priv_grants
       SET guid = l_new_target_guid
     WHERE guid = rec.prev_target_guid ;

    --Delete the original host group 
    MGMT_ADMIN.delete_target_sync(rec.prev_target_name,'host_group');
  END LOOP;

  -- Migrate summary metrics to chart for group

  -- Select group GUID and name
  SELECT MGMT_GUID_OBJ(comp_target_guid, target_name) BULK COLLECT
    INTO l_mgmt_guid_array
    FROM ( SELECT DISTINCT c.comp_target_guid comp_target_guid, t.target_name target_name
             FROM mgmt_comp_target_def c, mgmt_targets t
            WHERE c.comp_target_guid = t.target_guid
         ) ;

  FOR c IN 1 .. l_mgmt_guid_array.count LOOP

    l_charts := new MGMT_GRP_CHART_ARRAY();

    -- for each of the group get summary metrics of and  create a chart
    FOR rec IN (SELECT DISTINCT m.target_type member_target_type, m.metric_name member_metric_name, m.metric_column member_metric_column 
                 FROM mgmt_comp_target_def def, mgmt_metrics m
                WHERE def.metric_guid = m.metric_guid
                  AND def.comp_target_guid = l_mgmt_guid_array(c).guid
               )
    LOOP
      l_charts.extend;
      -- Setting MIN/MAX/AVG chart to "1" and SUM/STDEV chart to "0", default value
      l_charts(l_charts.LAST) := new MGMT_GRP_CHART(rec.member_target_type,
                                                    rec.member_metric_name,
                                                    rec.member_metric_column,
                                                    MGMT_GROUP.CHART_SUMMARY_METRIC,
                                                    1, 3, 1, 1, 1, 0, 0, NULL ); 

    END LOOP; 

    EM_GROUP.create_charts(p_target_name => l_mgmt_guid_array(c).name,
                           p_target_type =>  MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE,
                           p_charts_list => l_charts );

  END LOOP;

-- register type properties
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_HOST_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_AGENT_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_DATABASE_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_BEACON_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_IAS_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_IAS_TARGET_TYPE,
                                       MGMT_GLOBAL.G_IS_COMPOSITE_PROP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_OC4J_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_OC4J_TARGET_TYPE,
                                       MGMT_GLOBAL.G_IS_COMPOSITE_PROP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_WEBCACHE_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_HTTP_SERVER_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_TEMPLATE_PROP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');
  MGMT_TARGET.add_target_type_property(MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE,
                                       MGMT_GLOBAL.G_DISALLOW_REDUNDANCY_GROUP,
                                       '1');

  COMMIT;
END;
/
-- drop the old summary matrics table 
rem This table CAN NOT be dropped along with group_schema upgrade as we need this table data upgrade.
rem This table have been removed from latest code
DROP TABLE mgmt_comp_target_def ;