Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\v102010\policy\storage_policy_setup.sql
Rem Rem $Header: storage_policy_setup.sql 14-jul-2005.13:59:28 rreilly Exp $ Rem Rem storage_policy_setup.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem storage_policy_setup.sql - Configuration Policies Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem !!!!!!! Please update UPGRADE script when updates are made. !!!!!!! Rem Rem MODIFIED (MM/DD/YY) Rem xshen 06/07/05 - xshen_bug-4411021 Rem xchen 06/07/05 - bug 4411021 upgrade changes for beta 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 -- 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 -- 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)); 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 -- 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 := ':PROBLEM_CODE = 2 and ' || ':USER_NAME not in ' || '(''SYS'', ''SYSTEM'', ''SYSMAN'', ''CTXSYS'', ''MDSYS'', ' || ' ''ORDSYS'', ''ORDPLUGINS'', ''OLAPSYS'', ''DBSNMP'', ' || ' ''MGMT_VIEW'', ''OUTLN'', ''ANONYMOUS'', ''DMSYS'', ''EXFSYS'', ' || ' ''LBACSYS'', ''SI_INFORMTN_SCHEMA'', ''SYSMAN'', ''WKPROXY'', ' || ' ''WKSYS'', ''WK_TEST'', ''WMSYS'', ''XDB'', ''TRACESVR'', ' || ' ''SCOTT'', ''ADAMS'', ''BLAKE'', ''CLARK'', ''JONES'', ''HR'', ' || ' ''TSMSYS'', ''PM'', ''OE'', ''SH'', ''IX'') '; l_categoryList := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_STORAGE)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); 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 -- 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)); 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_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_SEGSPACE_MGMT'); -- 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_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_SEGSPACE_MGMT'); COMMIT; -- ---------------------------------------------------------- -- Policy: Dictionary Managed Tablespaces -- 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)); 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_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_DICTIONARY'); -- 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_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_DICTIONARY'); COMMIT; -- ---------------------------------------------------------- -- Policy: Tablespaces Containing Rollback and Data Segments -- 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 := ':PROBLEM_CODE = 3 and ' || ':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?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 -- 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 <tbsp name>; -- 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)); 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 -- 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 <tbsp name>; -- 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 -- Review Status: -- ---------------------------------------------------------- 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)); 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 Dictionary Managed Tablespaces -- 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. -- ---------------------------------------------------------- l_conditionSql := ':TABLESPACE_NAME != ''SYSTEM'' AND ' || ':CONTENTS = ''PERMANENT'' AND ' || ':EXTENT_MANAGEMENT = ''DICTIONARY'' AND ' || '(:PCTINCREASE !=0 OR ' || '(:INITIAL_EXTENT > 0 AND ' || 'MOD(:NEXT_EXTENT,:INITIAL_EXTENT) != 0)) '; l_categoryList := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_STORAGE)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'TABLESPACE_NAME', p_url_link_template => 'database/storage/tablespace?oname=^TABLESPACE_NAME^&otype=TABLESPACE&event=edit&cancelURL=^CANCEL_URL^&target=^TARGET_NAME^&type=^TARGET_TYPE^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => '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 -- 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 := ':PROBLEM_CODE = 4 and ' || ':OBJECT_OWNER not in ' || '(''SYS'', ''SYSTEM'', ''SYSMAN'', ''CTXSYS'', ''MDSYS'', ' || ' ''ORDSYS'', ''ORDPLUGINS'', ''OLAPSYS'', ''DBSNMP'', ' || ' ''MGMT_VIEW'', ''OUTLN'', ''ANONYMOUS'', ''DMSYS'', ''EXFSYS'', ' || ' ''LBACSYS'', ''SI_INFORMTN_SCHEMA'', ''SYSMAN'', ''WKPROXY'', ' || ' ''WKSYS'', ''WK_TEST'', ''WMSYS'', ''XDB'', ''TRACESVR'', ' || ' ''SCOTT'', ''ADAMS'', ''BLAKE'', ''CLARK'', ''JONES'', ''HR'', ' || ' ''TSMSYS'', ''PM'', ''OE'', ''SH'', ''IX'') '; l_categoryList := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_STORAGE)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); 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^^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_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 => 'SEG_NONSYS_SEG_IN_SYS_TS'); -- 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_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 => 'SEG_NONSYS_SEG_IN_SYS_TS'); COMMIT; -- ---------------------------------------------------------- -- Policy: Segment in Dictionary Tablespace with Non-Uniform Extent Size -- 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 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. -- Problem Code: >0, <4 : 1,2,3 -- ---------------------------------------------------------- l_conditionSql := ':PROBLEM_CODE > 0 AND :PROBLEM_CODE < 4 and ' || ':OBJECT_OWNER not in ' || '(''SYS'', ''SYSTEM'', ''SYSMAN'', ''CTXSYS'', ''MDSYS'', ' || ' ''ORDSYS'', ''ORDPLUGINS'', ''OLAPSYS'', ''DBSNMP'', ' || ' ''MGMT_VIEW'', ''OUTLN'', ''ANONYMOUS'', ''DMSYS'', ''EXFSYS'', ' || ' ''LBACSYS'', ''SI_INFORMTN_SCHEMA'', ''SYSMAN'', ''WKPROXY'', ' || ' ''WKSYS'', ''WK_TEST'', ''WMSYS'', ''XDB'', ''TRACESVR'', ' || ' ''SCOTT'', ''ADAMS'', ''BLAKE'', ''CLARK'', ''JONES'', ''HR'', ' || ' ''TSMSYS'', ''PM'', ''OE'', ''SH'', ''IX'') '; l_categoryList := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_STORAGE)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL)); 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^^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_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 => 'SEG_EXT_GROWTH_VIO'); -- 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_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 => 'SEG_EXT_GROWTH_VIO'); COMMIT; -- ---------------------------------------------------------- -- Policy: Insufficient Redo Log Size -- 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 -- 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)); 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 -- 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)); 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 -- 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)); 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 -- 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)); 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
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de