Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/policy/storage_metric_setup.sql /st_emdbsa_11.2/2 2008/09/30 06:23:37 yozhang Exp $ Rem Rem storage_metric_setup.sql Rem Rem Copyright (c) 2004, 2008, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem storage_metric_setup.sql - Storage 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 Must be run before storage_policy_setup.sql Rem Rem MODIFIED (MM/DD/YY) Rem yozhang 09/25/08 - Add versions to ASSM metric Rem mnihalan 09/15/08 - Fix bug 7359004 Rem rreilly 09/01/05 - bug 4584999 categ prop needs DB and NO Rem rreilly 07/28/05 - bug 4514202 fix category props Rem rreilly 07/20/05 - bug 4496454 fix versioning Rem rreilly 07/14/05 - PLEASE UPDATE UPGRADE AND DOWNGRADE SCRIPTS! Rem rreilly 06/10/05 - bug 4338810 bad reorg wizard link Rem rreilly 05/18/05 - 4378437 hide repository metrics Rem rreilly 05/18/05 - 4378424 fix snapshot to metric mapping Rem rreilly 04/22/05 - make sure people update the upgrade scripts 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 Storage Metrics Rem rreilly 12/13/04 - Created Rem -- SET ECHO ON -- SET FEEDBACK 1 -- SET NUMWIDTH 10 -- SET LINESIZE 80 -- SET TRIMSPOOL ON -- SET TAB OFF -- SET PAGESIZE 100 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'' ' || 'and ss.target_type in (''oracle_database'', ''rac_database'') ' || 'and ss.snapshot_type = ''oracle_storage'' '; 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','10gR203','11gR1','11gR2'), 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