Rem Rem $Header: upgrade_create_metrics.sql 07-mar-2007.16:13:54 ajdsouza Exp $ Rem Rem upgrade_create_metrics.sql Rem Rem Copyright (c) 2004, 2007, Oracle. All rights reserved. Rem Rem NAME Rem upgrade_create_metrics.sql - Configuration Metrics Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem !!!!!!! Please update UPGRADE script when updates are made. !!!!!!! Rem !!!!!!! !!!!!!! Rem !!!!!!! Whenever policies are created, removed or altered !!!!!!! Rem !!!!!!! Update the appropriate upgrade and downgrade files !!!!!!! Rem !!!!!!! in the v* directory with the latest version number !!!!!!! Rem Rem Rem MODIFIED (MM/DD/YY) Rem ajdsouza 03/07/07 - bug fix 5894300 Rem denath 09/28/06 - Fix 5572415.Added SET DEFINE. Rem denath 08/05/06 - Fix Bug 5390067.Added HEXTORAW to column Rem snapshot_guid before call to ecm_util.concat_col. Rem denath 08/05/06 - Fix Bug 5390067.Create metrics DB_REDO_LOG_COUNT, Rem DB_CONTROL_FILE_COUNT. SET DEFINE OFF DECLARE l_sql VARCHAR2(1000); l_snapshotList MGMT_SNAPSHOT_ARRAY; l_metricColList MGMT_METRIC_COLUMN_ARRAY; l_categoryList MGMT_CATEGORY_ARRAY; l_validIfDB MGMT_VALIDIF_ARRAY; l_10gR1MetaVersion CONSTANT VARCHAR2(3) := '3.0'; l_11gR1MetaVersion CONSTANT VARCHAR2(3) := '5.2'; l_mguid RAW(16); l_validIfList MGMT_VALIDIF_ARRAY; BEGIN -- All these metrics are configuration metrics l_categoryList := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION)); -- Use this to register metrics at the DB level, not instance level l_validIfDB := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB','NO') ) ); -- ---------------------------------------------------------- -- Repository Metric -- DB Redo Log Count -- -- Metric Name: DB_REDO_LOG_COUNT -- DB Version: All -- Agent Version: 10.1.0.2 + -- Description: -- This metric used by policies: -- 'Insufficient Number of Redo Logs' -- This metric will have 1 row for every database: -- Redo Log Count - # of redo log in the db -- Redo Log Files - a list of the redo log files -- Redo Log Group Numbers - a list of the redo log group #s -- Review Status: -- ---------------------------------------------------------- l_sql := 'SELECT ' || 'c.cnt as REDO_LOG_COUNT, ' || 'ecm_util.concat_col( ' || ' ''f.file_name'', ' || ' ''mgmt_db_redologs_ecm f'', ' || ' ''f.ecm_snapshot_id = HEXTORAW('''''' || 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 = HEXTORAW('''''' || 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 = HEXTORAW('''''' || 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 -- High Availability - Recovery Area -- -- Metric Name: HA_RECOVERY_AREA -- DB Version: 10.1 + -- Agent Version: 11gR1 + -- Description: -- This metric returns the flash recovery area location. -- Collects at the single-instance and rac database level. -- ---------------------------------------------------------- BEGIN SELECT metric_guid INTO l_mguid FROM MGMT_METRICS WHERE target_type = 'oracle_database' AND metric_name = 'HA_RECOVERY_AREA' AND metric_column = 'RECOVERY_AREA_LOCATION' AND type_meta_ver = l_11gR1MetaVersion AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN l_sql := 'SELECT p.value as RECOVERY_AREA_LOCATION, s.target_guid as TARGET_GUID FROM mgmt_db_init_params_ecm p, mgmt_ecm_gen_snapshot s WHERE p.ecm_snapshot_id = s.snapshot_guid and p.name = ''db_recovery_file_dest'' and s.is_current = ''Y'''; l_snapshotList := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => 'oracle_database', p_snapshot_name => 'oracle_dbconfig')); l_metricColList := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'RECOVERY_AREA_LOCATION', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Recovery Area Location', p_column_label_nlsid => 'RECOVERY_AREA_LOCATION')); -- Use this to register metrics at the DB level for 10.1 or higher l_validIfList := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW(p_catprop_1_choices => MGMT_CATEGORY_PROP_ARRAY('10gR1','10gR2','10gR203','11gR1','11gR2'), p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB','NO'))); -- ORACLE DATABASE MGMT_METRIC.CREATE_METRIC( p_target_type => 'oracle_database', p_metric_name => 'HA_RECOVERY_AREA', p_type_meta_ver => l_11gR1MetaVersion, 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; END; -- ---------------------------------------------------------- -- 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. -- ---------------------------------------------------------- BEGIN SELECT metric_guid INTO l_mguid FROM MGMT_METRICS WHERE target_type = 'oracle_database' AND metric_name = 'HA_INFO' AND metric_column = 'FORCE_LOGGING' AND type_meta_ver = l_11gR1MetaVersion AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN 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','10gR203','11gR1','11gR2'), p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB','NO'))); -- ORACLE DATABASE MGMT_METRIC.CREATE_METRIC( p_target_type => 'oracle_database', p_metric_name => 'HA_INFO', p_type_meta_ver => l_11gR1MetaVersion, 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; END; / SET DEFINE ON