Rem Rem $Header: config_metric_setup.sql 14-jul-2005.13:37:22 rreilly Exp $ Rem Rem config_metric_setup.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem config_metric_setup.sql - Configuration Metrics Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem !!!!!!! Please update UPGRADE script when updates are made. !!!!!!! Rem Rem Must be run before config_policy_setup.sql Rem Rem MODIFIED (MM/DD/YY) Rem xshen 06/07/05 - xshen_bug-4411021 Rem xchen 06/07/05 - bug 4411021 upgrade changes for beta 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_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') ) ); -- ---------------------------------------------------------- -- 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 -- Review Status: Sent for review -- ---------------------------------------------------------- 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.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 => '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_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); 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_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); 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_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); 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_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); 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_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); 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')), MGMT_VALIDIF_OBJ.NEW(p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB'))); -- 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_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); 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')), MGMT_VALIDIF_OBJ.NEW(p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB'))); -- 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_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); COMMIT; END; / SET DEFINE ON