Rem Rem $Header: upgrade_create_metrics.sql 01-sep-2005.13:16:36 rreilly Exp $ Rem Rem upgrade_create_metrics.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem upgrade_create_metrics.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rreilly 09/01/05 - bug 4584999 need DB and NO for validIf Rem rreilly 08/16/05 - bug4541936 only use drop create Rem rreilly 08/04/05 - bug 4534249 add set DEFINE ON Rem rreilly 07/30/05 - rreilly_bug-4520660 Rem rreilly 07/29/05 - Created Rem -- Create Metrics -- -- Metrics must be created before policies. -- Currently the create scripts are a copy/paste of the -- metric and policy scripts found in latest. -- The only difference is that configuration and storage -- are both handled in the same file. -- To find the configuration section search for CONFIGURATION, -- To find the storage section search for STORAGE. -- -- ---------------------------------------------------------- -- ---------------------------------------------------------- -- CONFIGURATION -- ---------------------------------------------------------- -- ---------------------------------------------------------- SET DEFINE OFF -- ========================================================== -- -- Create Configuration Metrics -- -- ========================================================== 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_validIfList MGMT_VALIDIF_ARRAY; l_10gR1MetaVersion CONSTANT VARCHAR2(3) := '3.0'; 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 Instance SGA_TARGET Init Param -- -- Metric Name: DB_SGA_TARGET_INIT_PARAM -- DB Version: 10gR1+ -- (works for all vers, but policy applicable to 10gR1+) -- Agent Version: 10.1.0.2 + -- Description: -- This metric is specific for the 'Not Using Automatic Shared -- Memory Management' Policy. It returns one row with 4 columns: -- the value of 'sga_target', the value of 'sga_max_size' -- and the value of 'statistics_level', and the target_guid. -- Added by rreilly for 10gR2 GC - Dec 15, 2004 -- Review Status: -- ---------------------------------------------------------- l_sql := 'SELECT ' || 'p.value as SGA_TARGET_VALUE, ' || 'm.value/1024/1024 as SGA_MAX_SIZE_MB_VALUE, ' || 'l.value as STATISTICS_LEVEL_VALUE, ' || 's.target_guid as TARGET_GUID ' || 'FROM ' || 'mgmt_db_init_params_ecm p, ' || 'mgmt_ecm_gen_snapshot s, ' || '(SELECT p.value, ' || 's.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'' ' || 'AND p.name = ''sga_max_size'') m, ' || '(SELECT p.value, ' || 's.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'' '|| 'AND p.name = ''statistics_level'') l ' || 'WHERE ' || 'p.ecm_snapshot_id = s.snapshot_guid and ' || 's.is_current = ''Y'' and ' || 's.target_guid = m.target_guid and ' || 's.target_guid = l.target_guid and ' || 'p.name = ''sga_target'' and ' || 'p.value = ''0'''; 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 => 'SGA_TARGET_VALUE', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'SGA_TARGET', p_column_label_nlsid => 'SGA_TARGET'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'SGA_MAX_SIZE_MB_VALUE', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'SGA_MAX_SIZE_MB', p_column_label_nlsid => 'SGA_MAX_SIZE_MB'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'STATISTICS_LEVEL_VALUE', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'STATISTICS_LEVEL', p_column_label_nlsid => 'STATISTICS_LEVEL')); MGMT_METRIC.CREATE_METRIC( p_target_type => 'oracle_database', p_metric_name => 'DB_SGA_TARGET_INIT_PARAM', 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 SGA_TARGET Initialization Parameter', p_metric_label_nlsid => 'DB_SGA_TARGET_INIT_PARAM_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''))) '; 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 Optimizer Version -- -- Metric Name: DB_OPTIMIZER_VERSION -- DB Version: All -- Agent Version: 10.1.0.2 + -- Description: -- This metric returns the name, value and -- target database version. If the latest -- optimizer version is not being used, this -- metric will return one row. -- Review Status: -- ---------------------------------------------------------- l_sql := 'SELECT ' || 'p.name as INIT_PARAM_NAME, ' || 'p.value as INIT_PARAM_VALUE, ' || 'db.property_value as DB_VERSION, ' || 's.target_guid as TARGET_GUID ' || 'FROM ' || 'mgmt_db_init_params_ecm p, ' || 'mgmt_ecm_gen_snapshot s, ' || 'mgmt_target_properties db ' || 'WHERE ' || 'p.ecm_snapshot_id = s.snapshot_guid and ' || 's.is_current = ''Y'' and ' || 's.target_guid = db.target_guid and ' || 'db.property_name=''DBVersion'' and ' || 'p.name = ''optimizer_features_enable'' and ' || 'db.property_value not like p.value || ''%'' '; 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 => 'INIT_PARAM_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 => 'INIT_PARAM_VALUE', p_column_label_nlsid => 'VALUE'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'DB_VERSION', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'DB_VERSION', p_column_label_nlsid => 'DB_VERSION')); MGMT_METRIC.CREATE_METRIC( p_target_type => 'oracle_database', p_metric_name => 'DB_OPTIMIZER_VERSION', 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 Optimizer Version', p_metric_label_nlsid => 'DB_OPTIMIZER_VERSION_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, ' || 'ecm_util.concat_col( ' || ' ''f.file_name'', ' || ' ''mgmt_db_redologs_ecm f'', ' || ' ''f.ecm_snapshot_id = '''''' || s.snapshot_guid || '''''' ' || ' ORDER BY f.group_num, f.file_name'', '','', ''10'') as FILE_LIST, ' || 'ecm_util.concat_col( '|| ' ''f.group_num'', ' || ' ''mgmt_db_redologs_ecm f'', ' || ' ''f.ecm_snapshot_id = '''''' || s.snapshot_guid || '''''' ' || ' ORDER BY f.group_num, f.file_name'', '','', ''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, ' || 'ecm_util.concat_col( ' || ' ''cf.file_name'', ' || ' ''mgmt_db_controlfiles_ecm cf'', ' || ' ''cf.ecm_snapshot_id = '''''' || s.snapshot_guid || '''''' ' || ' ORDER BY cf.file_name'', '','', ''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 ncomp JAccelerator Installation -- -- Metric Name: DB_INSTALL_NCOMPS -- DB Version: All -- Agent Version: -- Description: -- This metric is for the following policy: -- 'Installation of JAccelerator (NCOMP)' -- NOTES FROM Shyam: -- This metric checks for ncomp Oracle JAccelerator installation. -- Ideally we should also be checking to see if the target db is using java -- source/class, for that we would need to define a metric to collect -- that info as part of db config snapshot. for now, we shall just check -- the installation info in the ecm software components info in repository -- NOTE: This Metric Uses the host Inventory Snapshot -- The Test: -- Major Version must be >= 10 -- ---------------------------------------------------------- l_sql := 'SELECT ' || 'h.container_location || '' ('' || h.container_name || '')'' AS ORACLE_HOME, ' || 'TO_NUMBER(SUBSTR( rc.version,0, INSTR(rc.version, ''.'')-1)) as MAJOR_VERSION, ' || 'h.container_location AS HOME_LOCATION, ' || 't.host_name AS HOST_NAME, ' || 't.target_guid as TARGET_GUID ' || 'FROM ' || 'mgmt_targets t, ' || 'mgmt_target_properties p, ' || 'mgmt_ecm_snapshot s, ' || 'mgmt_inv_container h, ' || 'mgmt_target_type_component_map map, ' || 'mgmt_inv_component rc ' || 'WHERE t.target_guid = p.target_guid ' || 'AND p.property_name = ''OracleHome'' ' || 'AND p.property_value=h.container_location ' || 'AND s.target_name = t.host_name ' || 'AND s.target_type = ''host'' ' || 'AND s.is_current = ''Y'' ' || 'AND s.snapshot_type = ''host_configuration'' ' || 'AND h.snapshot_guid = s.snapshot_guid ' || 'AND map.target_type = t.target_type ' || 'AND rc.name = map.component_name ' || 'AND rc.container_guid = h.container_guid ' || 'AND NOT EXISTS ( select * from mgmt_inv_component c ' || 'where h.container_guid = c.container_guid ' || 'AND c.name = ''oracle.java.javavm.ncomp'' ' || 'AND h.container_location = p.property_value )'; -- -- NOTE: This Metric Uses the host Inventory Snapshot -- l_snapshotList := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => 'host', p_snapshot_name => 'Inventory')); l_metricColList := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'ORACLE_HOME', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'ORACLE_HOME', p_column_label_nlsid => 'ORACLE_HOME'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'MAJOR_VERSION', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'MAJOR_VERSION', p_column_label_nlsid => 'MAJOR_VERSION'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'HOME_LOCATION', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'HOME_LOCATION', p_column_label_nlsid => 'HOME_LOCATION'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'HOST_NAME', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'HOST_NAME', p_column_label_nlsid => 'HOST_NAME')); -- ORACLE DATABASE MGMT_METRIC.CREATE_METRIC( p_target_type => 'oracle_database', p_metric_name => 'DB_INSTALL_NCOMPS', p_type_meta_ver => CPF_POLICY.HOST_TYPE_META_VERSION, 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 => 'DB JAccelerator Installation (NCOMP)', p_metric_label_nlsid => 'DB_INSTALL_NCOMPS_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 MGMT_METRIC.CREATE_METRIC( p_target_type => 'rac_database', p_metric_name => 'DB_INSTALL_NCOMPS', p_type_meta_ver => CPF_POLICY.HOST_TYPE_META_VERSION, p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'DB JAccelerator Installation (NCOMP)', p_metric_label_nlsid => 'DB_INSTALL_NCOMPS_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'), 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'''; 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'), 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_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; -- ---------------------------------------------------------- -- ---------------------------------------------------------- -- START New Metrics: -- 10.2 GC Post BETA Metrics -- ---------------------------------------------------------- -- ---------------------------------------------------------- -- ---------------------------------------------------------- -- Repository Metric -- DB Instance Initialization Parameters (Hidden) -- -- Metric Name: DB_HIDDEN_INIT_PARAMS -- DB Version: All -- Agent Version: 10.1.0.2 + -- Description: -- This metric returns the name, value and -- is default setting of every hidden init parameter. -- Hidden init params start with _. -- Collects at the database instance level. -- Added by rreilly 10gR2 GC - July 29, 2005 -- ---------------------------------------------------------- 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'' and ' || 'p.name like ''\_%'' ESCAPE ''\'' '; 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_HIDDEN_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 Hidden 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 New Metrics: -- 10.2 GC Post BETA Metrics -- ---------------------------------------------------------- -- ---------------------------------------------------------- END; / SET DEFINE ON -- ---------------------------------------------------------- -- ---------------------------------------------------------- -- STORAGE -- ---------------------------------------------------------- -- ---------------------------------------------------------- SET DEFINE OFF -- ========================================================== -- -- Create Storage Metrics -- -- ========================================================== 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_ASM10gR1MetaVersion CONSTANT VARCHAR2(3) := '2.0'; BEGIN -- All these metrics are storage 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_STORAGE)); -- 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 Rollback Segments in SYSTEM Tablespace -- -- Metric Name: DB_RBS_IN_SYSTEM -- DB Version: All -- Agent Version: 10.1.0.2 + -- Description: -- This metric is specific for the 'Rollback in SYSTEM -- Tablespace' Policy. If the 'undo_management' init param -- is set to 'MANUAL' then this metric should return a row -- for each rollback segment that is in the SYSTEM tablespace -- The rollback segment name will be returned. -- NOTE: Exclude default SYSTEM rollback segments -- Check undo_management='MANUAL' OR db version is pre-9i -- @BUG: currently do_management is only -- available at the instance level (init params are being collected at the -- instance level), but for RAC rollback segs should be -- handled at the db level (rac_database). -- ---------------------------------------------------------- l_sql := 'SELECT ' || 'r.rollname as RBS_NAME, ' || 'r.tablespace_name as TABLESPACE_NAME, ' || 's.target_guid as TARGET_GUID ' || 'FROM ' || 'mgmt_db_rollback_segs_ecm r, ' || 'mgmt_db_init_params_ecm p, ' || 'mgmt_ecm_gen_snapshot s ' || 'WHERE ' || 'r.tablespace_name = ''SYSTEM'' and ' || 'r.rollname != ''SYSTEM'' and ' || 'r.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 ' || 'r.ecm_snapshot_id = p.ecm_snapshot_id (+) and ' || 'p.name (+) = ''undo_management'' and ' || 'upper(nvl(p.value,''MANUAL'')) = ''MANUAL'' '; l_metricColList := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'RBS_NAME', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'RBS_NAME', p_column_label_nlsid => 'RBS_NAME'), 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_FALSE, p_column_label => 'TABLESPACE_NAME', p_column_label_nlsid => 'TABLESPACE_NAME')); -- 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_RBS_IN_SYSTEM', 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 Rollback Segment in SYSTEM Tablespace', p_metric_label_nlsid => 'DB_RBS_IN_SYSTEM_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_RBS_IN_SYSTEM', 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 Rollback Segment in SYSTEM Tablespace', p_metric_label_nlsid => 'DB_RBS_IN_SYSTEM_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 Recommended User Settings -- -- Metric Name: DB_REC_USER_SETTINGS -- DB Version: All -- Agent Version: 10.1.0.2 + -- Description: -- This metric is for the 'Users with Permanent Tablespaces -- as Temporary Tablespace' and 'Users with a System Tablespace -- as Default Tablespace'. -- The data in this metric is returned as User Name, -- Problem Code and Tablespace Name. -- Problem Codes: -- 1 Users with Permanent Tablespace as Temporary Tablespace -- 2 Users with a System Tablespace as Default Tablespace -- ---------------------------------------------------------- l_sql := 'SELECT ' || 'u.user_name as USER_NAME, ' || 'u.problem_code as PROBLEM_CODE, ' || 'u.tablespace as TABLESPACE_NAME, ' || 'ss.target_guid as TARGET_GUID ' || 'FROM ' || 'mgmt_db_recusersettings_ecm u, ' || 'mgmt_ecm_gen_snapshot ss ' || 'WHERE ' || 'u.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' '; l_metricColList := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'USER_NAME', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'USER_NAME', p_column_label_nlsid => 'USER_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 => 'TABLESPACE_NAME', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'TABLESPACE_NAME', p_column_label_nlsid => 'TABLESPACE_NAME')); -- ORACLE DATABASE 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_REC_USER_SETTINGS', 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 User Settings', p_metric_label_nlsid => 'DB_REC_USER_SETTINGS_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_REC_USER_SETTINGS', 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 Recommended User Settings', p_metric_label_nlsid => 'DB_REC_USER_SETTINGS_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 Recommended Tablespace Settings -- -- Metric Name: DB_REC_TS_SETTINGS -- DB Version: All -- 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 ' || '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 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_REC_TS_SETTINGS', 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 Recommended Tablespace Settings', p_metric_label_nlsid => 'DB_REC_TS_SETTINGS_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_REC_TS_SETTINGS', 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 Recommended Tablespace Settings', p_metric_label_nlsid => 'DB_REC_TS_SETTINGS_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); -- ---------------------------------------------------------- -- 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'), 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 -- DB Recommended Segment Settings -- -- Metric Name: DB_REC_SEGMENT_SETTINGS -- DB Version: All -- Agent Version: 10gR1 (10.1.0.2 +) -- Description: -- This metric is used for two policies: -- 'Non-System Data Segments in a System Tablespaces' -- 'Segment in Dictionary Tablespace with Extent Growth Policy Violation' -- This metric will show the Tablespace Name, Segment Type, Lob Col, -- Partition Name, Object Name and Object Owner. -- This policy Excludes CLUSTER obj types (obj_type=3) -- because reorg does not support them. -- ---------------------------------------------------------- l_sql := 'SELECT ' || 's.obj_owner || ''.'' || s.obj_name as OBJECT, ' || 'DECODE(s.obj_type, 1, ''TABLE'', 2, ''INDEX'', 3, ''CLUSTER'', ' || ' ''UNHANDLED TYPE '' || s.obj_type) as OBJECT_TYPE, ' || 's.obj_owner as OBJECT_OWNER, ' || 's.obj_name as OBJECT_NAME, ' || 's.obj_partition as PARTITION, ' || 's.obj_lob_col as LOB_COL, ' || 'decode( s.segment_type, 1, ''TABLE'', 2, ''INDEX'', ' || '3, ''TABLE PARTITION'', 4, ''INDEX PARTITION'', ' || '5, ''IOT OVERFLOW'', 6, ''LOB INDEX'', 7, ''LOB COLUMN'', ' || '8, ''LOB PARTITION'', 9, ''TABLE SUBPARTITION'', ' || '10, ''INDEX SUBPARTITION'', 11, ''LOB SUBPARTITION'', ' || '12, ''CLUSTER'', 13, ''IOT OVERFLOW PARTITION'', ''UNKNOWN'') ' || 'as SEGMENT_TYPE, ' || 's.tablespace as TABLESPACE_NAME, ' || 'decode(decode(s.segment_type,9,0,10,0,11,0,1), 0, NULL, ' || '1, decode(s.obj_partition, '' '', NULL, s.obj_partition)) ' || 'as PTN_PARAM, ' || 's.problem_code as PROBLEM_CODE, ' || 'ss.target_guid as TARGET_GUID ' || 'FROM ' || 'mgmt_db_recsegmentsettings_ecm s, ' || 'mgmt_ecm_gen_snapshot ss ' || 'WHERE ' || 's.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' and ' || 's.obj_type != 3 '; l_metricColList := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'OBJECT', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'OBJECT', p_column_label_nlsid => 'OBJECT'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'OBJECT_TYPE', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'OBJECT_TYPE', p_column_label_nlsid => 'OBJECT_TYPE'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'OBJECT_OWNER', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'OBJECT_OWNER', p_column_label_nlsid => 'OBJECT_OWNER'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'OBJECT_NAME', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'OBJECT_NAME', p_column_label_nlsid => 'OBJECT_NAME'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'PARTITION', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'PARTITION', p_column_label_nlsid => 'PARTITION'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'LOB_COL', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'LOB_COL', p_column_label_nlsid => 'LOB_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'SEGMENT_TYPE', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'SEGMENT_TYPE', p_column_label_nlsid => 'SEGMENT_TYPE'), 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_FALSE, p_column_label => 'TABLESPACE_NAME', p_column_label_nlsid => 'TABLESPACE_NAME'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'PTN_PARAM', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'PTN_PARAM', p_column_label_nlsid => 'PTN_PARAM'), 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')); -- ORACLE DATABASE 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_REC_SEGMENT_SETTINGS', 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 Recommended Segment Settings', p_metric_label_nlsid => 'DB_REC_SEGMENT_SETTINGS_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_REC_SEGMENT_SETTINGS', 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 Recommended Segment Settings', p_metric_label_nlsid => 'DB_REC_SEGMENT_SETTINGS_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 -- ASM Instance Disk Group Settings -- -- Metric Name: DB_ASM_DISK_GROUP_SETTINGS -- ASM DB Versions: 10gR1, 10gR2 -- Agent Version: 10.2 DB osm metadata needed -- Description: -- This metric is for ASM Database Disk Group Policies. -- Problem Codes: -- 1 Disk Group Contains Disks of Significantly Different Sizes -- 2 Disk Group Depends on External Redundancy and has Unprotected Disks -- 3 Disk Group Contains Disks with Different Redundancy Attributes -- 4 Disk Group with NORMAL or HIGH Redundancy has Mirrored or Parity Protected Disks -- ---------------------------------------------------------- l_sql := 'SELECT ' || 'asm.DISK_GROUP as DISK_GROUP_NAME, ' || 'asm.problem_code as PROBLEM_CODE, ' || 'asm.value1_n as VALUE1_N, ' || 'ss.target_guid as TARGET_GUID ' || 'FROM ' || 'mgmt_osm_disk_group_ecm asm, ' || 'mgmt_ecm_gen_snapshot ss ' || 'WHERE ' || 'asm.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' '; l_snapshotList := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => 'osm_instance', p_snapshot_name => 'oracle_osm')); l_metricColList := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'DISK_GROUP_NAME', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'DISK_GROUP_NAME', p_column_label_nlsid => 'DISK_GROUP_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_N', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'VALUE1_N', p_column_label_nlsid => 'VALUE1_N')); -- OSM INSTANCE MGMT_METRIC.CREATE_METRIC( p_target_type => 'osm_instance', p_metric_name => 'DB_ASM_DISK_GROUP_SETTINGS', p_type_meta_ver => l_ASM10gR1MetaVersion, p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'ASM Instance Disk Group Settings', p_metric_label_nlsid => 'DB_ASM_DISK_GROUP_SETTINGS_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