Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/policy/config_metric_setup.sql /st_emdbsa_11.2/7 2011/05/20 10:15:53 mpawelko Exp $
Rem
Rem config_metric_setup.sql
Rem
Rem Copyright (c) 2004, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      config_metric_setup.sql - Configuration Metrics
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem
Rem      !!!!!!! Please update UPGRADE script when updates are made. !!!!!!!
Rem      !!!!!!!                                                     !!!!!!!
Rem      !!!!!!! Whenever policies are created, removed or altered   !!!!!!!
Rem      !!!!!!! Update the appropriate upgrade and downgrade files  !!!!!!!
Rem      !!!!!!! in the v* directory with the latest version number  !!!!!!!
Rem
Rem      Must be run before config_policy_setup.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    mpawelko    04/26/11 - Backport mnihalan_bug-8837510_2 from
Rem                           st_emdbgc_10.2.0.1.0
Rem    mnihalan    06/24/09 - Fix bug 8629652
Rem    yozhang     06/10/09 - Fix bug 8477284: AUTO_PGA
Rem    pbantis     10/14/08 - Bug 7358554 snapshot_type to HA_INFO.
Rem    mnihalan    09/25/08 - Fix bug 7395749
Rem    yozhang     09/24/08 - Fix bug 6510791 in 11.2DBC
Rem    mnihalan    06/24/08 - Fix compile error
Rem    mnihalan    05/07/08 - Fix bug 4689974
Rem    ajdsouza    03/18/07 - bug fix 5894300
Rem    ajdsouza    03/07/07 - bug fix 5894300
Rem    mnihalan    02/02/07 - Remove Installation of JAccelerator
Rem    denath      09/01/06 - Fix Bug 5390067.Added HEXTORAW to column
Rem                           snapshot_guid before call to ecm_util.concat_col
Rem                           for metrics DB_REDO_LOG_COUNT,
Rem                           DB_CONTROL_FILE_COUNT.
Rem    xshi        02/23/06 - forced login policy 
Rem    pbantis     10/28/05 - HA_RECOVERY_AREA change query for 10.1 Agent. 
Rem    rreilly     09/01/05 - bug 4584999 categ prop needs DB and NO 
Rem    rreilly     07/28/05 - bug 4514202 fix category props 
Rem    rreilly     07/20/05 - bug 4496454 fix versioning 
Rem    rreilly     07/14/05 - PLEASE UPDATE UPGRADE AND DOWNGRADE SCRIPTS! 
Rem    rreilly     07/01/05 - bug 4294495 check extents on migrated locally 
Rem                           managed tbsp 
Rem    rreilly     05/19/05 - Remove Key setting for count columns
Rem    rreilly     05/18/05 - 4378437 hide repository metrics
Rem    rreilly     05/18/05 - 4378424 fix snapshot to metric mapping 
Rem    pbantis     04/28/05 - Add HA metrics. 
Rem    rreilly     04/22/05 - make sure people update the upgrade scripts 
Rem    rreilly     03/21/05 - bug 4246935 convert installation metric 
Rem    rreilly     03/07/05 - bug 4221764 set category prop for metrics 
Rem    rreilly     02/15/05 - version repository metrics 
Rem    rreilly     01/24/05 - add configuration metrics
Rem    rreilly     12/13/04 - Created
Rem

SET DEFINE OFF

-- ==========================================================
--
-- Create Configuration Metrics
--
-- ==========================================================

DECLARE

  l_sql                 VARCHAR2(1000);
  l_sql2                VARCHAR2(1000);
  l_snapshotList        MGMT_SNAPSHOT_ARRAY;
  l_metricColList       MGMT_METRIC_COLUMN_ARRAY;
  l_categoryList        MGMT_CATEGORY_ARRAY;
  l_validIfDB           MGMT_VALIDIF_ARRAY;
  l_validIfList         MGMT_VALIDIF_ARRAY;
  l_10gR1MetaVersion    CONSTANT VARCHAR2(3) := '3.0';
  l_10gR2MetaVersion    CONSTANT VARCHAR2(3) := '4.1';
 
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 Instance Initialization Parameters
  --
  -- Metric Name: DB_INIT_PARAMS
  -- 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_ecm_gen_snapshot s ' ||
           'WHERE ' ||
              'p.ecm_snapshot_id = s.snapshot_guid 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',
      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;

  -- ----------------------------------------------------------
  -- 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 Redo Logs
  --    
  -- Metric Name: DB_REDO_LOGS
  -- DB Version: All
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This metric used by policy:
  --    'Insufficient Redo Log Size' (STORAGE POLICY)
  -- Review Status:  
  -- ----------------------------------------------------------
  
  l_sql := 'SELECT ' ||
              'f.group_num as GROUP_NUM, ' ||
              'f.file_name as FILE_NAME, ' ||
              'f.logsize/1024/1024 as FILE_SIZE_MB, ' ||
              's.target_guid as TARGET_GUID ' ||
           'FROM ' ||
              'mgmt_db_redologs_ecm f, ' ||
              'mgmt_ecm_gen_snapshot s ' ||
           'WHERE ' ||
              'f.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 => 'GROUP_NUM',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'GROUP_NUM',
      p_column_label_nlsid => 'GROUP_NUM'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'FILE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'FILE_NAME',
      p_column_label_nlsid => 'FILE_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'FILE_SIZE_MB',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'FILE_SIZE_MB',
      p_column_label_nlsid => 'FILE_SIZE_MB'));
 
  -- 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_LOGS',
      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 Logs',
      p_metric_label_nlsid => 'DB_REDO_LOGS_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_LOGS',
      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 Logs',
      p_metric_label_nlsid => 'DB_REDO_LOGS_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 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.redo_file_list( ' ||
                 ' s.snapshot_guid) as FILE_LIST, ' ||
               'config_util.redo_group_num_list( '||
                 's.snapshot_guid) 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.control_file_list( ' ||
                 's.snapshot_guid ) 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
  --    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 10.2 Agent
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'HA_RECOVERY_AREA',
      p_type_meta_ver => l_10gR2MetaVersion,
      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);

  -- Use MGMT_HA_INIT_PARAMS_ECM.
  l_sql2 := 'SELECT
              p.value as RECOVERY_AREA_LOCATION,
              s.target_guid as TARGET_GUID
           FROM
              mgmt_ha_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''';

  -- RAC DATABASE 10.1 Agent
  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_sql2,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList,
      p_end_type_meta_ver => l_10gR1MetaVersion);

  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 - 10.2 Agent
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'HA_INFO',
      p_type_meta_ver => l_10gR2MetaVersion,
      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);

  -- For RAC DATABASE 10.1 Agent
  l_sql2 := '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_ha_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''';

  -- RAC DATABASE 10.1 Agent
  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_sql2,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList,
      p_end_type_meta_ver => l_10gR1MetaVersion);


  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