Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/policy/upgrade_create_policies.sql /st_emdbsa_11.2/6 2009/06/15 12:13:34 yozhang Exp $ Rem Rem upgrade_create_policies.sql Rem Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. Rem 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 yozhang 06/10/09 - Fix bug 8477284: AUTO_PGA Rem nzhao 02/23/09 - Bug backports between Rem EMDBGC_10.2.0.5.0_GENERIC_081008 and Rem EMDBGC_10.2.0.5.0_GENERIC_090128 to Rem EMDBSA_11.2.0.0.0_LINUX_090127 Rem yozhang 02/18/09 - Fix bug 7641873: rename policy name for Rem HIDDEN_PARAMS Rem yozhang 09/25/08 - Fix bug 6442503 Rem yozhang 09/24/08 - Fix bug 7298709 Rem yozhang 09/24/08 - Fix bug 6510791 in 11.2DBC Rem mnihalan 09/25/08 - Fix bug 7395749 Rem mnihalan 05/16/08 - Delete policies Rem mnihalan 05/16/08 - Created Rem -- -- 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_10gR1MetaVersion CONSTANT VARCHAR2(3) := '3.0'; BEGIN -- ---------------------------------------------------------- -- 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: 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'' '; 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: 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(:INIT_PARAM_VALUE) = ''MANUAL'' OR :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('undo_management',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('undo_tablespace',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&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: Use of Non-Standard Initialization Parameters -- Policy ID: HIDDEN_PARAMS -- 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 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 like ''\_%'' ESCAPE ''\'' and :INIT_PARAM_NAME not like ''\_\_%'' ESCAPE ''\'' and :INIT_PARAM_NAME != ''_awr_flush_threshold_metrics'') or :INIT_PARAM_NAME=''event'' '; 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_PARAMS2', p_metric_name => 'DB_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_PARAMS2'); COMMIT; -- ---------------------------------------------------------- -- Policy: Setting TIMED_STATISTICS to TRUE -- Policy ID: TIMED_STATISTICS -- Uses Metric: DB_INIT_PARAMS -- -- Category: Configuration -- Violation Level: Critical -- DB Versions: 9gR2 + -- 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 'timed_statistics' has a value of -- 'FALSE'. This means the user is not collecting the -- statistics that oracle needs. -- Review Status: -- ---------------------------------------------------------- l_conditionSql := ':INIT_PARAM_VALUE = ''FALSE'' '; 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('timed_statistics',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=timed_statistics&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 => 'TIMED_STATISTICS', p_metric_name => 'DB_INIT_PARAMS', p_policy_label_nlsid => 'TIMED_STATISTICS_NAME', p_description => 'TIMED_STATISTICS_DESC', p_description_nlsid => 'TIMED_STATISTICS_DESC', p_impact => 'TIMED_STATISTICS_IMPACT', p_impact_nlsid => 'TIMED_STATISTICS_IMPACT', p_recommendation => 'TIMED_STATISTICS_RECOM', p_recommendation_nlsid => 'TIMED_STATISTICS_RECOM', p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => l_conditionSql, p_message => 'TIMED_STATISTICS is set to FALSE.', p_message_nlsid => 'TIMED_STATISTICS_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 => 'TIMED_STATISTICS'); 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 (''MGMT_VIEW'',''OUTLN'',''ANONYMOUS'',''DMSYS'',''LBACSYS'',''WKPROXY'',''TRACESVR'',''SCOTT'',''ADAMS'',''BLAKE'',''CLARK'',''JONES'',''HR'',''PM'',''OE'',''SH'',''IX'') ' || 'AND :USER_NAME NOT IN (''CTXSYS'',''DMSYS'',''MDSYS'',''OLAPSYS'',''ORDPLUGINS'',''ORDSYS'',''PERFSTAT'',''SI_INFORMTN_SCHEMA'',''SYS'',''SYSMAN'',''SYSTEM'',''WKSYS'',''WMSYS'',''XDB'') ' || 'AND :USER_NAME NOT LIKE ''FLOWS!_%'' 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_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: 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 (''MGMT_VIEW'',''OUTLN'',''ANONYMOUS'',''DMSYS'',''LBACSYS'',''WKPROXY'',''TRACESVR'',''SCOTT'',''ADAMS'',''BLAKE'',''CLARK'',''JONES'',''HR'',''PM'',''OE'',''SH'',''IX'') ' || 'AND :OBJECT_OWNER NOT IN (''CTXSYS'',''DMSYS'',''MDSYS'',''OLAPSYS'',''ORDPLUGINS'',''ORDSYS'',''PERFSTAT'',''SI_INFORMTN_SCHEMA'',''SYS'',''SYSMAN'',''SYSTEM'',''WKSYS'',''WMSYS'',''XDB'') ' || 'AND :OBJECT_OWNER NOT LIKE ''FLOWS!_%'' 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_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 (''MGMT_VIEW'',''OUTLN'',''ANONYMOUS'',''DMSYS'',''LBACSYS'',''WKPROXY'',''TRACESVR'',''SCOTT'',''ADAMS'',''BLAKE'',''CLARK'',''JONES'',''HR'',''PM'',''OE'',''SH'',''IX'') ' || 'AND :OBJECT_OWNER NOT IN (''CTXSYS'',''DMSYS'',''MDSYS'',''OLAPSYS'',''ORDPLUGINS'',''ORDSYS'',''PERFSTAT'',''SI_INFORMTN_SCHEMA'',''SYS'',''SYSMAN'',''SYSTEM'',''WKSYS'',''WMSYS'',''XDB'') ' || 'AND :OBJECT_OWNER NOT LIKE ''FLOWS!_%'' 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_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: 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); 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); 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); -- 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); 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: Not Using Automatic PGA Management -- Policy ID: AUTO_PGA -- Uses Metric: DB_INIT_PARAMS_PGA -- -- 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_PGA2', p_metric_name => 'DB_INIT_PARAMS_PGA', 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_PGA2'); COMMIT; END; / SET DEFINE ON