Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.3/policy/upgrade_create_policies.sql /st_emdbsa_11.2/1 2011/05/20 10:15:53 mpawelko Exp $ Rem Rem upgrade_create_policies.sql Rem Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem upgrade_create_policies.sql - Upgrade create policies SQL script Rem Rem DESCRIPTION Rem During 11.2.0.0...11.2.0.3 upgrade, recreate affected policies Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem mpawelko 05/17/11 - add SET DEFINE OFF/ON Rem mpawelko 04/27/11 - 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; END; / SET DEFINE ON