Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.3/policy/upgrade_create_metrics.sql /st_emdbsa_11.2/1 2011/05/20 10:15:53 mpawelko Exp $
Rem
Rem upgrade_create_metrics.sql
Rem
Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      upgrade_create_metrics.sql - Upgrade create metrics SQL script
Rem
Rem    DESCRIPTION
Rem      During 11.2.0.0...11.2.0.3 upgrade, recreate metrics
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    mpawelko    05/17/11 - add SET DEFINE OFF/ON
Rem    mpawelko    04/28/11 - 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.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;

END;
/

SET DEFINE ON