Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/policy/storage_policy_setup.sql /st_emdbsa_11.2/2 2008/10/02 14:03:41 mnihalan Exp $ Rem Rem storage_policy_setup.sql Rem Rem Copyright (c) 2004, 2008, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem storage_policy_setup.sql - Configuration Policies Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem !!!!!!! Please update UPGRADE script when updates are made. !!!!!!! Rem !!!!!!! !!!!!!! Rem !!!!!!! Whenever policies are created, removed or altered !!!!!!! Rem !!!!!!! Update the appropriate upgrade and downgrade files !!!!!!! Rem !!!!!!! in the v* directory with the latest version number !!!!!!! Rem Rem MODIFIED (MM/DD/YY) Rem mnihalan 09/25/08 - Fix bug 7395749 Rem yozhang 09/24/08 - Fix bug 6510791 in 11.2DBC Rem rreilly 07/28/05 - bug 4520660 add key checks to prevent over Rem execution of checks Rem rreilly 07/14/05 - PLEASE UPDATE UPGRADE AND DOWNGRADE SCRIPTS! Rem rreilly 07/13/05 - bug 4380127 segment level policies not be Rem out-of-box Rem rreilly 07/13/05 - bug 4380127 make dictionary and non-assm Rem policies NOT be out-of-box Rem rreilly 07/01/05 - bug 4294495 check extents on migrated locally Rem manged tbsp Rem rreilly 06/10/05 - fix reorg wizard links Rem rreilly 05/19/05 - bug 3720871 check for assm tbsp only in 9.2+ Rem rreilly 05/19/05 - bug 4381470 policies need start_meta_ver Rem rreilly 04/22/05 - make sure people update the upgrade scripts Rem rreilly 04/22/05 - fix for policy upgrade, auto assign to existing Rem dbs Rem rreilly 03/09/05 - bug 4208731 - remove incorrect l_paramlist Rem rreilly 02/15/05 - version policies Rem rreilly 01/24/05 - Add Storage Policies Rem rreilly 12/13/04 - Created Rem -- SET ECHO ON -- SET FEEDBACK 1 -- SET NUMWIDTH 10 -- SET LINESIZE 80 -- SET TRIMSPOOL ON -- SET TAB OFF -- SET PAGESIZE 100 SET DEFINE OFF -- ========================================================== -- -- CREATE Storage 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 (''MGMT_VIEW'',''OUTLN'',''ANONYMOUS'',''DMSYS'',''LBACSYS'',''WKPROXY'',''TRACESVR'',''SCOTT'',''ADAMS'',''BLAKE'',''CLARK'',''JONES'',''HR'',''PM'',''OE'',''SH'',''IX'') ' || 'AND :USER_NAME NOT IN (SELECT DISTINCT schema_name FROM V$SYSAUX_OCCUPANTS) ' || '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: 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'' '; 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 (''MGMT_VIEW'',''OUTLN'',''ANONYMOUS'',''DMSYS'',''LBACSYS'',''WKPROXY'',''TRACESVR'',''SCOTT'',''ADAMS'',''BLAKE'',''CLARK'',''JONES'',''HR'',''PM'',''OE'',''SH'',''IX'') ' || 'AND :OBJECT_OWNER NOT IN (SELECT DISTINCT schema_name FROM V$SYSAUX_OCCUPANTS) ' || '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 (SELECT DISTINCT schema_name FROM V$SYSAUX_OCCUPANTS) ' || '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: 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