Rem Rem $Header: upgrade_create_policies.sql 17-aug-2005.09:17:24 rreilly Exp $ Rem Rem upgrade_create_policies.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem upgrade_create_policies.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rreilly 08/17/05 - bug4521970 mark ncomp page as uix Rem rreilly 08/16/05 - rreilly_bug_4541936_upgrade Rem rreilly 08/16/05 - Created Rem -- Create Policies -- -- 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 Policies -- -- ========================================================== DECLARE l_conditionSql VARCHAR2(1000); l_categoryList MGMT_CATEGORY_ARRAY; l_defaultSettingsList MGMT_POLICY_KEY_VAL_ARRAY; l_violCtxtList MGMT_VIOL_CTXT_DEF_ARRAY; l_paramList MGMT_POLICY_PARAM_DEF_ARRAY; l_defParamValList MGMT_POLICY_PARAM_VAL_ARRAY; l_10gR1MetaVersion CONSTANT VARCHAR2(3) := '3.0'; BEGIN -- ---------------------------------------------------------- -- Policy: Not Using Automatic Undo Management -- Policy ID: NO_UNDO_TBSP -- Uses Metric: DB_INIT_PARAMS -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: 9.2 + -- Agent Version: 10.1.0.2 + -- Description: -- This policy is considered to be in violation if -- the init param 'undo_management' has a value of -- 'MANUAL'. -- Added by rreilly for 10gR2 GC - Dec 14, 2004 -- Review Status: Sent for review -- ---------------------------------------------------------- l_conditionSql := 'upper(nvl(:INIT_PARAM_VALUE, ''MANUAL'')) = ''MANUAL'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('undo_management',0,'INIT_PARAM_NAME')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_NAME', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=undo_management&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_VALUE')); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'NO_UNDO_TBSP', p_metric_name => 'DB_INIT_PARAMS', p_policy_label_nlsid => 'NO_UNDO_TBSP_NAME', p_description => 'NO_UNDO_TBSP_DESC', p_description_nlsid => 'NO_UNDO_TBSP_DESC', p_impact => 'NO_UNDO_TBSP_IMPACT', p_impact_nlsid => 'NO_UNDO_TBSP_IMPACT', p_recommendation => 'NO_UNDO_TBSP_RECOM', p_recommendation_nlsid => 'NO_UNDO_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database is not using automatic undo management. This can cause unnecessary contention and performance issues.', p_message_nlsid => 'NO_UNDO_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'NO_UNDO_TBSP'); COMMIT; -- ---------------------------------------------------------- -- Policy: Not Using Spfile -- Policy ID: NO_SPFILE -- Uses Metric: DB_INIT_PARAMS -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: 10gR1 + -- Agent Version: 10.1.0.2 + -- Description: -- This policy is considered to be in violation if -- the init param 'spfile' has a value of NULL. -- Added by rreilly for 10gR2 GC - Dec 15, 2004 -- ---------------------------------------------------------- l_conditionSql := ':INIT_PARAM_VALUE IS NULL'; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('spfile',0,'INIT_PARAM_NAME')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_NAME', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=spfile&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_VALUE')); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'NO_SPFILE', p_metric_name => 'DB_INIT_PARAMS', p_policy_label_nlsid => 'NO_SPFILE_NAME', p_description => 'NO_SPFILE_DESC', p_description_nlsid => 'NO_SPFILE_DESC', p_impact => 'NO_SPFILE_IMPACT', p_impact_nlsid => 'NO_SPFILE_IMPACT', p_recommendation => 'NO_SPFILE_RECOM', p_recommendation_nlsid => 'NO_SPFILE_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database is not using spfile. The changes made using ALTER SYSTEM commands will not persist.', p_message_nlsid => 'NO_SPFILE_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'NO_SPFILE'); COMMIT; -- ---------------------------------------------------------- -- Policy: Use of Non-Standard Initialization Parameters -- Policy ID: HIDDEN_PARAMS -- Uses Metric: DB_HIDDEN_INIT_PARAMS -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: 10gR1 + -- Agent Version: 10.1.0.2 + -- Description: -- This policy is considered to be in violation if -- the there are any init params that start with a -- single underscore (_). Parameters that start w/ -- two underscores (__) are excluded from this check. -- This is because the 10gR2 db uses two underscores -- to id parameters that being used to save values. -- Added by rreilly for 10gR2 GC - Dec 15, 2004 -- To Test: add one of the following init params to init.ora -- __shared_pool_size, __db_cache_size, __java_pool_size, -- __large_pool_size -- Then add a _ param (these can be seen in x$ksppi -- _ash_dummy_test_param=3 is a good example -- ---------------------------------------------------------- l_conditionSql := ':INIT_PARAM_NAME not like ''\_\_%'' ESCAPE ''\'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_NAME', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=^INIT_PARAM_NAME^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_VALUE')); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'HIDDEN_PARAMS', p_metric_name => 'DB_HIDDEN_INIT_PARAMS', p_policy_label_nlsid => 'HIDDEN_PARAMS_NAME', p_description => 'HIDDEN_PARAMS_DESC', p_description_nlsid => 'HIDDEN_PARAMS_DESC', p_impact => 'HIDDEN_PARAMS_IMPACT', p_impact_nlsid => 'HIDDEN_PARAMS_IMPACT', p_recommendation => 'HIDDEN_PARAMS_RECOM', p_recommendation_nlsid => 'HIDDEN_PARAMS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database is using non-standard initialization parameter %init_param_name%, that has a value of %init_param_value%. Using non-standard initialization parameters may cause database problems that are difficult to diagnose.', p_message_nlsid => 'HIDDEN_PARAMS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'HIDDEN_PARAMS'); COMMIT; -- ---------------------------------------------------------- -- Policy: Not Using Automatic Shared Memory Management -- Uses Metric: DB_SGA_TARGET_INIT_PARAM -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: 10gR1 + -- Agent Version: 10.1.0.2 + -- Description: -- This policy is considered to be in violation if -- the init param 'sga_target' = 0. To correct the problem -- users need to set sga_target to a number > 0 and -- < sga_max_size. statistics_level should be set to 'TYPICAL' -- when automatic shared memory management is being used. -- Added by rreilly for 10gR2 GC - Dec 15, 2004 -- Review Status: -- ---------------------------------------------------------- l_paramList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'SGA_TARGET_TR', param_name_nlsid => 'SGA_TARGET_TR_NLSID', param_type => MGMT_POLICY.G_PARAM_TYPE_STRING)); l_defParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'SGA_TARGET_TR', p_info_threshold=>'0')); 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defParamValList)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'SGA_TARGET_VALUE', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=sga_target&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'SGA_MAX_SIZE_MB_VALUE', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=sga_max_size&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'STATISTICS_LEVEL_VALUE', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=statistics_level&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'SGA_TARGET', p_metric_name => 'DB_SGA_TARGET_INIT_PARAM', p_policy_label_nlsid => 'SGA_TARGET_NAME', p_description => 'SGA_TARGET_DESC', p_description_nlsid => 'SGA_TARGET_DESC', p_impact => 'SGA_TARGET_IMPACT', p_impact_nlsid => 'SGA_TARGET_IMPACT', p_recommendation => 'SGA_TARGET_RECOM', p_recommendation_nlsid => 'SGA_TARGET_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD, p_condition => 'SGA_TARGET_VALUE', p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_EQ, p_message => 'The database is not using Automatic Shared Memory Management. It simplifies the configuration and management of the SGA.', p_message_nlsid => 'SGA_TARGET_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_policy_param_list => l_paramList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'SGA_TARGET'); COMMIT; -- ---------------------------------------------------------- -- Policy: STATISTICS_LEVEL Set to All -- Policy ID: STATISTICS_LEVEL_HIGH -- Uses Metric: DB_INIT_PARAMS -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: 10gR1 + -- Agent Version: 10.1.0.2 + -- Added In: 10.2 DB Control -- Description: -- This policy is considered to be in violation if -- the init param 'statistics_level' has a value of -- 'ALL'. This means the user is collecting more -- statistics than are necessary, which may lead to perf -- problems with the datbase. -- Added by rreilly for 10gR2 GC - Dec 14, 2004 -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':INIT_PARAM_VALUE = ''ALL'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('statistics_level',0,'INIT_PARAM_NAME')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_NAME', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=statistics_level&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_VALUE')); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'STATISTICS_LEVEL_HIGH', p_metric_name => 'DB_INIT_PARAMS', p_policy_label_nlsid => 'STATISTICS_LEVEL_HIGH_NAME', p_description => 'STATISTICS_LEVEL_HIGH_DESC', p_description_nlsid => 'STATISTICS_LEVEL_HIGH_DESC', p_impact => 'STATISTICS_LEVEL_HIGH_IMPACT', p_impact_nlsid => 'STATISTICS_LEVEL_HIGH_IMPACT', p_recommendation => 'STATISTICS_LEVEL_HIGH_RECOM', p_recommendation_nlsid => 'STATISTICS_LEVEL_HIGH_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'STATISTICS_LEVEL is set to ALL. Unnesessary timed OS and plan execution statistics are being collected. This creates additional overhead on the system.', p_message_nlsid => 'STATISTICS_LEVEL_HIGH_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'STATISTICS_LEVEL_HIGH'); COMMIT; -- ---------------------------------------------------------- -- Policy: Disabled Automatic Statistics Collection -- Policy ID: STATISTICS_LEVEL -- Uses Metric: DB_INIT_PARAMS -- -- Category: Configuration -- Violation Level: Critical -- DB Versions: 10gR1 + -- Agent Version: 10.1.0.2 + -- Added in: 10.2 DB Control -- Description: -- This policy is considered to be in violation if -- the init param 'statistics_level' has a value of -- 'BASIC'. This means the user is not collecting the -- statistics that oracle needs, which may lead to -- problems with the database, esp with new db features. -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':INIT_PARAM_VALUE = ''BASIC'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('statistics_level',0,'INIT_PARAM_NAME')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_NAME', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=statistics_level&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_VALUE')); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'STATISTICS_LEVEL', p_metric_name => 'DB_INIT_PARAMS', p_policy_label_nlsid => 'STATISTICS_LEVEL_NAME', p_description => 'STATISTICS_LEVEL_DESC', p_description_nlsid => 'STATISTICS_LEVEL_DESC', p_impact => 'STATISTICS_LEVEL_IMPACT', p_impact_nlsid => 'STATISTICS_LEVEL_IMPACT', p_recommendation => 'STATISTICS_LEVEL_RECOM', p_recommendation_nlsid => 'STATISTICS_LEVEL_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'STATISTICS_LEVEL is set to BASIC. The collection of many important statistics, required by Oracle database features and functionality, are disabled.', p_message_nlsid => 'STATISTICS_LEVEL_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'STATISTICS_LEVEL'); COMMIT; -- ---------------------------------------------------------- -- Policy: Not Using Automatic PGA Management -- Policy ID: AUTO_PGA -- Uses Metric: DB_INIT_PARAMS -- -- Category: Configuration -- Violation Level: Warning -- DB Versions: 9i + -- Agent Version: 10.1.0.2 + -- Added In: 10.2 DB Control -- Description: -- This policy is considered to be in violation if -- the init param 'pga_aggregate_target' has a value of -- 0 OR 'workarea_size_policy' has a value of 'MANUAL'. -- Automatic PGA Management improves how PGA memory is allocated. -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':INIT_PARAM_VALUE = ''0'' OR :INIT_PARAM_VALUE=''MANUAL'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('pga_aggregate_target',0,'INIT_PARAM_NAME')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL), MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('workarea_size_policy',0,'INIT_PARAM_NAME')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_NAME', p_url_link_template => 'database/instance/memory?target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_VALUE')); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'AUTO_PGA', p_metric_name => 'DB_INIT_PARAMS', p_policy_label_nlsid => 'AUTO_PGA_NAME', p_description => 'AUTO_PGA_DESC', p_description_nlsid => 'AUTO_PGA_DESC', p_impact => 'AUTO_PGA_IMPACT', p_impact_nlsid => 'AUTO_PGA_IMPACT', p_recommendation => 'AUTO_PGA_RECOM', p_recommendation_nlsid => 'AUTO_PGA_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database is not using Automatic PGA memory management. It simplifies and improves the way PGA memory is allocated.', p_message_nlsid => 'AUTO_PGA_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'AUTO_PGA'); COMMIT; -- ---------------------------------------------------------- -- Policy: Not Using Latest Version of the Optimizer -- Policy ID: OPTIMIZER_VERSION -- Uses Metric: DB_OPTIMIZER_VERSION -- -- Category: Configuration -- Violation Level: Warning -- DB Versions: All -- Agent Version: 10.1.0.2 -- Added In: 10.2 DB Control -- Description: -- This policy is considered to be in violation if -- the init param 'optimizer_feature_enable' has a value -- that is less than the db version. -- Using older version of the optimizer can lead to execution -- plan or query performance issues. -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':DB_VERSION not like :INIT_PARAM_VALUE || ''%'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('optimizer_features_enable',0,'INIT_PARAM_NAME')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_NAME', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=^INIT_PARAM_NAME^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INIT_PARAM_VALUE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'DB_VERSION')); MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'OPTIMIZER_VERSION', p_metric_name => 'DB_OPTIMIZER_VERSION', p_policy_label_nlsid => 'OPTIMIZER_VERSION_NAME', p_description => 'OPTIMIZER_VERSION_DESC', p_description_nlsid => 'OPTIMIZER_VERSION_DESC', p_impact => 'OPTIMIZER_VERSION_IMPACT', p_impact_nlsid => 'OPTIMIZER_VERSION_IMPACT', p_recommendation => 'OPTIMIZER_VERSION_RECOM', p_recommendation_nlsid => 'OPTIMIZER_VERSION_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database is not using the latest optimizer version. The new version of the optimizer has better information to help it determine the most efficient way to run a SQL Statement.', p_message_nlsid => 'OPTIMIZER_VERSION_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'OPTIMIZER_VERSION'); COMMIT; -- ---------------------------------------------------------- -- Policy: Insufficient Number of Redo Logs -- Uses Metric: DB_REDO_LOG_COUNT -- -- Category: Configuration -- Violation Level: Warning -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2) -- Description: -- This policy will return 1 violation for each -- database that has less than 3 redo logs. The row returned -- will contain the Redo Log Count, a list of the redo log files, -- and a list of the Redo Log Group numbers. -- ---------------------------------------------------------- l_conditionSql := ':REDO_LOG_COUNT < 3 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'REDO_LOG_COUNT', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=redolog&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'FILE_LIST', p_url_link_template => 'database/storage/redolog?oname=^GROUP_NUM_LIST^&event=edit&otype=REDOLOG&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'GROUP_NUM_LIST')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'INSUFF_REDO_LOGS', p_metric_name => 'DB_REDO_LOG_COUNT', p_policy_label_nlsid => 'INSUFF_REDO_LOGS_NAME', p_description => 'INSUFF_REDO_LOGS_DESC', p_description_nlsid => 'INSUFF_REDO_LOGS_DESC', p_impact => 'INSUFF_REDO_LOGS_IMPACT', p_impact_nlsid => 'INSUFF_REDO_LOGS_IMPACT', p_recommendation => 'INSUFF_REDO_LOGS_RECOM', p_recommendation_nlsid => 'INSUFF_REDO_LOGS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database has an insufficient number of redo log files. When the size and number of online redo logs are inadequate, LGWR will wait for ARCH to complete its writing to the archived log destination, before it overwrites that log. This can cause severe performance slowdowns during peak activity periods.', p_message_nlsid => 'INSUFF_REDO_LOGS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'INSUFF_REDO_LOGS'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'INSUFF_REDO_LOGS', p_metric_name => 'DB_REDO_LOG_COUNT', p_policy_label_nlsid => 'INSUFF_REDO_LOGS_NAME', p_description => 'INSUFF_REDO_LOGS_DESC', p_description_nlsid => 'INSUFF_REDO_LOGS_DESC', p_impact => 'INSUFF_REDO_LOGS_IMPACT', p_impact_nlsid => 'INSUFF_REDO_LOGS_IMPACT', p_recommendation => 'INSUFF_REDO_LOGS_RECOM', p_recommendation_nlsid => 'INSUFF_REDO_LOGS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database has an insufficient number of redo log files. When the size and number of online redo logs are inadequate, LGWR will wait for ARCH to complete its writing to the archived log destination, before it overwrites that log. This can cause severe performance slowdowns during peak activity periods.', p_message_nlsid => 'INSUFF_REDO_LOGS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'INSUFF_REDO_LOGS'); COMMIT; -- ---------------------------------------------------------- -- Policy: Insufficient Number of Control Files -- Uses Metric: DB_CONTROL_FILE_COUNT -- -- Category: Configuration -- Violation Level: Critical -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2) -- Description: -- This policy will return 1 violation for each -- database that has only 1 control file. The row returned -- will contain the Control File Count and a list of the control files. -- ---------------------------------------------------------- l_conditionSql := ':CONTROL_FILE_COUNT = 1 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'CONTROL_FILE_COUNT', p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=control_files&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'FILE_LIST')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'INSUFF_CONTROL_FILES', p_metric_name => 'DB_CONTROL_FILE_COUNT', p_policy_label_nlsid => 'INSUFF_CONTROL_FILES_NAME', p_description => 'INSUFF_CONTROL_FILES_DESC', p_description_nlsid => 'INSUFF_CONTROL_FILES_DESC', p_impact => 'INSUFF_CONTROL_FILES_IMPACT', p_impact_nlsid => 'INSUFF_CONTROL_FILES_IMPACT', p_recommendation => 'INSUFF_CONTROL_FILES_RECOM', p_recommendation_nlsid => 'INSUFF_CONTROL_FILES_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database has an insufficient number of control files. If the only copy of the control file is lost due to a media error, there will be unnecessary down time and other risks.', p_message_nlsid => 'INSUFF_CONTROL_FILES_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'INSUFF_CONTROL_FILES'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'INSUFF_CONTROL_FILES', p_metric_name => 'DB_CONTROL_FILE_COUNT', p_policy_label_nlsid => 'INSUFF_CONTROL_FILES_NAME', p_description => 'INSUFF_CONTROL_FILES_DESC', p_description_nlsid => 'INSUFF_CONTROL_FILES_DESC', p_impact => 'INSUFF_CONTROL_FILES_IMPACT', p_impact_nlsid => 'INSUFF_CONTROL_FILES_IMPACT', p_recommendation => 'INSUFF_CONTROL_FILES_RECOM', p_recommendation_nlsid => 'INSUFF_CONTROL_FILES_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The database has an insufficient number of control files. If the only copy of the control file is lost due to a media error, there will be unnecessary down time and other risks', p_message_nlsid => 'INSUFF_CONTROL_FILES_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'INSUFF_CONTROL_FILES'); COMMIT; -- ---------------------------------------------------------- -- Policy: Installation of JAccelerator (NCOMP) -- Uses Metric: DB_INSTALL_NCOMPS -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: All -- Agent Version: -- Description: -- NOTES FROM Shyam: -- This policy 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 -- The Test: -- Major Version must be >= 10 -- ---------------------------------------------------------- l_paramList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'MAJOR_VERSION_TR', param_name_nlsid => 'MAJOR_VERSION_TR_NLSID', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'MAJOR_VERSION_TR', p_info_threshold=>'10')); 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defParamValList)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'MAJOR_VERSION'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'HOME_LOCATION'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'HOST_NAME'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'ORACLE_HOME', p_url_link_template => 'ecm/track/view/oracleHome$target=^HOST_NAME^$type=host$location=^HOME_LOCATION^?event=doLoad', p_url_link_type => MGMT_POLICY.G_URL_TYPE_UIX)); -- ORACLE_DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'INSTALL_NCOMPS', p_metric_name => 'DB_INSTALL_NCOMPS', p_policy_label_nlsid => 'INSTALL_NCOMPS_NAME', p_description => 'INSTALL_NCOMPS_DESC', p_description_nlsid => 'INSTALL_NCOMPS_DESC', p_impact => 'INSTALL_NCOMPS_IMPACT', p_impact_nlsid => 'INSTALL_NCOMPS_IMPACT', p_recommendation => 'INSTALL_NCOMPS_RECOM', p_recommendation_nlsid => 'INSTALL_NCOMPS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD, p_condition => 'MAJOR_VERSION', p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_GE, p_message => 'JAccelerator (NCOMP) is not installed. Without JAccelerator Java VM performance will be sub-optimal.', p_message_nlsid => 'INSTALL_NCOMPS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_policy_param_list => l_paramList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'INSTALL_NCOMPS'); -- RAC_DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'INSTALL_NCOMPS', p_metric_name => 'DB_INSTALL_NCOMPS', p_policy_label_nlsid => 'INSTALL_NCOMPS_NAME', p_description => 'INSTALL_NCOMPS_DESC', p_description_nlsid => 'INSTALL_NCOMPS_DESC', p_impact => 'INSTALL_NCOMPS_IMPACT', p_impact_nlsid => 'INSTALL_NCOMPS_IMPACT', p_recommendation => 'INSTALL_NCOMPS_RECOM', p_recommendation_nlsid => 'INSTALL_NCOMPS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD, p_condition => 'MAJOR_VERSION', p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_GE, p_message => 'JAccelerator (NCOMP) is not installed. Without JAccelerator Java VM performance will be sub-optimal.', p_message_nlsid => 'INSTALL_NCOMPS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_policy_param_list => l_paramList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'INSTALL_NCOMPS'); COMMIT; -- ---------------------------------------------------------- -- Policy: Recovery Area Location Not Set -- Uses Metric: HA_RECOVERY_AREA -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: 10.1 + -- Agent Version: 10.1 + -- Description: -- This policy is considered to be in violation if -- the init param 'db_recovery_file_dest' does not have a value. -- ---------------------------------------------------------- l_conditionSql := 'upper(nvl(:RECOVERY_AREA_LOCATION, ''NULL'')) = ''NULL'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'RECOVERY_AREA_LOCATION', p_url_link_template => 'database/instance/recovery?target=^TARGET_NAME^&type=^TARGET_TYPE^&setFocus=FRA#FRA', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'NO_RECOVERY_AREA', p_metric_name => 'HA_RECOVERY_AREA', p_policy_label_nlsid => 'NO_RECOVERY_AREA_NAME', p_description => 'Checks for recovery area not set', p_description_nlsid => 'NO_RECOVERY_AREA_DESC', p_impact => 'Not setting the recovery area location will result in a divided storage location for all recovery components.', p_impact_nlsid => 'NO_RECOVERY_AREA_IMPACT', p_recommendation => 'It is recommended the recovery area location be set to provide a unified storage location for all recovery components.', p_recommendation_nlsid => 'NO_RECOVERY_AREA_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The recovery area location is not set. Setting the recovery area location provides a unified storage location for all recovery components.', p_message_nlsid => 'NO_RECOVERY_AREA_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'NO_RECOVERY_AREA'); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'RECOVERY_AREA_LOCATION', p_url_link_template => 'rac/racRecovery?target=^TARGET_NAME^&type=^TARGET_TYPE^&setFocus=FRA#FRA', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'NO_RECOVERY_AREA', p_metric_name => 'HA_RECOVERY_AREA', p_policy_label_nlsid => 'NO_RECOVERY_AREA_NAME', p_description => 'Checks for recovery area not set', p_description_nlsid => 'NO_RECOVERY_AREA_DESC', p_impact => 'Not setting the recovery area location will result in a divided storage location for all recovery components.', p_impact_nlsid => 'NO_RECOVERY_AREA_IMPACT', p_recommendation => 'It is recommended the recovery area location be set to provide a unified storage location for all recovery components.', p_recommendation_nlsid => 'NO_RECOVERY_AREA_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The recovery area location is not set. Setting the recovery area location provides a unified storage location for all recovery components.', p_message_nlsid => 'NO_RECOVERY_AREA_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'NO_RECOVERY_AREA'); COMMIT; -- ---------------------------------------------------------- -- Policy: Force Logging Disabled -- Uses Metric: HA_INFO -- -- Category: Configuration -- Violation Level: Informational -- DB Versions: 9.2 + -- Agent Version: 10.1 + -- Description: -- This policy is considered to be in violation if -- the Data Guard Broker is being used and the primary -- datbase has force logging disabled. -- ---------------------------------------------------------- l_conditionSql := 'upper(nvl(:FORCE_LOGGING, ''NULL'')) = ''NO'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'FORCE_LOGGING'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'DATABASE_ROLE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'DG_BROKER_START')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'NO_FORCE_LOGGING', p_metric_name => 'HA_INFO', p_policy_label_nlsid => 'NO_FORCE_LOGGING_NAME', p_description => 'When Data Guard Broker is being used, checks the primary database for disabled force logging', p_description_nlsid => 'NO_FORCE_LOGGING_DESC', p_impact => 'The primary database is not in force logging mode. As a result unlogged direct writes in the primary database cannot be propagated to the standby database.', p_impact_nlsid => 'NO_FORCE_LOGGING_IMPACT', p_recommendation => 'The primary database should be put in force logging mode using ALTER DATABASE FORCE LOGGING.', p_recommendation_nlsid => 'NO_FORCE_LOGGING_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The primary database is not in force logging mode. As a result unlogged direct writes in the primary database cannot be propagated to the standby database.', p_message_nlsid => 'NO_FORCE_LOGGING_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'NO_FORCE_LOGGING'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'NO_FORCE_LOGGING', p_metric_name => 'HA_INFO', p_policy_label_nlsid => 'NO_FORCE_LOGGING_NAME', p_description => 'When Data Guard Broker is being used, checks the primary database for disabled force logging', p_description_nlsid => 'NO_FORCE_LOGGING_DESC', p_impact => 'The primary database is not in force logging mode. As a result unlogged direct writes in the primary database cannot be propagated to the standby database.', p_impact_nlsid => 'NO_FORCE_LOGGING_IMPACT', p_recommendation => 'The primary database should be put in force logging mode using ALTER DATABASE FORCE LOGGING.', p_recommendation_nlsid => 'NO_FORCE_LOGGING_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The primary database is not in force logging mode. As a result unlogged direct writes in the primary database cannot be propagated to the standby database.', p_message_nlsid => 'NO_FORCE_LOGGING_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'NO_FORCE_LOGGING'); COMMIT; END; / SET DEFINE ON -- ---------------------------------------------------------- -- ---------------------------------------------------------- -- STORAGE -- ---------------------------------------------------------- -- ---------------------------------------------------------- SET DEFINE OFF -- ========================================================== -- -- CREATE Storage Policies -- -- ========================================================== DECLARE l_conditionSql VARCHAR2(1000); l_categoryList MGMT_CATEGORY_ARRAY; l_defaultSettingsList MGMT_POLICY_KEY_VAL_ARRAY; l_violCtxtList MGMT_VIOL_CTXT_DEF_ARRAY; l_paramList MGMT_POLICY_PARAM_DEF_ARRAY; l_defParamValList MGMT_POLICY_PARAM_VAL_ARRAY; l_10gR1MetaVersion CONSTANT VARCHAR2(3) := '3.0'; l_ASM10gR1MetaVersion CONSTANT VARCHAR2(3) := '2.0'; BEGIN -- ---------------------------------------------------------- -- Policy: Rollback in SYSTEM Tablespace -- Policy ID: RBS_IN_SYSTEM -- Uses Metric: DB_RBS_IN_SYSTEM -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10.1.0.2 + -- Description: -- This policy is considered to be in violation if -- the init param 'undo_management' = 'MANUAL' (or db is pre-9.2) -- AND there is a rollback segment, other than SYSTEM that -- is listed in the SYSTEM tablespace. Rollback segments can't -- be in SYSAUX because it has AUTO segment space management. -- Added by rreilly for 10gR2 GC Dec 15, 2004 -- NOTE: Exclude default SYSTEM rollback segments -- Check undo_management='MANUAL' OR db version is pre9i -- @BUG: currently undo_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_paramList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'RBS_TBSP_TR', param_name_nlsid => 'RBS_TBSP_TR_NLSID', param_type => MGMT_POLICY.G_PARAM_TYPE_STRING)); l_defParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'RBS_TBSP_TR', p_info_threshold=>'SYSTEM')); 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defParamValList)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'RBS_NAME', p_url_link_template => 'database/storage/rbsegment?oname=^RBS_NAME^&event=edit&otype=RB_SEGMENT&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'RBS_IN_SYSTEM', p_metric_name => 'DB_RBS_IN_SYSTEM', p_policy_label_nlsid => 'RBS_IN_SYSTEM_NAME', p_description => 'RBS_IN_SYSTEM_DESC', p_description_nlsid => 'RBS_IN_SYSTEM_DESC', p_impact => 'RBS_IN_SYSTEM_IMPACT', p_impact_nlsid => 'RBS_IN_SYSTEM_IMPACT', p_recommendation => 'RBS_IN_SYSTEM_RECOM', p_recommendation_nlsid => 'RBS_IN_SYSTEM_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD, p_condition => 'TABLESPACE_NAME', p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_EQ, p_message => 'The SYSTEM tablespace contains rollback segment %rbs_name%. The SYSTEM tablespace should be reserved only for the Oracle data dictionary and its associated objects.', p_message_nlsid => 'RBS_IN_SYSTEM_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_policy_param_list => l_paramList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'RBS_IN_SYSTEM'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'RBS_IN_SYSTEM', p_metric_name => 'DB_RBS_IN_SYSTEM', p_policy_label_nlsid => 'RBS_IN_SYSTEM_NAME', p_description => 'RBS_IN_SYSTEM_DESC', p_description_nlsid => 'RBS_IN_SYSTEM_DESC', p_impact => 'RBS_IN_SYSTEM_IMPACT', p_impact_nlsid => 'RBS_IN_SYSTEM_IMPACT', p_recommendation => 'RBS_IN_SYSTEM_RECOM', p_recommendation_nlsid => 'RBS_IN_SYSTEM_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD, p_condition => 'TABLESPACE_NAME', p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_EQ, p_message => 'The SYSTEM tablespace contains rollback segment %rbs_name%. The SYSTEM tablespace should be reserved only for the Oracle data dictionary and its associated objects.', p_message_nlsid => 'RBS_IN_SYSTEM_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_policy_param_list => l_paramList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'RBS_IN_SYSTEM'); COMMIT; -- ---------------------------------------------------------- -- Policy: Users with Permanent Tablespace as Temporary Tablespace -- Policy ID: PERM_AS_TEMP_TBSP -- Uses Metric: DB_REC_USER_SETTINGS -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2+) -- Description: -- This policy is considered to be in violation if -- a user has a permanent tablespace listed as their -- temporary tablespace. The policy violation will contain -- the username, name of the tablespace, and a problem code of 1. -- Added by rreilly for 10gR2 GC Feb 04, 2005 -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 1 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'USER_NAME'), mgmt_policy_key_col_cond.new('1',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'USER_NAME', p_url_link_template => 'database/security/user?oname=^USER_NAME^&event=edit&otype=USER&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'PERM_AS_TEMP_TBSP', p_metric_name => 'DB_REC_USER_SETTINGS', p_policy_label_nlsid => 'PERM_AS_TEMP_TBSP_NAME', p_description => 'PERM_AS_TEMP_TBSP_DESC', p_description_nlsid => 'PERM_AS_TEMP_TBSP_DESC', p_impact => 'PERM_AS_TEMP_TBSP_IMPACT', p_impact_nlsid => 'PERM_AS_TEMP_TBSP_IMPACT', p_recommendation => 'PERM_AS_TEMP_TBSP_RECOM', p_recommendation_nlsid => 'PERM_AS_TEMP_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'User %user_name% uses permanent tablespace %tablespace_name% as the temporary tablespace. Using a permanent tablespace as the temporary tablespace may result in performance degradation, especially for Real Application Clusters. If the user is using a system tablespace as a temporary tablespace, there is an additional security concern.', p_message_nlsid => 'PERM_AS_TEMP_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'PERM_AS_TEMP_TBSP'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'PERM_AS_TEMP_TBSP', p_metric_name => 'DB_REC_USER_SETTINGS', p_policy_label_nlsid => 'PERM_AS_TEMP_TBSP_NAME', p_description => 'PERM_AS_TEMP_TBSP_DESC', p_description_nlsid => 'PERM_AS_TEMP_TBSP_DESC', p_impact => 'PERM_AS_TEMP_TBSP_IMPACT', p_impact_nlsid => 'PERM_AS_TEMP_TBSP_IMPACT', p_recommendation => 'PERM_AS_TEMP_TBSP_RECOM', p_recommendation_nlsid => 'PERM_AS_TEMP_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'User %user_name% uses permanent tablespace %tablespace_name% as the temporary tablespace. Using a permanent tablespace as the temporary tablespace may result in performance degradation, especially for Real Application Clusters. If the user is using a system tablespace as a temporary tablespace, there is an additional security concern.', p_message_nlsid => 'PERM_AS_TEMP_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'PERM_AS_TEMP_TBSP'); COMMIT; -- ---------------------------------------------------------- -- Policy: Users with a System Tablespace as Default Tablespace -- Policy ID: SYSTEM_AS_DEFAULT_TBSP -- Uses Metric: DB_REC_USER_SETTINGS -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2+) -- Description: -- This policy is considered to be in violation if -- a user has a system tablespace listed as their -- default tablespace. The policy violation will contain -- the username, name of the tablespace, and a problem code of 2. -- Added by rreilly for 10gR2 GC Feb 04, 2005 -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':USER_NAME not in ' || '(''SYS'', ''SYSTEM'', ''SYSMAN'', ''CTXSYS'', ''MDSYS'', ' || ' ''ORDSYS'', ''ORDPLUGINS'', ''OLAPSYS'', ''DBSNMP'', ' || ' ''MGMT_VIEW'', ''OUTLN'', ''ANONYMOUS'', ''DMSYS'', ''EXFSYS'', ' || ' ''LBACSYS'', ''SI_INFORMTN_SCHEMA'', ''SYSMAN'', ''WKPROXY'', ' || ' ''WKSYS'', ''WK_TEST'', ''WMSYS'', ''XDB'', ''TRACESVR'', ' || ' ''SCOTT'', ''ADAMS'', ''BLAKE'', ''CLARK'', ''JONES'', ''HR'', ' || ' ''TSMSYS'', ''PM'', ''OE'', ''SH'', ''IX'') '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'USER_NAME'), mgmt_policy_key_col_cond.new('2',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'USER_NAME', p_url_link_template => 'database/security/user?oname=^USER_NAME^&event=edit&otype=USER&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP', p_metric_name => 'DB_REC_USER_SETTINGS', p_policy_label_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_NAME', p_description => 'SYSTEM_AS_DEFAULT_TBSP_DESC', p_description_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_DESC', p_impact => 'SYSTEM_AS_DEFAULT_TBSP_IMPACT', p_impact_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_IMPACT', p_recommendation => 'SYSTEM_AS_DEFAULT_TBSP_RECOM', p_recommendation_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'User %user_name% uses a system tablespace %tablespace_name% as the default tablespace. This will result in non-system data segments being added to the system tablespace and possible performance degradation and security issues in the system tablespace.', p_message_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP', p_metric_name => 'DB_REC_USER_SETTINGS', p_policy_label_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_NAME', p_description => 'SYSTEM_AS_DEFAULT_TBSP_DESC', p_description_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_DESC', p_impact => 'SYSTEM_AS_DEFAULT_TBSP_IMPACT', p_impact_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_IMPACT', p_recommendation => 'SYSTEM_AS_DEFAULT_TBSP_RECOM', p_recommendation_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'User %user_name% uses a system tablespace %tablespace_name% as the default tablespace. This will result in non-system data segments being added to the system tablespace and possible performance degradation and security issues in the system tablespace.', p_message_nlsid => 'SYSTEM_AS_DEFAULT_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP'); COMMIT; -- ---------------------------------------------------------- -- Policy: Tablespace Not Using Automatic Segment-Space Management -- Policy ID: TBSP_SEGSPACE_MGMT -- Uses Metric: DB_TBSP_SEGSPACE_MGMT -- -- Category: Storage -- Violation Level: Informational -- DB Versions: 9.2+ -- Agent Version: 10.1.0.4 (META_VER 3.5) [10.2 DB Control] -- Description: -- This policy is considered to be in violation if -- a user locally managed tablespaces that are using -- manual segment-space management. -- The policy violation will contain the tablespace name, -- a problem code of 1, value1 (NUMBER), value2 (NUMBER) -- Value1 and value2 are not used by this policy. -- Added by rreilly for 10gR2 GC Feb 05, 2005 -- @BUG 3720871: Due to server problems for pre9i -- with LOB and ASSM tablespaces, this policy -- should only run for 9.2+ versions of the DB. -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 1 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'TABLESPACE_NAME'), mgmt_policy_key_col_cond.new('1',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/reorg/reorg?event=launch&oname=^TABLESPACE_NAME^<ype=reorgObjects&cancelURL=^CANCEL_URL^&lctx=tablespace&target=^TARGET_NAME^&type=^TARGET_TYPE^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'TBSP_SEGSPACE_MGMT', p_metric_name => 'DB_TBSP_SEGSPACE_MGMT', p_policy_label_nlsid => 'TBSP_SEGSPACE_MGMT_NAME', p_description => 'TBSP_SEGSPACE_MGMT_DESC', p_description_nlsid => 'TBSP_SEGSPACE_MGMT_DESC', p_impact => 'TBSP_SEGSPACE_MGMT_IMPACT', p_impact_nlsid => 'TBSP_SEGSPACE_MGMT_IMPACT', p_recommendation => 'TBSP_SEGSPACE_MGMT_RECOM', p_recommendation_nlsid => 'TBSP_SEGSPACE_MGMT_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% is not using automatic segment-space management.', p_message_nlsid => 'TBSP_SEGSPACE_MGMT_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'TBSP_SEGSPACE_MGMT', p_metric_name => 'DB_TBSP_SEGSPACE_MGMT', p_policy_label_nlsid => 'TBSP_SEGSPACE_MGMT_NAME', p_description => 'TBSP_SEGSPACE_MGMT_DESC', p_description_nlsid => 'TBSP_SEGSPACE_MGMT_DESC', p_impact => 'TBSP_SEGSPACE_MGMT_IMPACT', p_impact_nlsid => 'TBSP_SEGSPACE_MGMT_IMPACT', p_recommendation => 'TBSP_SEGSPACE_MGMT_RECOM', p_recommendation_nlsid => 'TBSP_SEGSPACE_MGMT_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% is not using automatic segment-space management.', p_message_nlsid => 'TBSP_SEGSPACE_MGMT_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); COMMIT; -- ---------------------------------------------------------- -- Policy: Dictionary Managed Tablespaces -- Policy ID: TBSP_DICTIONARY -- Uses Metric: DB_REC_TS_SETTINGS -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2+) -- Description: -- This policy is considered to be in violation if -- a user has any dictionary managed tablespaces. -- The policy violation will contain the tablespace name, -- a problem code of 2, value1 (NUMBER), value2 (NUMBER) -- Value1 and value2 are not used by this policy. -- Added by rreilly for 10gR2 GC Feb 05, 2005 -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 2 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'TABLESPACE_NAME'), mgmt_policy_key_col_cond.new('2',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/reorg/reorg?event=launch<ype=makeLocal&lctx=maintPage&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'TBSP_DICTIONARY', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'TBSP_DICTIONARY_NAME', p_description => 'TBSP_DICTIONARY_DESC', p_description_nlsid => 'TBSP_DICTIONARY_DESC', p_impact => 'TBSP_DICTIONARY_IMPACT', p_impact_nlsid => 'TBSP_DICTIONARY_IMPACT', p_recommendation => 'TBSP_DICTIONARY_RECOM', p_recommendation_nlsid => 'TBSP_DICTIONARY_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% is dictionary managed. Oracle recommends using locally managed tablespaces, with AUTO segment-space management, to enhance performance and ease of space management.', p_message_nlsid => 'TBSP_DICTIONARY_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'TBSP_DICTIONARY', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'TBSP_DICTIONARY_NAME', p_description => 'TBSP_DICTIONARY_DESC', p_description_nlsid => 'TBSP_DICTIONARY_DESC', p_impact => 'TBSP_DICTIONARY_IMPACT', p_impact_nlsid => 'TBSP_DICTIONARY_IMPACT', p_recommendation => 'TBSP_DICTIONARY_RECOM', p_recommendation_nlsid => 'TBSP_DICTIONARY_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% is dictionary managed. Oracle recommends using locally managed tablespaces, with AUTO segment-space management, to enhance performance and ease of space management.', p_message_nlsid => 'TBSP_DICTIONARY_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); COMMIT; -- ---------------------------------------------------------- -- Policy: Tablespaces Containing Rollback and Data Segments -- Policy ID: TBSP_MIXED_SEGS -- Uses Metric: DB_REC_TS_SETTINGS -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2+) -- Description: -- This policy is considered to be in violation if -- a tablespace has object and rollback segs. -- The policy violation will contain the tablespace name, -- a problem code of 3, value1 (NUMBER of rollback segments), -- value2 (NUMBER of data segments). -- Added by rreilly for 10gR2 GC Feb 05, 2005 -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':TABLESPACE_NAME != ''SYSTEM'' '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'TABLESPACE_NAME'), mgmt_policy_key_col_cond.new('3',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/storage/tablespace?event=go&optionChoice=SHOW_SEGMENTS&oname=^TABLESPACE_NAME^&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'VALUE1'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'VALUE2')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'TBSP_MIXED_SEGS', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'TBSP_MIXED_SEGS_NAME', p_description => 'TBSP_MIXED_SEGS_DESC', p_description_nlsid => 'TBSP_MIXED_SEGS_DESC', p_impact => 'TBSP_MIXED_SEGS_IMPACT', p_impact_nlsid => 'TBSP_MIXED_SEGS_IMPACT', p_recommendation => 'TBSP_MIXED_SEGS_RECOM', p_recommendation_nlsid => 'TBSP_MIXED_SEGS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% contains both rollback and data segments. Mixing segment types in this way makes it more difficult to manage space and may degrade performance in the tablespace.', p_message_nlsid => 'TBSP_MIXED_SEGS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'TBSP_MIXED_SEGS'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'TBSP_MIXED_SEGS', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'TBSP_MIXED_SEGS_NAME', p_description => 'TBSP_MIXED_SEGS_DESC', p_description_nlsid => 'TBSP_MIXED_SEGS_DESC', p_impact => 'TBSP_MIXED_SEGS_IMPACT', p_impact_nlsid => 'TBSP_MIXED_SEGS_IMPACT', p_recommendation => 'TBSP_MIXED_SEGS_RECOM', p_recommendation_nlsid => 'TBSP_MIXED_SEGS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% contains both rollback and data segments. Mixing segment types in this way makes it more difficult to manage space and may degrade performance in the tablespace.', p_message_nlsid => 'TBSP_MIXED_SEGS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'TBSP_MIXED_SEGS'); COMMIT; -- ---------------------------------------------------------- -- Policy: Default Temporary Tablespace set to a System Tablespace -- Policy ID: DEFAULT_TEMP_TBSP -- Uses Metric: DB_REC_TS_SETTINGS -- -- Category: Storage -- Violation Level: Warning -- DB Versions: 9i+ -- Agent Version: 10.1.0.4 (META_VER 3.5) [10.2 DB Control] -- Description: -- This policy is considered to be in violation if -- the DEFAULT_TEMPORARY_TABLESPACE property is set -- to a system tablespace. -- Change this setting by using: -- ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ; -- The policy violation will contain the tablespace name -- and a problem code of 6. -- Value1 (NUMBER) and Value2 (NUMBER) are not used. -- Added by rreilly for 10gR2 GC Feb 05, 2005 -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 6 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'TABLESPACE_NAME'), mgmt_policy_key_col_cond.new('6',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'DEFAULT_TEMP_TBSP', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'DEFAULT_TEMP_TBSP_NAME', p_description => 'DEFAULT_TEMP_TBSP_DESC', p_description_nlsid => 'DEFAULT_TEMP_TBSP_DESC', p_impact => 'DEFAULT_TEMP_TBSP_IMPACT', p_impact_nlsid => 'DEFAULT_TEMP_TBSP_IMPACT', p_recommendation => 'DEFAULT_TEMP_TBSP_RECOM', p_recommendation_nlsid => 'DEFAULT_TEMP_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The default temporary tablespace is set to system tablespace %tablespace_name%. This means that users with no explicit temporary tablespace will use this tablespace as their temporary tablespace. This can create system performance problems and is a security issue.', p_message_nlsid => 'DEFAULT_TEMP_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'DEFAULT_TEMP_TBSP'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'DEFAULT_TEMP_TBSP', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'DEFAULT_TEMP_TBSP_NAME', p_description => 'DEFAULT_TEMP_TBSP_DESC', p_description_nlsid => 'DEFAULT_TEMP_TBSP_DESC', p_impact => 'DEFAULT_TEMP_TBSP_IMPACT', p_impact_nlsid => 'DEFAULT_TEMP_TBSP_IMPACT', p_recommendation => 'DEFAULT_TEMP_TBSP_RECOM', p_recommendation_nlsid => 'DEFAULT_TEMP_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The default temporary tablespace is set to system tablespace %tablespace_name%. This means that users with no explicit temporary tablespace will use this tablespace as their temporary tablespace. This can create system performance problems and is a security issue.', p_message_nlsid => 'DEFAULT_TEMP_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'DEFAULT_TEMP_TBSP'); COMMIT; -- ---------------------------------------------------------- -- Policy: Default Permanent Tablespace set to a System Tablespace -- Policy ID: DEFAULT_PERMANENT_TBSP -- Uses Metric: DB_REC_TS_SETTINGS -- -- Category: Storage -- Violation Level: Warning -- DB Versions: 10gR1 DB -- Agent Version: 10.1.0.4 (META_VER 3.5) [10.2 DB Control] -- Description: -- This policy is considered to be in violation if -- the DEFAULT_PERMANENT_TABLESPACE property is set -- to a system tablespace. -- Change this setting by using: -- ALTER DATABASE DEFAULT TABLESPACE ; -- The policy violation will contain the tablespace name -- and a problem code of 7. -- Value1 (NUMBER) and Value2 (NUMBER) are not used. -- Added by rreilly for 10gR2 GC Feb 05, 2005 -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 7 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'TABLESPACE_NAME'), mgmt_policy_key_col_cond.new('7',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'DEFAULT_PERMANENT_TBSP', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'DEFAULT_PERMANENT_TBSP_NAME', p_description => 'DEFAULT_PERMANENT_TBSP_DESC', p_description_nlsid => 'DEFAULT_PERMANENT_TBSP_DESC', p_impact => 'DEFAULT_PERMANENT_TBSP_IMPACT', p_impact_nlsid => 'DEFAULT_PERMANENT_TBSP_IMPACT', p_recommendation => 'DEFAULT_PERMANENT_TBSP_RECOM', p_recommendation_nlsid => 'DEFAULT_PERMANENT_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The default permanent tablespace is to system tablespace %tablespace_name%. This means that users will default to using this tablespace as their permanent tablespace. Non-system users should not use a system tablespace as their permanent tablespace. It can create system performance problems and is a security issue.', p_message_nlsid => 'DEFAULT_PERMANENT_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'DEFAULT_PERMANENT_TBSP'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'DEFAULT_PERMANENT_TBSP', p_metric_name => 'DB_REC_TS_SETTINGS', p_policy_label_nlsid => 'DEFAULT_PERMANENT_TBSP_NAME', p_description => 'DEFAULT_PERMANENT_TBSP_DESC', p_description_nlsid => 'DEFAULT_PERMANENT_TBSP_DESC', p_impact => 'DEFAULT_PERMANENT_TBSP_IMPACT', p_impact_nlsid => 'DEFAULT_PERMANENT_TBSP_IMPACT', p_recommendation => 'DEFAULT_PERMANENT_TBSP_RECOM', p_recommendation_nlsid => 'DEFAULT_PERMANENT_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The default permanent tablespace is to system tablespace %tablespace_name%. This means that users will default to using this tablespace as their permanent tablespace. Non-system users should not use a system tablespace as their permanent tablespace. It can create system performance problems and is a security issue.', p_message_nlsid => 'DEFAULT_PERMANENT_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'DEFAULT_PERMANENT_TBSP'); COMMIT; -- ---------------------------------------------------------- -- Policy: Non-Uniform Default Extent Size for Tablespaces -- Policy ID: NON_UNIFORM_TBSP -- Uses Metric: DB_TABLESPACES -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2) -- Description: -- This policy is considered to be in violation if -- there is a PERMANENT dictionary managed tablespace -- that has a non-zero pctincrease, or has a next extent -- size that is not a multiple of the initial extent size. -- 050701 This policy originally worked for just dictionary -- managed tablespaces. Added support for migrated -- locally managed tablespaces. These are identified by -- EXTENT_MANAGEMENT of LOCAL and ALLOCATION_TYPE of USER -- ---------------------------------------------------------- l_conditionSql := ':TABLESPACE_NAME != ''SYSTEM'' AND ' || ':CONTENTS = ''PERMANENT'' AND ' || '(:EXTENT_MANAGEMENT = ''DICTIONARY'' OR ' || ' (:EXTENT_MANAGEMENT = ''LOCAL'' AND :ALLOCATION_TYPE = ''USER'')) AND ' || '(:PCTINCREASE !=0 OR ' || '(:INITIAL_EXTENT > 0 AND ' || 'MOD(:NEXT_EXTENT,:INITIAL_EXTENT) != 0)) '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/storage/tablespace?oname=^TABLESPACE_NAME^&otype=TABLESPACE&event=edit&cancelURL=^CANCEL_URL^&target=^TARGET_NAME^&type=^TARGET_TYPE^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'EXTENT_MANAGEMENT'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'ALLOCATION_TYPE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'PCTINCREASE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'INITIAL_EXTENT'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'NEXT_EXTENT')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'NON_UNIFORM_TBSP', p_metric_name => 'DB_TABLESPACES', p_policy_label_nlsid => 'NON_UNIFORM_TBSP_NAME', p_description => 'NON_UNIFORM_TBSP_DESC', p_description_nlsid => 'NON_UNIFORM_TBSP_DESC', p_impact => 'NON_UNIFORM_TBSP_IMPACT', p_impact_nlsid => 'NON_UNIFORM_TBSP_IMPACT', p_recommendation => 'NON_UNIFORM_TBSP_RECOM', p_recommendation_nlsid => 'NON_UNIFORM_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% uses non-uniform extents. Using uniform extents ensures that any free extent in the tablespace can always be used for any segment in the tablespace.', p_message_nlsid => 'NON_UNIFORM_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'NON_UNIFORM_TBSP'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'NON_UNIFORM_TBSP', p_metric_name => 'DB_TABLESPACES', p_policy_label_nlsid => 'NON_UNIFORM_TBSP_NAME', p_description => 'NON_UNIFORM_TBSP_DESC', p_description_nlsid => 'NON_UNIFORM_TBSP_DESC', p_impact => 'NON_UNIFORM_TBSP_IMPACT', p_impact_nlsid => 'NON_UNIFORM_TBSP_IMPACT', p_recommendation => 'NON_UNIFORM_TBSP_RECOM', p_recommendation_nlsid => 'NON_UNIFORM_TBSP_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Tablespace %tablespace_name% uses non-uniform extents. Using uniform extents ensures that any free extent in the tablespace can always be used for any segment in the tablespace.', p_message_nlsid => 'NON_UNIFORM_TBSP_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'NON_UNIFORM_TBSP'); COMMIT; -- ---------------------------------------------------------- -- Policy: Non-System Data Segments in a System Tablespace -- Policy ID: SEG_NONSYS_SEG_IN_SYS_TS -- Uses Metric: DB_REC_SEGMENT_SETTINGS -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2) -- Description: -- This policy is considered to be in violation if -- a data segment is in a system tablespace and it is not -- owned by a system user. -- Problem Code: 4 -- ---------------------------------------------------------- l_conditionSql := ':OBJECT_OWNER not in ' || '(''SYS'', ''SYSTEM'', ''SYSMAN'', ''CTXSYS'', ''MDSYS'', ' || ' ''ORDSYS'', ''ORDPLUGINS'', ''OLAPSYS'', ''DBSNMP'', ' || ' ''MGMT_VIEW'', ''OUTLN'', ''ANONYMOUS'', ''DMSYS'', ''EXFSYS'', ' || ' ''LBACSYS'', ''SI_INFORMTN_SCHEMA'', ''SYSMAN'', ''WKPROXY'', ' || ' ''WKSYS'', ''WK_TEST'', ''WMSYS'', ''XDB'', ''TRACESVR'', ' || ' ''SCOTT'', ''ADAMS'', ''BLAKE'', ''CLARK'', ''JONES'', ''HR'', ' || ' ''TSMSYS'', ''PM'', ''OE'', ''SH'', ''IX'') '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'OBJECT'), mgmt_policy_key_col_cond.new('%',1,'OBJECT_TYPE'), mgmt_policy_key_col_cond.new('%',1,'PARTITION'), mgmt_policy_key_col_cond.new('%',1,'LOB_COL'), mgmt_policy_key_col_cond.new('4',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT', p_url_link_template => 'database/reorg/reorg?lctx=schemaObject&sname=^OBJECT_OWNER^&oname=^OBJECT_NAME^&pname=^PTN_PARAM^&event=launch<ype=reorgObjects&otype=^OBJECT_TYPE^&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT_TYPE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'PARTITION'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'LOB_COL'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'SEGMENT_TYPE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'PTN_PARAM', p_is_hidden => '1'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT_OWNER', p_is_hidden => '1'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT_NAME', p_is_hidden => '1')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'SEG_NONSYS_SEG_IN_SYS_TS', p_metric_name => 'DB_REC_SEGMENT_SETTINGS', p_policy_label_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_NAME', p_description => 'SEG_NONSYS_SEG_IN_SYS_TS_DESC', p_description_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_DESC', p_impact => 'SEG_NONSYS_SEG_IN_SYS_TS_IMPACT', p_impact_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_IMPACT', p_recommendation => 'SEG_NONSYS_SEG_IN_SYS_TS_RECOM', p_recommendation_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The segment %object% belongs to a non-system user and is being stored in system tablespace %tablespace_name%. This makes it more difficult to manage these data segments and may result in performance degradation in the system tablespace and is a security issue.', p_message_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'SEG_NONSYS_SEG_IN_SYS_TS', p_metric_name => 'DB_REC_SEGMENT_SETTINGS', p_policy_label_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_NAME', p_description => 'SEG_NONSYS_SEG_IN_SYS_TS_DESC', p_description_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_DESC', p_impact => 'SEG_NONSYS_SEG_IN_SYS_TS_IMPACT', p_impact_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_IMPACT', p_recommendation => 'SEG_NONSYS_SEG_IN_SYS_TS_RECOM', p_recommendation_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The segment %object% belongs to a non-system user and is being stored in system tablespace %tablespace_name%. This makes it more difficult to manage these data segments and may result in performance degradation in the system tablespace and is a security issue.', p_message_nlsid => 'SEG_NONSYS_SEG_IN_SYS_TS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); COMMIT; -- ---------------------------------------------------------- -- Policy: Segment with Non-Uniform Extent Size -- Policy ID: SEG_EXT_GROWTH_VIO -- Uses Metric: DB_REC_SEGMENT_SETTINGS -- -- Category: Storage -- Violation Level: Informational -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2) -- Segs in Migrated Local Tbsp require 10.2 agent -- NOTE: the 10.2 agent change is problematic to backport -- because 10.1 EM is Dictionary only policy and -- 10.1 EM does not support extents for migrated -- locally managed tablespaces. -- Description: -- This policy is considered to be in violation if -- a data segment in a dictionary managed tablespace -- has a non-zero pctincrease, or has a next extent -- size that is not a multiple of the initial extent size. -- Clusters are excluded because reorg does not support them. -- 050701 This policy originally worked for just segs in -- dictionary managed tablespaces. Added support for migrated -- locally managed tablespaces. These are identified by -- EXTENT_MANAGEMENT of LOCAL and ALLOCATION_TYPE of USER -- This required a 10.2 agent change. -- Problem Code: >0, <4 : 1,2,3 -- ---------------------------------------------------------- l_conditionSql := ':OBJECT_OWNER not in ' || '(''SYS'', ''SYSTEM'', ''SYSMAN'', ''CTXSYS'', ''MDSYS'', ' || ' ''ORDSYS'', ''ORDPLUGINS'', ''OLAPSYS'', ''DBSNMP'', ' || ' ''MGMT_VIEW'', ''OUTLN'', ''ANONYMOUS'', ''DMSYS'', ''EXFSYS'', ' || ' ''LBACSYS'', ''SI_INFORMTN_SCHEMA'', ''SYSMAN'', ''WKPROXY'', ' || ' ''WKSYS'', ''WK_TEST'', ''WMSYS'', ''XDB'', ''TRACESVR'', ' || ' ''SCOTT'', ''ADAMS'', ''BLAKE'', ''CLARK'', ''JONES'', ''HR'', ' || ' ''TSMSYS'', ''PM'', ''OE'', ''SH'', ''IX'') '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'OBJECT'), mgmt_policy_key_col_cond.new('%',1,'OBJECT_TYPE'), mgmt_policy_key_col_cond.new('%',1,'PARTITION'), mgmt_policy_key_col_cond.new('%',1,'LOB_COL'), mgmt_policy_key_col_cond.new('1',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL), MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'OBJECT'), mgmt_policy_key_col_cond.new('%',1,'OBJECT_TYPE'), mgmt_policy_key_col_cond.new('%',1,'PARTITION'), mgmt_policy_key_col_cond.new('%',1,'LOB_COL'), mgmt_policy_key_col_cond.new('2',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL), MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'OBJECT'), mgmt_policy_key_col_cond.new('%',1,'OBJECT_TYPE'), mgmt_policy_key_col_cond.new('%',1,'PARTITION'), mgmt_policy_key_col_cond.new('%',1,'LOB_COL'), mgmt_policy_key_col_cond.new('3',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT', p_url_link_template => 'database/reorg/reorg?lctx=schemaObject&sname=^OBJECT_OWNER^&oname=^OBJECT_NAME^&pname=^PTN_PARAM^&event=launch<ype=reorgObjects&otype=^OBJECT_TYPE^&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT_TYPE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'PARTITION'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'LOB_COL'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'SEGMENT_TYPE'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=TABLESPACE&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'PTN_PARAM', p_is_hidden => '1'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT_OWNER', p_is_hidden => '1'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'OBJECT_NAME', p_is_hidden => '1')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'SEG_EXT_GROWTH_VIO', p_metric_name => 'DB_REC_SEGMENT_SETTINGS', p_policy_label_nlsid => 'SEG_EXT_GROWTH_VIO_NAME', p_description => 'SEG_EXT_GROWTH_VIO_DESC', p_description_nlsid => 'SEG_EXT_GROWTH_VIO_DESC', p_impact => 'SEG_EXT_GROWTH_VIO_IMPACT', p_impact_nlsid => 'SEG_EXT_GROWTH_VIO_IMPACT', p_recommendation => 'SEG_EXT_GROWTH_VIO_RECOM', p_recommendation_nlsid => 'SEG_EXT_GROWTH_VIO_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Segment %object% in dictionary managed tablespace %tablespace_name% has irregular extent sizes and/or non-zero Percent Increase settings. This can result in inefficient reuse of space and fragmentation problems.', p_message_nlsid => 'SEG_EXT_GROWTH_VIO_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'SEG_EXT_GROWTH_VIO', p_metric_name => 'DB_REC_SEGMENT_SETTINGS', p_policy_label_nlsid => 'SEG_EXT_GROWTH_VIO_NAME', p_description => 'SEG_EXT_GROWTH_VIO_DESC', p_description_nlsid => 'SEG_EXT_GROWTH_VIO_DESC', p_impact => 'SEG_EXT_GROWTH_VIO_IMPACT', p_impact_nlsid => 'SEG_EXT_GROWTH_VIO_IMPACT', p_recommendation => 'SEG_EXT_GROWTH_VIO_RECOM', p_recommendation_nlsid => 'SEG_EXT_GROWTH_VIO_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Segment %object% in dictionary managed tablespace %tablespace_name% has irregular extent sizes and/or non-zero Percent Increase settings. This can result in inefficient reuse of space and fragmentation problems.', p_message_nlsid => 'SEG_EXT_GROWTH_VIO_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_FALSE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); COMMIT; -- ---------------------------------------------------------- -- Policy: Insufficient Redo Log Size -- Policy ID: SMALL_REDO_LOGS -- Uses Metric: DB_REDO_LOGS -- -- Category: Storage -- Violation Level: Critical -- DB Versions: All -- Agent Version: 10gR1 (10.1.0.2) -- Description: -- This policy will return 1 violation for each -- file that is less than 1M in size. The row returned -- will contain the Group Number, File Name and Log Size (MB). -- NOTE: A redo log file can't be smaller than the db_block_size -- init.ora param. -- ---------------------------------------------------------- l_conditionSql := ':FILE_SIZE_MB < 1 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'GROUP_NUM'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'FILE_NAME', p_url_link_template => 'database/storage/redolog?oname=^GROUP_NUM^&event=edit&otype=REDOLOG&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'FILE_SIZE_MB')); -- ORACLE DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'oracle_database', p_policy_name => 'SMALL_REDO_LOGS', p_metric_name => 'DB_REDO_LOGS', p_policy_label_nlsid => 'SMALL_REDO_LOGS_NAME', p_description => 'SMALL_REDO_LOGS_DESC', p_description_nlsid => 'SMALL_REDO_LOGS_DESC', p_impact => 'SMALL_REDO_LOGS_IMPACT', p_impact_nlsid => 'SMALL_REDO_LOGS_IMPACT', p_recommendation => 'SMALL_REDO_LOGS_RECOM', p_recommendation_nlsid => 'SMALL_REDO_LOGS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The in redo log group %group_num%, redo log file %file_name% is of insufficient size. Small redo logs can put a high load on the buffer cache and cause increased I/O.', p_message_nlsid => 'SMALL_REDO_LOGS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'oracle_database', p_policy_name => 'SMALL_REDO_LOGS'); -- RAC DATABASE MGMT_POLICY.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name => 'SMALL_REDO_LOGS', p_metric_name => 'DB_REDO_LOGS', p_policy_label_nlsid => 'SMALL_REDO_LOGS_NAME', p_description => 'SMALL_REDO_LOGS_DESC', p_description_nlsid => 'SMALL_REDO_LOGS_DESC', p_impact => 'SMALL_REDO_LOGS_IMPACT', p_impact_nlsid => 'SMALL_REDO_LOGS_IMPACT', p_recommendation => 'SMALL_REDO_LOGS_RECOM', p_recommendation_nlsid => 'SMALL_REDO_LOGS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'The in redo log group %group_num%, redo log file %file_name% is of insufficient size. Small redo logs can put a high load on the buffer cache and cause increased I/O.', p_message_nlsid => 'SMALL_REDO_LOGS_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => l_10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'rac_database', p_policy_name => 'SMALL_REDO_LOGS'); COMMIT; -- ---------------------------------------------------------- -- Policy: Disk Group Contains Disks of Significantly Different Sizes -- Policy ID: ASM_DISK_SIZE -- Uses Metric: DB_ASM_DISK_GROUP_SETTINGS -- -- Category: Storage -- Violation Level: Warning -- ASM Versions: 10gR1, 10gR2 -- Agent Version: 10gR2 DB version of osm_instance metadata -- Description: -- This policy is considered to be in violation if -- a disks of varying sizes are in the same disk group. -- Problem Code: 1 -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 1 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'DISK_GROUP_NAME'), mgmt_policy_key_col_cond.new('1',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'DISK_GROUP_NAME', p_url_link_template => 'database/osm/diskGroup?type=^TARGET_TYPE^&target=^TARGET_NAME^&oname=^DISK_GROUP_NAME^&event=edit&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- OSM INSTANCE MGMT_POLICY.CREATE_POLICY( p_target_type => 'osm_instance', p_policy_name => 'ASM_DISK_SIZE', p_metric_name => 'DB_ASM_DISK_GROUP_SETTINGS', p_policy_label_nlsid => 'ASM_DISK_SIZE_NAME', p_description => 'ASM_DISK_SIZE_DESC', p_description_nlsid => 'ASM_DISK_SIZE_DESC', p_impact => 'ASM_DISK_SIZE_IMPACT', p_impact_nlsid => 'ASM_DISK_SIZE_IMPACT', p_recommendation => 'ASM_DISK_SIZE_RECOM', p_recommendation_nlsid => 'ASM_DISK_SIZE_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Disk Group %disk_group_name% contains disks of significantly different sizes. For balanced I/O and optimal performance, disks in a given disk group should have similar size and performance characteristics.', p_message_nlsid => 'ASM_DISK_SIZE_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_ASM10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'osm_instance', p_policy_name => 'ASM_DISK_SIZE'); COMMIT; -- ---------------------------------------------------------- -- Policy: Disk Group Depends on External Redundancy and has Unprotected Disks -- Policy ID: ASM_DATA_PROTECTION -- Uses Metric: DB_ASM_DISK_GROUP_SETTINGS -- -- Category: Storage -- Violation Level: Warning -- ASM Versions: 10gR1, 10gR2 -- Agent Version: 10gR2 DB version of osm_instance metadata -- Description: -- This will return one row for each disk group in violation -- Problem Code: 2 -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 2 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'DISK_GROUP_NAME'), mgmt_policy_key_col_cond.new('2',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'DISK_GROUP_NAME', p_url_link_template => 'database/osm/diskGroup?type=^TARGET_TYPE^&target=^TARGET_NAME^&oname=^DISK_GROUP_NAME^&event=edit&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- OSM INSTANCE MGMT_POLICY.CREATE_POLICY( p_target_type => 'osm_instance', p_policy_name => 'ASM_DATA_PROTECTION', p_metric_name => 'DB_ASM_DISK_GROUP_SETTINGS', p_policy_label_nlsid => 'ASM_DATA_PROTECTION_NAME', p_description => 'ASM_DATA_PROTECTION_DESC', p_description_nlsid => 'ASM_DATA_PROTECTION_DESC', p_impact => 'ASM_DATA_PROTECTION_IMPACT', p_impact_nlsid => 'ASM_DATA_PROTECTION_IMPACT', p_recommendation => 'ASM_DATA_PROTECTION_RECOM', p_recommendation_nlsid => 'ASM_DATA_PROTECTION_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Disk Group %disk_group_name% depends on external redundancy and has disks that are not mirrored or parity protected.', p_message_nlsid => 'ASM_DATA_PROTECTION_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_ASM10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'osm_instance', p_policy_name => 'ASM_DATA_PROTECTION'); COMMIT; -- ---------------------------------------------------------- -- Policy: Disk Group Contains Disks with Different Redundancy Attributes -- Policy ID: ASM_MIXED_REDUNDANCY -- Uses Metric: DB_ASM_DISK_GROUP_SETTINGS -- -- Category: Storage -- Violation Level: Warning -- ASM Versions: 10gR1, 10gR2 -- Agent Version: 10gR2 DB version of osm_instance metadata -- Description: -- This will return one row for each disk group in violation -- Problem Code: 3 -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 3 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'DISK_GROUP_NAME'), mgmt_policy_key_col_cond.new('3',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'DISK_GROUP_NAME', p_url_link_template => 'database/osm/diskGroup?type=^TARGET_TYPE^&target=^TARGET_NAME^&oname=^DISK_GROUP_NAME^&event=edit&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- OSM INSTANCE MGMT_POLICY.CREATE_POLICY( p_target_type => 'osm_instance', p_policy_name => 'ASM_MIXED_REDUNDANCY', p_metric_name => 'DB_ASM_DISK_GROUP_SETTINGS', p_policy_label_nlsid => 'ASM_MIXED_REDUNDANCY_NAME', p_description => 'ASM_MIXED_REDUNDANCY_DESC', p_description_nlsid => 'ASM_MIXED_REDUNDANCY_DESC', p_impact => 'ASM_MIXED_REDUNDANCY_IMPACT', p_impact_nlsid => 'ASM_MIXED_REDUNDANCY_IMPACT', p_recommendation => 'ASM_MIXED_REDUNDANCY_RECOM', p_recommendation_nlsid => 'ASM_MIXED_REDUNDANCY_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'Disk Group %disk_group_name% contains disks with different redundancy attributes. This may offer inconsistent levels of data protection.', p_message_nlsid => 'ASM_MIXED_REDUNDANCY_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_ASM10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'osm_instance', p_policy_name => 'ASM_MIXED_REDUNDANCY'); COMMIT; -- ---------------------------------------------------------- -- Policy: Disk Group with NORMAL or HIGH Redundancy has Mirrored or Parity Protected Disks -- Policy ID: ASM_UNNEEDED_REDUNDANCY -- Uses Metric: DB_ASM_DISK_GROUP_SETTINGS -- -- Category: Storage -- Violation Level: Informational -- ASM Versions: 10gR1, 10gR2 -- Agent Version: 10gR2 DB version of osm_instance metadata -- Description: -- This will return one row for each disk group in violation -- Problem Code: 4 -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE = 4 '; 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)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_key_value => mgmt_policy_key_col_cond_array( mgmt_policy_key_col_cond.new('%',1,'DISK_GROUP_NAME'), mgmt_policy_key_col_cond.new('4',0,'PROBLEM_CODE')), p_is_exception => 0, p_condition_operator => MGMT_GLOBAL.G_CONDITION_SQL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'DISK_GROUP_NAME', p_url_link_template => 'database/osm/diskGroup?type=^TARGET_TYPE^&target=^TARGET_NAME^&oname=^DISK_GROUP_NAME^&event=edit&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); -- OSM INSTANCE MGMT_POLICY.CREATE_POLICY( p_target_type => 'osm_instance', p_policy_name => 'ASM_UNNEEDED_REDUNDANCY', p_metric_name => 'DB_ASM_DISK_GROUP_SETTINGS', p_policy_label_nlsid => 'ASM_UNNEEDED_REDUNDANCY_NAME', p_description => 'ASM_UNNEEDED_REDUNDANCY_DESC', p_description_nlsid => 'ASM_UNNEEDED_REDUNDANCY_DESC', p_impact => 'ASM_UNNEEDED_REDUNDANCY_IMPACT', p_impact_nlsid => 'ASM_UNNEEDED_REDUNDANCY_IMPACT', p_recommendation => 'ASM_UNNEEDED_REDUNDANCY_RECOM', p_recommendation_nlsid => 'ASM_UNNEEDED_REDUNDANCY_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'NORMAL or HIGH redundancy disk group %disk_group_name% has disks that are mirrored or parity protected. Disk resources are wasted, and performance may be unnecessarily affected when both a disk and its owning disk group are providing data redundancy.', p_message_nlsid => 'ASM_UNNEEDED_REDUNDANCY_MESSAGE', p_auto_enable => MGMT_GLOBAL.G_TRUE, p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => l_ASM10gR1MetaVersion, p_category_list => l_categoryList, p_dflt_param_val_list => l_defaultSettingsList, p_viol_ctxt_list => l_violCtxtList); MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => 'osm_instance', p_policy_name => 'ASM_UNNEEDED_REDUNDANCY'); COMMIT; END; / SET DEFINE ON