Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: basic_data_upgrade.sql 26-sep-2005.17:55:43 gan Exp $
Rem
Rem basic_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      basic_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    gan         09/26/05 - set remote column to 0 
Rem    rpinnama    09/01/05 - Populate mgmt_polcies table with thresholded 
Rem                           policies 
Rem    jsadras     07/29/05 - bug:4523431 compress mgmt_current_metrics_idx_01 
Rem    ramalhot    07/27/05 - rep_id related code added 
Rem    bkesavan    07/21/05 - bkesavan_upg4032726
Rem    bkesavan    07/18/05 - Created
Rem

Rem Upgrade script for bug 4032726
DECLARE
   ptype MGMT_PURGE_POLICY.POLICY_TYPE%TYPE;
BEGIN
   SELECT policy_type
   INTO   ptype
   FROM MGMT_PURGE_POLICY
   WHERE policy_name = 'MGMT_METRIC_ERRORS';

   IF ptype = 1 THEN
      EM_PURGE.drop_purge_policy('MGMT_METRIC_ERRORS');
      EM_PURGE.add_purge_policy('MGMT_METRIC_ERRORS',
                     EM_PURGE.G_POLICY_TYPE_SYSTEM,
                     'EMD_LOADER.METRIC_ERROR_PURGE',
                     180*24,
                     NULL,
                     'Purge policy for Metric errors.',
                     EM_PURGE.G_RETENTION_GROUP_ALERTS
                     );

   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('***** INFO : Error adding purge policy MGMT_METRIC_ERRORS');
      NULL;

END;
/

DECLARE
   ptype MGMT_PURGE_POLICY.POLICY_TYPE%TYPE;
BEGIN
   SELECT policy_type
   INTO   ptype
   FROM MGMT_PURGE_POLICY
   WHERE policy_name = 'MGMT_STRING_METRIC_HISTORY';

   IF ptype = 1 THEN
      EM_PURGE.drop_purge_policy('MGMT_STRING_METRIC_HISTORY');
      EM_PURGE.add_purge_policy('MGMT_STRING_METRIC_HISTORY',
                     EM_PURGE.G_POLICY_TYPE_SYSTEM,
                     'EMD_LOADER.STRING_HISTORY_PURGE',
                     31*24,
                     NULL,
                     'Purge policy for String metric history.',
                     EM_PURGE.G_RETENTION_GROUP_LVL2_SUMMARY
                     );

   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('***** INFO : Error adding purge policy MGMT_STRING_METRIC_HISTORY');
      NULL;

END;
/

BEGIN
  EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_CURRENT_METRICS_IDX_01',
                                 1,FALSE); 
END ;
/
BEGIN
  INSERT
    INTO mgmt_parameters
         (parameter_name, parameter_value, parameter_comment, internal_flag)
  VALUES
         (MGMT_GLOBAL.G_REPOS_ID_PARAM_NAME, SYS_GUID(), 'Unique id for the repository' ,0);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;
/


BEGIN
  UPDATE mgmt_metrics
     SET remote = 0
   WHERE remote is null;
END;
/
show errors;

Rem Create threshold policies
PROMPT Creating threshold policies
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

DECLARE
  l_tbl_metric_guid RAW(16);
  l_tbl_metric_name VARCHAR2(256);
BEGIN
  -- Get the metrics with table 
  FOR met_rec IN (SELECT DISTINCT target_type, metric_name, metric_column, metric_guid
                    FROM mgmt_metrics m
                   ORDER BY target_type, metric_name, DECODE(metric_column, ' ', 0, 1))
  LOOP

    IF (met_rec.metric_column = ' ') THEN
      l_tbl_metric_guid := met_rec.metric_guid;
      l_tbl_metric_name := met_rec.metric_name;
    ELSE

      BEGIN
         EM_POLICY.add_policy(
             p_policy_guid => met_rec.metric_guid,
             p_target_type => met_rec.target_type,
             p_policy_name => met_rec.metric_name || ' ' || met_rec.metric_column,
             p_metric_guid => l_tbl_metric_guid,
             p_policy_type => MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC,
             p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD,
             p_condition => met_rec.metric_column);
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          -- Ignore duplicates
          NULL;
      END;

    END IF;
  END LOOP;
END;
/

PROMPT Done Creating threshold policies
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;