Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/policy/upgrade_create_metrics.sql /st_emdbsa_11.2/6 2009/06/25 18:45:39 mnihalan Exp $
Rem
Rem upgrade_create_metrics.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      upgrade_create_metrics.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    mnihalan    06/24/09 - Fix bug 8629652
Rem    yozhang     06/10/09 - Fix bug 8477284: AUTO_PGA
Rem    nzhao       02/23/09 - Bug backports between
Rem                           EMDBGC_10.2.0.5.0_GENERIC_081008 and
Rem                           EMDBGC_10.2.0.5.0_GENERIC_090128 to
Rem                           EMDBSA_11.2.0.0.0_LINUX_090127
Rem    yozhang     09/25/08 - Fix bug 6442503
Rem    mnihalan    09/25/08 - Fix bug 7395749
Rem    mnihalan    05/19/08 - Fix errors
Rem    mnihalan    05/14/08 - Policy upgrade script
Rem    mnihalan    05/14/08 - Created
Rem

SET DEFINE OFF

DECLARE

  l_sql                 VARCHAR2(1000);
  l_snapshotList        MGMT_SNAPSHOT_ARRAY;
  l_metricColList       MGMT_METRIC_COLUMN_ARRAY;
  l_categoryList        MGMT_CATEGORY_ARRAY;
  l_validIfDB           MGMT_VALIDIF_ARRAY;
  l_validIfDB92         MGMT_VALIDIF_ARRAY;
  l_10gR1MetaVersion    CONSTANT VARCHAR2(3) := '3.0';
  l_11gR1MetaVersion    CONSTANT VARCHAR2(3) := '5.2';
  l_mguid               RAW(16);
  l_validIfList         MGMT_VALIDIF_ARRAY;
BEGIN

  -- All these metrics are configuration metrics
  l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION)); 

  -- Use this to register metrics at the DB level, not instance level
  l_validIfDB := MGMT_VALIDIF_ARRAY(
    MGMT_VALIDIF_OBJ.NEW(
      p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB','NO')
    )
  );

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Redo Log Count
  --    
  -- Metric Name: DB_REDO_LOG_COUNT
  -- DB Version: All
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This metric used by policies:
  --    'Insufficient Number of Redo Logs'
  --    This metric will have 1 row for every database:
  --    Redo Log Count - # of redo log in the db
  --    Redo Log Files - a list of the redo log files
  --    Redo Log Group Numbers - a list of the redo log group #s
  -- Review Status:  
  -- ----------------------------------------------------------
  
  l_sql := 'SELECT ' ||
               'c.cnt as REDO_LOG_COUNT, ' ||
               'config_util.concat_file_name( ' ||
                 ' ''file_name'', ' ||
                 ' ''mgmt_db_redologs_ecm '', ' ||
		 ' ''ecm_snapshot_id = HEXTORAW(:x) ' ||
                 ' ORDER BY group_num, file_name'', s.snapshot_guid, '','',''10'') as FILE_LIST, ' ||
               'config_util.concat_file_name( '||
                 ' ''group_num'', ' ||
                 ' ''mgmt_db_redologs_ecm'', ' ||
		 ' ''ecm_snapshot_id = HEXTORAW(:x) ' ||
                 ' ORDER BY group_num, file_name'', s.snapshot_guid, '','',''10'') as GROUP_NUM_LIST, ' ||
               's.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_ecm_gen_snapshot s, ' ||
               '(select count(*) as CNT, ecm_snapshot_id as ECM_SNAPSHOT_ID ' ||
                    'from mgmt_db_redologs_ecm group by ecm_snapshot_id) c ' ||
           'WHERE ' ||
               'c.ecm_snapshot_id = s.snapshot_guid and ' ||
               's.is_current =  ''Y'' and ' ||
               '((s.target_type = ''rac_database'') or ' ||
                '(s.target_type = ''oracle_database'' and ' ||
                 'exists ( select * from mgmt_target_properties tp ' ||
                          'where s.target_guid = tp.target_guid and ' ||
                              'tp.property_name = ''RACOption'' and ' ||
                              'tp.property_value = ''NO''))) ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'REDO_LOG_COUNT',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'REDO_LOG_COUNT',
      p_column_label_nlsid => 'REDO_LOG_COUNT'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'FILE_LIST',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'FILE_LIST',
      p_column_label_nlsid => 'FILE_LIST'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'GROUP_NUM_LIST',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'GROUP_NUM_LIST',
      p_column_label_nlsid => 'GROUP_NUM_LIST'));
 
  -- ORACLE DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_dbconfig'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_REDO_LOG_COUNT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Redo Log Count',
      p_metric_label_nlsid => 'DB_REDO_LOG_COUNT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_racconfig'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_REDO_LOG_COUNT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Redo Log Count',
      p_metric_label_nlsid => 'DB_REDO_LOG_COUNT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Control File Count
  --    
  -- Metric Name: DB_CONTROL_FILE_COUNT
  -- DB Version: All
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This metric used by policies:
  --    'Insufficient Number of Control Files'
  --    This metric will have 1 row for every database.
  --    The metric columns are:
  --    Control File Count - # of control files
  --    Control File Name - The list of control files
  -- Review Status:  
  -- ----------------------------------------------------------
  
  l_sql := 'SELECT ' ||
               'c.cnt as CONTROL_FILE_COUNT, ' ||
               'config_util.concat_file_name( ' ||
                 ' ''file_name'', ' ||
                 ' ''mgmt_db_controlfiles_ecm '', ' ||
                 ' ''ecm_snapshot_id = HEXTORAW(:x) ' ||
                 ' ORDER BY file_name'', s.snapshot_guid, '','', ''10'') as FILE_LIST, ' ||
               's.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_ecm_gen_snapshot s, ' ||
               'mgmt_db_controlfiles_ecm cf, ' ||
               '(select count(*) as CNT, ecm_snapshot_id as ECM_SNAPSHOT_ID ' ||
                    'from mgmt_db_controlfiles_ecm group by ecm_snapshot_id) c ' ||
           'WHERE ' ||
               'cf.ecm_snapshot_id = s.snapshot_guid and ' ||
               'c.ecm_snapshot_id = s.snapshot_guid and ' ||
               's.is_current =  ''Y'' and ' ||
               '((s.target_type = ''rac_database'') or ' ||
                '(s.target_type = ''oracle_database'' and ' ||
                 'exists ( select * from mgmt_target_properties tp ' ||
                          'where s.target_guid = tp.target_guid and ' ||
                              'tp.property_name = ''RACOption'' and ' ||
                              'tp.property_value = ''NO''))) ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'CONTROL_FILE_COUNT',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'CONTROL_FILE_COUNT',
      p_column_label_nlsid => 'CONTROL_FILE_COUNT'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'FILE_LIST',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'FILE_LIST',
      p_column_label_nlsid => 'FILE_LIST'));
 
  -- ORACLE DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_dbconfig'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_CONTROL_FILE_COUNT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Control File Count',
      p_metric_label_nlsid => 'DB_CONTROL_FILE_COUNT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_racconfig'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_CONTROL_FILE_COUNT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Control File Count',
      p_metric_label_nlsid => 'DB_CONTROL_FILE_COUNT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Tablespaces
  --
  -- Metric Name: DB_TABLESPACES
  -- DB Version: All
  -- Agent Version: 10gR1 (10.1.0.2 +)
  -- Description:
  --    This metric is for the 'Non-Uniform Default Extent Size for
  --    Dictionary Managed Tablespaces' (STORAGE POLICY).
  --    This metric will show the Tablespace Name, Initial Extent and
  --    Next Extent size and the Increment by setting.
  --    It only considers PERMANENT tablespaces.
  -- ----------------------------------------------------------


 l_sql := 'SELECT ' ||
               'tbsp.tablespace_name as TABLESPACE_NAME, ' ||
               'tbsp.contents as CONTENTS, ' ||
               'tbsp.extent_management as EXTENT_MANAGEMENT, ' ||
               'tbsp.allocation_type as ALLOCATION_TYPE, ' ||
               'tbsp.initial_ext_size as INITIAL_EXTENT, ' ||
               'tbsp.next_extent as NEXT_EXTENT, ' ||
               'tbsp.increment_by as PCTINCREASE, ' ||
               's.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_db_tablespaces_ecm tbsp, ' ||
               'mgmt_ecm_gen_snapshot s ' ||
           'WHERE ' ||
               'tbsp.ecm_snapshot_id = s.snapshot_guid and ' ||
               's.is_current =  ''Y'' and ' ||
               '((s.target_type = ''rac_database'') or ' ||
                 '(s.target_type = ''oracle_database'' and ' ||
                   'exists ( select * from mgmt_target_properties tp ' ||
                             'where s.target_guid = tp.target_guid and ' ||
                             'tp.property_name = ''RACOption'' and ' ||
                             'tp.property_value = ''NO''))) and ' ||
                 ' CONTENTS = ''PERMANENT'' AND ' ||
                 ' (EXTENT_MANAGEMENT = ''DICTIONARY'' OR ' ||
                 ' (EXTENT_MANAGEMENT = ''LOCAL'' AND ALLOCATION_TYPE = ''USER'')) AND '||
                 ' (increment_by !=0 OR  (initial_ext_size > 0 AND ' ||
                 '   MOD(NEXT_EXTENT,initial_ext_size) != 0)) ';

   l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'TABLESPACE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'TABLESPACE_NAME',
      p_column_label_nlsid => 'TABLESPACE_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'CONTENTS',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'CONTENTS',
      p_column_label_nlsid => 'CONTENTS'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'EXTENT_MANAGEMENT',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'EXTENT_MANAGEMENT',
      p_column_label_nlsid => 'EXTENT_MANAGEMENT'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'ALLOCATION_TYPE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'Allocation Type',
      p_column_label_nlsid => 'ALLOCATION_TYPE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'INITIAL_EXTENT',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'INITIAL_EXTENT',
      p_column_label_nlsid => 'INITIAL_EXTENT'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'NEXT_EXTENT',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'NEXT_EXTENT',
      p_column_label_nlsid => 'NEXT_EXTENT'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PCTINCREASE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'PCTINCREASE',
      p_column_label_nlsid => 'PCTINCREASE'));

  -- ORACLE DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_dbconfig'));
  MGMT_METRIC.CREATE_METRIC(
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_TABLESPACES',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Tablespaces',
      p_metric_label_nlsid => 'DB_TABLESPACES_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_racconfig'));
  MGMT_METRIC.CREATE_METRIC(
      p_target_type => 'rac_database',
      p_metric_name => 'DB_TABLESPACES',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Tablespaces',
      p_metric_label_nlsid => 'DB_TABLESPACES_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;
  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Tablespace Not Using Automatic Segment-Space Management
  --    
  --    
  -- Metric Name: DB_TBSP_SEGSPACE_MGMT       
  -- DB Version: 9.2+
  -- Agent Version: 10gR1 (10.1.0.2 +)
  -- Description: 
  --    This metric supports four currently used policies.
  --    The data in this metric is returned as Tablespace Name,
  --    Problem Code, Value1 (NUMBER) and Value2 (NUMBER).
  -- Problem Codes:
  --    1   Tablespace Not Using Automatic Segment-Space Management
  --    2   Dictionary Managed Tablespace
  --    3   Tablespace Containing Rollback and Data Segments
  --    4   (Not Used) Tbsp has non-zero pctincrease
  --    5   (Not Used) Tbsp next extent not a multiple of init extent
  --    6   Deafult Temporary Tablespace set to a System Tablespace
  --    7   Default Permanent Tablespace set to a System Tablespace
  --
  -- @RAR For Translation: 
  --          VALUE1 should read 'Number of Rollback Segments'
  --          VALUE2 should read 'Number of Data Segments'    
  --      Since the only policy to use these is 
  --      Tablespaces Containing Rollback and Data Segments
  --      this should be okay.      
  -- ----------------------------------------------------------

  l_sql := 'SELECT ' ||
               't.tablespace as TABLESPACE_NAME, ' ||
               't.problem_code as PROBLEM_CODE, ' ||
               't.value1 as VALUE1, ' ||
               't.value2 as VALUE2, ' ||
               'ss.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_db_rectssettings_ecm t, ' ||
               'mgmt_ecm_gen_snapshot ss ' ||
           'WHERE ' ||
               't.ecm_snapshot_id = ss.snapshot_guid and ' ||
               't.problem_code = 1 and ' ||
               'ss.is_current =  ''Y'' ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'TABLESPACE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'TABLESPACE_NAME',
      p_column_label_nlsid => 'TABLESPACE_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PROBLEM_CODE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'PROBLEM_CODE',
      p_column_label_nlsid => 'PROBLEM_CODE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'VALUE1',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'VALUE1',
      p_column_label_nlsid => 'VALUE1'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'VALUE2',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'VALUE2',
      p_column_label_nlsid => 'VALUE2'));

  -- ORACLE DATABASE
  -- Use this to register metrics at the DB level, not instance level, for 9.2+ dbs only
  l_validIfDB92 := MGMT_VALIDIF_ARRAY(
    MGMT_VALIDIF_OBJ.NEW(p_catprop_1_choices => MGMT_CATEGORY_PROP_ARRAY('9iR2','10gR1','10gR2','10gR203','11gR1'),
                         p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB','NO')));

  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_TBSP_SEGSPACE_MGMT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB92,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Tablespace Not Using Automatic Segment-Space Management',
      p_metric_label_nlsid => 'DB_TBSP_SEGSPACE_MGMT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_TBSP_SEGSPACE_MGMT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Tablespace Not Using Automatic Segment-Space Management',
      p_metric_label_nlsid => 'DB_TBSP_SEGSPACE_MGMT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

   -- ----------------------------------------------------------
  -- Repository Metric
  --    High Availability - Recovery Area
  --
  -- Metric Name: HA_RECOVERY_AREA
  -- DB Version: 10.1 +
  -- Agent Version: 10.1 +
  -- Description: 
  --    This metric returns the flash recovery area location.
  --    Collects at the single-instance and rac database level.
  -- ----------------------------------------------------------
  
  l_sql := 'SELECT
              p.value as RECOVERY_AREA_LOCATION,
              s.target_guid as TARGET_GUID
           FROM
              mgmt_db_init_params_ecm p,
              mgmt_ecm_gen_snapshot s
           WHERE
              p.ecm_snapshot_id = s.snapshot_guid and
              p.name = ''db_recovery_file_dest'' and
              s.is_current =  ''Y''';

  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_dbconfig'));

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'RECOVERY_AREA_LOCATION',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'Recovery Area Location',
      p_column_label_nlsid => 'RECOVERY_AREA_LOCATION'));

  -- Use this to register metrics at the DB level for 10.1 or higher
  l_validIfList := MGMT_VALIDIF_ARRAY(
    MGMT_VALIDIF_OBJ.NEW(p_catprop_1_choices => MGMT_CATEGORY_PROP_ARRAY('10gR1','10gR2','10gR203','11gR1','11gR2'),
                         p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB','NO')));

  -- ORACLE DATABASE 
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'HA_RECOVERY_AREA',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfList,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_RAW,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Recovery Area',
      p_metric_label_nlsid => 'HA_RECOVERY_AREA',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_racconfig'));

  -- RAC DATABASE 
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'HA_RECOVERY_AREA',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_RAW,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Recovery Area',
      p_metric_label_nlsid => 'HA_RECOVERY_AREA',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    High Availability - High Availability Information
  --
  -- Metric Name: HA_INFO
  -- DB Version: 9.2 +
  -- Agent Version: 10.1 +
  -- Description: 
  --    This metric returns the following HA information:
  --    force_logging, database_role, and dg_broker_start.
  --    Collects at the single-instance and rac database level.
  -- ----------------------------------------------------------
  
  l_sql :=  'SELECT 
              ha.force_logging as FORCE_LOGGING, 
              ha.database_role as DATABASE_ROLE,
              p.value as DG_BROKER_START,
              s.target_guid as TARGET_GUID 
            FROM
              mgmt_db_init_params_ecm p, 
              mgmt_ha_info_ecm ha, 
              mgmt_ecm_gen_snapshot s
            WHERE
              p.ecm_snapshot_id = ha.ecm_snapshot_id and 
              p.name = ''dg_broker_start'' and 
              p.value = ''TRUE'' and 
              ha.database_role = ''PRIMARY'' and 
              ha.force_logging = ''NO'' and 
              p.ecm_snapshot_id = s.snapshot_guid and 
              s.is_current =  ''Y'' and
              s.snapshot_type = ''oracle_dbconfig''';

  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_dbconfig'));

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'FORCE_LOGGING',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'Force Logging',
      p_column_label_nlsid => 'FORCE_LOGGING'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'DATABASE_ROLE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'Database Role',
      p_column_label_nlsid => 'DATABASE_ROLE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'DG_BROKER_START',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'Data Guard Broker',
      p_column_label_nlsid => 'DG_BROKER_START'));

  -- Use this to register metrics at the DB level for 9.2 or higher
  l_validIfList := MGMT_VALIDIF_ARRAY(
    MGMT_VALIDIF_OBJ.NEW(p_catprop_1_choices => MGMT_CATEGORY_PROP_ARRAY('9iR2','10gR1','10gR2','10gR203','11gR1','11gR2'),
                         p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB','NO')));

  -- ORACLE DATABASE 
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'HA_INFO',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfList,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_RAW,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'High Availability Information',
      p_metric_label_nlsid => 'HA_INFO',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);
  
  l_sql :=  'SELECT 
              ha.force_logging as FORCE_LOGGING, 
              ha.database_role as DATABASE_ROLE,
              p.value as DG_BROKER_START,
              s.target_guid as TARGET_GUID 
            FROM
              mgmt_db_init_params_ecm p, 
              mgmt_ha_info_ecm ha, 
              mgmt_ecm_gen_snapshot s
            WHERE
              p.ecm_snapshot_id = ha.ecm_snapshot_id and 
              p.name = ''dg_broker_start'' and 
              p.value = ''TRUE'' and 
              ha.database_role = ''PRIMARY'' and 
              ha.force_logging = ''NO'' and 
              p.ecm_snapshot_id = s.snapshot_guid and 
              s.is_current =  ''Y'' and
              s.snapshot_type = ''oracle_racconfig''';

  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_racconfig'));

  -- RAC DATABASE 
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'HA_INFO',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_RAW,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'High Availability Information',
      p_metric_label_nlsid => 'HA_INFO',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Instance Initialization Parameters for AUTO_PGA Policy
  --
  -- Metric Name: DB_INIT_PARAMS_PGA
  -- DB Version: All
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This metric returns the name, value and 
  --    is default setting of every init parameter.
  --    Collects at the database instance level.
  --    Added by rreilly for 10gR2 GC - Dec 13, 2004
  -- ----------------------------------------------------------
  
  l_sql := 'SELECT ' ||
              'p.name as INIT_PARAM_NAME, ' ||
              'p.value as INIT_PARAM_VALUE, ' ||
              'p.isdefault as INIT_PARAM_IS_DEFAULT, ' ||
              's.target_guid as TARGET_GUID ' ||
           'FROM ' ||
              'mgmt_db_init_params_ecm p, ' ||
              'mgmt_db_init_params_ecm p2, ' ||
              'mgmt_ecm_gen_snapshot s ' ||
           'WHERE ' ||
              'p.ecm_snapshot_id = s.snapshot_guid and ' ||
              'p2.ecm_snapshot_id = s.snapshot_guid and ' ||
              '(p.name=''pga_aggregate_target'' or p.name=''workarea_size_policy'') and ' ||
              '(p2.name=''memory_target'' and (p2.value=''0'' or p2.value=null)) and ' ||
              's.is_current =  ''Y''';

  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_dbconfig'));

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'INIT_PARAM_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'Name',
      p_column_label_nlsid => 'NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'INIT_PARAM_VALUE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'Value',
      p_column_label_nlsid => 'VALUE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'INIT_PARAM_IS_DEFAULT',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'Is Default',
      p_column_label_nlsid => 'IS_DEFAULT'));
 
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_INIT_PARAMS_PGA',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Instance Initialization Parameters',
      p_metric_label_nlsid => 'DB_INIT_PARAMS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

END;
/

SET DEFINE ON