Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\v102010\policy\config_policy

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\v102010\policy\config_policy_setup.sql

Rem 
Rem $Header: config_policy_setup.sql 19-may-2005.10:25:10 rreilly Exp $
Rem
Rem config_policy_setup.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem    NAME
Rem      config_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    rreilly     05/19/05 - bug 4381470 policies need start_meta_ver 
Rem    pbantis     04/28/05 - Add HA policies. 
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/21/05 - bug 4246935 convert installation metric 
Rem    rreilly     03/09/05 - bug 4208731 - remove incorrect l_paramlist 
Rem    rreilly     02/15/05 - version policies 
Rem    rreilly     01/24/05 - Add Configuration 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 Configuration Policies
--
-- ==========================================================

DECLARE

l_conditionSql        VARCHAR2(1000);
  l_categoryList        MGMT_CATEGORY_ARRAY;
  l_defaultSettingsList MGMT_POLICY_KEY_VAL_ARRAY;
  l_violCtxtList        MGMT_VIOL_CTXT_DEF_ARRAY;
  l_paramList           MGMT_POLICY_PARAM_DEF_ARRAY;
  l_defParamValList     MGMT_POLICY_PARAM_VAL_ARRAY;
  l_10gR1MetaVersion    CONSTANT VARCHAR2(3) := '3.0';

BEGIN

-- ----------------------------------------------------------
  -- Policy: Not Using Automatic Undo Management
  -- Uses Metric: DB_INIT_PARAMS
  --
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: 9.2 +
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'undo_management' has a value of
  --    'MANUAL'.
  --    Added by rreilly for 10gR2 GC - Dec 14, 2004 
  -- Review Status:  Sent for review
  -- ----------------------------------------------------------

l_conditionSql := 
     ':INIT_PARAM_NAME = ''undo_management'' ' ||
     'and upper(nvl(:INIT_PARAM_VALUE, ''MANUAL'')) = ''MANUAL'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_NAME',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=undo_management&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_VALUE'));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_UNDO_TBSP',
      p_metric_name => 'DB_INIT_PARAMS',
      p_policy_label_nlsid => 'NO_UNDO_TBSP_NAME',
      p_description => 'NO_UNDO_TBSP_DESC',
      p_description_nlsid => 'NO_UNDO_TBSP_DESC',
      p_impact => 'NO_UNDO_TBSP_IMPACT',
      p_impact_nlsid => 'NO_UNDO_TBSP_IMPACT',
      p_recommendation => 'NO_UNDO_TBSP_RECOM',
      p_recommendation_nlsid => 'NO_UNDO_TBSP_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database is not using automatic undo management.  This can cause unnecessary contention and performance issues.',
      p_message_nlsid => 'NO_UNDO_TBSP_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_UNDO_TBSP');      
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Not Using Spfile
  -- Uses Metric: DB_INIT_PARAMS
  --
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: 10gR1 +
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'spfile' has a value of NULL.
  --    Added by rreilly for 10gR2 GC - Dec 15, 2004 
  -- Review Status:
  -- ----------------------------------------------------------

l_conditionSql := 
     ':INIT_PARAM_NAME = ''spfile'' ' ||
     'and nvl(:INIT_PARAM_VALUE, ''NULL'') = ''NULL'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_NAME',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=spfile&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_VALUE'));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_SPFILE',
      p_metric_name => 'DB_INIT_PARAMS',
      p_policy_label_nlsid => 'NO_SPFILE_NAME',
      p_description => 'NO_SPFILE_DESC',
      p_description_nlsid => 'NO_SPFILE_DESC',
      p_impact => 'NO_SPFILE_IMPACT',
      p_impact_nlsid => 'NO_SPFILE_IMPACT',
      p_recommendation => 'NO_SPFILE_RECOM',
      p_recommendation_nlsid => 'NO_SPFILE_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database is not using spfile.  The changes made using ALTER SYSTEM commands will not persist.',
      p_message_nlsid => 'NO_SPFILE_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);
  
  MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_SPFILE');

COMMIT;

-- ----------------------------------------------------------
  -- Policy: Use of Non-Standard Initialization Parameters
  -- Uses Metric: DB_INIT_PARAMS
  --
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: 10gR1 +
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This policy is considered to be in violation if
  --    the there are any init params that start with a
  --    single underscore (_).  Parameters that start w/
  --    two underscores (__) are excluded from this check.
  --    This is because the 10gR2 db uses two underscores
  --    to id parameters that being used to save values.
  --    Added by rreilly for 10gR2 GC - Dec 15, 2004 
  -- Review Status:
  -- ----------------------------------------------------------

l_conditionSql := 
     ':INIT_PARAM_NAME like ''\_%'' ESCAPE ''\'' ' ||
     'and :INIT_PARAM_NAME not like ''\_\_%'' ESCAPE ''\'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_NAME',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=^INIT_PARAM_NAME^&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_VALUE'));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'HIDDEN_PARAMS',
      p_metric_name => 'DB_INIT_PARAMS',
      p_policy_label_nlsid => 'HIDDEN_PARAMS_NAME',
      p_description => 'HIDDEN_PARAMS_DESC',
      p_description_nlsid => 'HIDDEN_PARAMS_DESC',
      p_impact => 'HIDDEN_PARAMS_IMPACT',
      p_impact_nlsid => 'HIDDEN_PARAMS_IMPACT',
      p_recommendation => 'HIDDEN_PARAMS_RECOM',
      p_recommendation_nlsid => 'HIDDEN_PARAMS_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database is using non-standard initialization parameter %init_param_name%, that has a value of %init_param_value%.  Using non-standard initialization parameters may cause database problems that are difficult to diagnose.',
      p_message_nlsid => 'HIDDEN_PARAMS_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_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 => 'HIDDEN_PARAMS');      
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Not Using Automatic Shared Memory Management
  -- Uses Metric: DB_SGA_TARGET_INIT_PARAM
  -- 
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: 10gR1 +
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'sga_target' = 0.  To correct the problem
  --    users need to set sga_target to a number > 0 and 
  --    < sga_max_size.  statistics_level should be set to 'TYPICAL'
  --    when automatic shared memory management is being used.
  --    Added by rreilly for 10gR2 GC - Dec 15, 2004 
  -- Review Status:
  -- ----------------------------------------------------------

l_paramList := MGMT_POLICY_PARAM_DEF_ARRAY(
    MGMT_POLICY_PARAM_DEF(
      param_name => 'SGA_TARGET_TR',
      param_name_nlsid => 'SGA_TARGET_TR_NLSID',
      param_type => MGMT_POLICY.G_PARAM_TYPE_STRING));

l_defParamValList := MGMT_POLICY_PARAM_VAL_ARRAY(
    MGMT_POLICY_PARAM_VAL.NEW(
      p_param_name => 'SGA_TARGET_TR',
      p_info_threshold=>'0'));

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL,
      p_param_values => l_defParamValList));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'SGA_TARGET_VALUE',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=sga_target&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'SGA_MAX_SIZE_MB_VALUE',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=sga_max_size&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'STATISTICS_LEVEL_VALUE',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=statistics_level&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'SGA_TARGET',
      p_metric_name => 'DB_SGA_TARGET_INIT_PARAM',
      p_policy_label_nlsid => 'SGA_TARGET_NAME',
      p_description => 'SGA_TARGET_DESC',
      p_description_nlsid => 'SGA_TARGET_DESC',
      p_impact => 'SGA_TARGET_IMPACT',
      p_impact_nlsid => 'SGA_TARGET_IMPACT',
      p_recommendation => 'SGA_TARGET_RECOM',
      p_recommendation_nlsid => 'SGA_TARGET_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD,
      p_condition => 'SGA_TARGET_VALUE',
      p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_EQ,
      p_message => 'The database is not using Automatic Shared Memory Management. It simplifies the configuration and management of the SGA.',
      p_message_nlsid => 'SGA_TARGET_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_policy_param_list => l_paramList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'SGA_TARGET');      
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: STATISTICS_LEVEL Set to All
  -- Uses Metric: DB_INIT_PARAMS
  --
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: 10gR1 +
  -- Agent Version: 10.1.0.2 +
  -- Added In: 10.2 DB Control
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'statistics_level' has a value of
  --    'ALL'.  This means the user is collecting more 
  --    statistics than are necessary, which may lead to perf
  --    problems with the datbase.
  --    Added by rreilly for 10gR2 GC - Dec 14, 2004 
  -- Review Status:  
  -- ----------------------------------------------------------

l_conditionSql := 
     ':INIT_PARAM_NAME = ''statistics_level'' ' ||
     'and :INIT_PARAM_VALUE = ''ALL'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_NAME',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=statistics_level&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_VALUE'));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'STATISTICS_LEVEL_HIGH',
      p_metric_name => 'DB_INIT_PARAMS',
      p_policy_label_nlsid => 'STATISTICS_LEVEL_HIGH_NAME',
      p_description => 'STATISTICS_LEVEL_HIGH_DESC',
      p_description_nlsid => 'STATISTICS_LEVEL_HIGH_DESC',
      p_impact => 'STATISTICS_LEVEL_HIGH_IMPACT',
      p_impact_nlsid => 'STATISTICS_LEVEL_HIGH_IMPACT',
      p_recommendation => 'STATISTICS_LEVEL_HIGH_RECOM',
      p_recommendation_nlsid => 'STATISTICS_LEVEL_HIGH_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'STATISTICS_LEVEL is set to ALL.  Unnesessary timed OS and plan execution statistics are being collected. This  creates additional overhead on the system.',
      p_message_nlsid => 'STATISTICS_LEVEL_HIGH_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'STATISTICS_LEVEL_HIGH');      
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Disabled Automatic Statistics Collection
  -- Uses Metric: DB_INIT_PARAMS
  --
  -- Category: Configuration
  -- Violation Level: Critical
  -- DB Versions: 10gR1 +
  -- Agent Version: 10.1.0.2 +
  -- Added in:  10.2 DB Control
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'statistics_level' has a value of
  --    'BASIC'.  This means the user is not collecting the
  --    statistics that oracle needs, which may lead to
  --    problems with the database, esp with new db features.
  -- Review Status:  
  -- ----------------------------------------------------------

l_conditionSql := 
     ':INIT_PARAM_NAME = ''statistics_level'' ' ||
     'and :INIT_PARAM_VALUE = ''BASIC'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_NAME',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=statistics_level&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_VALUE'));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'STATISTICS_LEVEL',
      p_metric_name => 'DB_INIT_PARAMS',
      p_policy_label_nlsid => 'STATISTICS_LEVEL_NAME',
      p_description => 'STATISTICS_LEVEL_DESC',
      p_description_nlsid => 'STATISTICS_LEVEL_DESC',
      p_impact => 'STATISTICS_LEVEL_IMPACT',
      p_impact_nlsid => 'STATISTICS_LEVEL_IMPACT',
      p_recommendation => 'STATISTICS_LEVEL_RECOM',
      p_recommendation_nlsid => 'STATISTICS_LEVEL_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'STATISTICS_LEVEL is set to BASIC.  The collection of many important statistics, required by Oracle database features and functionality, are disabled.',
      p_message_nlsid => 'STATISTICS_LEVEL_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'STATISTICS_LEVEL');      
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Not Using Automatic PGA Management
  -- Uses Metric: DB_INIT_PARAMS
  --
  -- Category: Configuration
  -- Violation Level: Warning
  -- DB Versions: 9i +
  -- Agent Version: 10.1.0.2 +
  -- Added In: 10.2 DB Control
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'pga_aggregate_target' has a value of
  --    0 OR 'workarea_size_policy' has a value of 'MANUAL'.
  --    Automatic PGA Management improves how PGA memory is allocated.
  -- Review Status:  
  -- ----------------------------------------------------------

l_conditionSql := 
     '((:INIT_PARAM_NAME  = ''pga_aggregate_target'' AND ' ||
       ':INIT_PARAM_VALUE = ''0'') ' ||
       'OR (:INIT_PARAM_NAME = ''workarea_size_policy'' AND ' ||
            ':INIT_PARAM_VALUE=''MANUAL'')) ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_NAME',
      p_url_link_template => 'database/instance/memory?target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_VALUE'));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'AUTO_PGA',
      p_metric_name => 'DB_INIT_PARAMS',
      p_policy_label_nlsid => 'AUTO_PGA_NAME',
      p_description => 'AUTO_PGA_DESC',
      p_description_nlsid => 'AUTO_PGA_DESC',
      p_impact => 'AUTO_PGA_IMPACT',
      p_impact_nlsid => 'AUTO_PGA_IMPACT',
      p_recommendation => 'AUTO_PGA_RECOM',
      p_recommendation_nlsid => 'AUTO_PGA_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database is not using Automatic PGA memory management.  It simplifies and improves the way PGA memory is allocated.',
      p_message_nlsid => 'AUTO_PGA_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'AUTO_PGA');      
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Not Using Latest Version of the Optimizer
  -- Uses Metric: DB_OPTIMIZER_VERSION
  --
  -- Category: Configuration
  -- Violation Level: Warning
  -- DB Versions: All
  -- Agent Version: 10.1.0.2
  -- Added In: 10.2 DB Control
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'optimizer_feature_enable' has a value 
  --    that is less than the db version.
  --    Using older version of the optimizer can lead to execution
  --    plan or query performance issues.
  -- Review Status:  
  -- ----------------------------------------------------------

l_conditionSql := 
             ':INIT_PARAM_NAME = ''optimizer_features_enable'' and '||
             ':DB_VERSION not like :INIT_PARAM_VALUE || ''%'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_NAME',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=^INIT_PARAM_NAME^&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'INIT_PARAM_VALUE'),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'DB_VERSION'));

MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'OPTIMIZER_VERSION',
      p_metric_name => 'DB_OPTIMIZER_VERSION',
      p_policy_label_nlsid => 'OPTIMIZER_VERSION_NAME',
      p_description => 'OPTIMIZER_VERSION_DESC',
      p_description_nlsid => 'OPTIMIZER_VERSION_DESC',
      p_impact => 'OPTIMIZER_VERSION_IMPACT',
      p_impact_nlsid => 'OPTIMIZER_VERSION_IMPACT',
      p_recommendation => 'OPTIMIZER_VERSION_RECOM',
      p_recommendation_nlsid => 'OPTIMIZER_VERSION_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database is not using the latest optimizer version.  The new version of the optimizer has better information to help it determine the most efficient way to run a SQL Statement.',
      p_message_nlsid => 'OPTIMIZER_VERSION_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'OPTIMIZER_VERSION');      
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Insufficient Number of Redo Logs
  -- Uses Metric: DB_REDO_LOG_COUNT
  --
  -- Category: Configuration
  -- Violation Level: Warning
  -- DB Versions: All
  -- Agent Version: 10gR1 (10.1.0.2)
  -- Description: 
  --    This policy will return 1 violation for each
  --    database that has less than 3 redo logs.  The row returned 
  --    will contain the Redo Log Count, a list of the redo log files,
  --    and a list of the Redo Log Group numbers.
  -- ----------------------------------------------------------

l_conditionSql := 
    ':REDO_LOG_COUNT < 3 ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'REDO_LOG_COUNT',
      p_url_link_template => 'database/databaseObjectsSearch?event=search&otype=redolog&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'FILE_LIST',
      p_url_link_template => 'database/storage/redolog?oname=^GROUP_NUM_LIST^&event=edit&otype=REDOLOG&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'GROUP_NUM_LIST'));

-- ORACLE DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSUFF_REDO_LOGS',
      p_metric_name => 'DB_REDO_LOG_COUNT',
      p_policy_label_nlsid => 'INSUFF_REDO_LOGS_NAME',
      p_description => 'INSUFF_REDO_LOGS_DESC',
      p_description_nlsid => 'INSUFF_REDO_LOGS_DESC',
      p_impact => 'INSUFF_REDO_LOGS_IMPACT',
      p_impact_nlsid => 'INSUFF_REDO_LOGS_IMPACT',
      p_recommendation => 'INSUFF_REDO_LOGS_RECOM',
      p_recommendation_nlsid => 'INSUFF_REDO_LOGS_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database has an insufficient number of redo log files.  When the size and number of online redo logs are inadequate, LGWR will wait for ARCH to complete its writing to the archived log destination, before it overwrites that log. This can cause severe performance slowdowns during peak activity periods.',
      p_message_nlsid => 'INSUFF_REDO_LOGS_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSUFF_REDO_LOGS');

-- RAC DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'INSUFF_REDO_LOGS',
      p_metric_name => 'DB_REDO_LOG_COUNT',
      p_policy_label_nlsid => 'INSUFF_REDO_LOGS_NAME',
      p_description => 'INSUFF_REDO_LOGS_DESC',
      p_description_nlsid => 'INSUFF_REDO_LOGS_DESC',
      p_impact => 'INSUFF_REDO_LOGS_IMPACT',
      p_impact_nlsid => 'INSUFF_REDO_LOGS_IMPACT',
      p_recommendation => 'INSUFF_REDO_LOGS_RECOM',
      p_recommendation_nlsid => 'INSUFF_REDO_LOGS_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database has an insufficient number of redo log files.  When the size and number of online redo logs are inadequate, LGWR will wait for ARCH to complete its writing to the archived log destination, before it overwrites that log. This can cause severe performance slowdowns during peak activity periods.',
      p_message_nlsid => 'INSUFF_REDO_LOGS_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'rac_database',
      p_policy_name => 'INSUFF_REDO_LOGS');

COMMIT;

-- ----------------------------------------------------------
  -- Policy: Insufficient Number of Control Files
  -- Uses Metric: DB_CONTROL_FILE_COUNT
  --
  -- Category: Configuration
  -- Violation Level: Critical
  -- DB Versions: All
  -- Agent Version: 10gR1 (10.1.0.2)
  -- Description: 
  --    This policy will return 1 violation for each
  --    database that has only 1 control file.  The row returned 
  --    will contain the Control File Count and a list of the control files.
  -- ----------------------------------------------------------

l_conditionSql := 
    ':CONTROL_FILE_COUNT = 1 ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'CONTROL_FILE_COUNT',
      p_url_link_template => 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=control_files&cancelURL=^CANCEL_URL^',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'FILE_LIST'));

-- ORACLE DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSUFF_CONTROL_FILES',
      p_metric_name => 'DB_CONTROL_FILE_COUNT',
      p_policy_label_nlsid => 'INSUFF_CONTROL_FILES_NAME',
      p_description => 'INSUFF_CONTROL_FILES_DESC',
      p_description_nlsid => 'INSUFF_CONTROL_FILES_DESC',
      p_impact => 'INSUFF_CONTROL_FILES_IMPACT',
      p_impact_nlsid => 'INSUFF_CONTROL_FILES_IMPACT',
      p_recommendation => 'INSUFF_CONTROL_FILES_RECOM',
      p_recommendation_nlsid => 'INSUFF_CONTROL_FILES_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database has an insufficient number of control files. If the only copy of the control file is lost due to a media error, there will be unnecessary down time and other risks.',
      p_message_nlsid => 'INSUFF_CONTROL_FILES_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSUFF_CONTROL_FILES');

-- RAC DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'INSUFF_CONTROL_FILES',
      p_metric_name => 'DB_CONTROL_FILE_COUNT',
      p_policy_label_nlsid => 'INSUFF_CONTROL_FILES_NAME',
      p_description => 'INSUFF_CONTROL_FILES_DESC',
      p_description_nlsid => 'INSUFF_CONTROL_FILES_DESC',
      p_impact => 'INSUFF_CONTROL_FILES_IMPACT',
      p_impact_nlsid => 'INSUFF_CONTROL_FILES_IMPACT',
      p_recommendation => 'INSUFF_CONTROL_FILES_RECOM',
      p_recommendation_nlsid => 'INSUFF_CONTROL_FILES_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_CRITICAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The database has an insufficient number of control files. If the only copy of the control file is lost due to a media error, there will be unnecessary down time and other risks',
      p_message_nlsid => 'INSUFF_CONTROL_FILES_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'rac_database',
      p_policy_name => 'INSUFF_CONTROL_FILES');

COMMIT;

-- ----------------------------------------------------------
  -- Policy: Installation of JAccelerator (NCOMP)
  -- Uses Metric: DB_INSTALL_NCOMPS
  -- 
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: All
  -- Agent Version: 
  -- Description: 
  --    NOTES FROM Shyam: 
  --    This policy checks for ncomp Oracle JAccelerator installation. 
  --    Ideally we should also be checking to see if the target db is using java 
  --    source/class, for that we would need to define a metric to collect
  --    that info as part of db config snapshot. for now, we shall just check 
  --    the installation info in the ecm software components info in repository
  -- The Test:
  --    Major Version must be >= 10
  -- ----------------------------------------------------------

l_paramList := MGMT_POLICY_PARAM_DEF_ARRAY(
    MGMT_POLICY_PARAM_DEF(
      param_name => 'MAJOR_VERSION_TR',
      param_name_nlsid => 'MAJOR_VERSION_TR_NLSID',
      param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER));

l_defParamValList := MGMT_POLICY_PARAM_VAL_ARRAY(
    MGMT_POLICY_PARAM_VAL.NEW(
      p_param_name => 'MAJOR_VERSION_TR',
      p_info_threshold=>'10'));

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL,
      p_param_values => l_defParamValList));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'MAJOR_VERSION'),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'HOME_LOCATION'),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'HOST_NAME'),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'ORACLE_HOME',
      p_url_link_template => 'ecm/track/view/oracleHome$target=^HOST_NAME^$type=host$location=^HOME_LOCATION^?event=doLoad',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP));

-- ORACLE_DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSTALL_NCOMPS',
      p_metric_name => 'DB_INSTALL_NCOMPS',
      p_policy_label_nlsid => 'INSTALL_NCOMPS_NAME',
      p_description => 'INSTALL_NCOMPS_DESC',
      p_description_nlsid => 'INSTALL_NCOMPS_DESC',
      p_impact => 'INSTALL_NCOMPS_IMPACT',
      p_impact_nlsid => 'INSTALL_NCOMPS_IMPACT',
      p_recommendation => 'INSTALL_NCOMPS_RECOM',
      p_recommendation_nlsid => 'INSTALL_NCOMPS_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD,
      p_condition => 'MAJOR_VERSION',
      p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_GE,
      p_message => 'JAccelerator (NCOMP) is not installed.  Without JAccelerator Java VM performance will be sub-optimal.',
      p_message_nlsid => 'INSTALL_NCOMPS_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_policy_param_list => l_paramList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSTALL_NCOMPS');

-- RAC_DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'INSTALL_NCOMPS',
      p_metric_name => 'DB_INSTALL_NCOMPS',
      p_policy_label_nlsid => 'INSTALL_NCOMPS_NAME',
      p_description => 'INSTALL_NCOMPS_DESC',
      p_description_nlsid => 'INSTALL_NCOMPS_DESC',
      p_impact => 'INSTALL_NCOMPS_IMPACT',
      p_impact_nlsid => 'INSTALL_NCOMPS_IMPACT',
      p_recommendation => 'INSTALL_NCOMPS_RECOM',
      p_recommendation_nlsid => 'INSTALL_NCOMPS_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD,
      p_condition => 'MAJOR_VERSION',
      p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_GE,
      p_message => 'JAccelerator (NCOMP) is not installed.  Without JAccelerator Java VM performance will be sub-optimal.',
      p_message_nlsid => 'INSTALL_NCOMPS_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_policy_param_list => l_paramList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'rac_database',
      p_policy_name => 'INSTALL_NCOMPS');
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Recovery Area Location Not Set
  -- Uses Metric: HA_RECOVERY_AREA
  --
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: 10.1 +
  -- Agent Version: 10.1 +
  -- Description: 
  --    This policy is considered to be in violation if
  --    the init param 'db_recovery_file_dest' does not have a value.
  -- ----------------------------------------------------------

l_conditionSql := 
     'upper(nvl(:RECOVERY_AREA_LOCATION, ''NULL'')) = ''NULL'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'RECOVERY_AREA_LOCATION',
      p_url_link_template => 'database/instance/recovery?target=^TARGET_NAME^&type=^TARGET_TYPE^&setFocus=FRA#FRA',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP));

-- ORACLE DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_RECOVERY_AREA',
      p_metric_name => 'HA_RECOVERY_AREA',
      p_policy_label_nlsid => 'NO_RECOVERY_AREA_NAME',
      p_description => 'Checks for recovery area not set',
      p_description_nlsid => 'NO_RECOVERY_AREA_DESC',
      p_impact => 'Not setting the recovery area location will result in a divided storage location for all recovery components.',
      p_impact_nlsid => 'NO_RECOVERY_AREA_IMPACT',
      p_recommendation => 'It is recommended the recovery area location be set to provide a unified storage location for all recovery components.',
      p_recommendation_nlsid => 'NO_RECOVERY_AREA_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The recovery area location is not set. Setting the recovery area location provides a unified storage location for all recovery components.',
      p_message_nlsid => 'NO_RECOVERY_AREA_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_RECOVERY_AREA');

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'RECOVERY_AREA_LOCATION',
      p_url_link_template => 'rac/racRecovery?target=^TARGET_NAME^&type=^TARGET_TYPE^&setFocus=FRA#FRA',
      p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP));

-- RAC DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_RECOVERY_AREA',
      p_metric_name => 'HA_RECOVERY_AREA',
      p_policy_label_nlsid => 'NO_RECOVERY_AREA_NAME',
      p_description => 'Checks for recovery area not set',
      p_description_nlsid => 'NO_RECOVERY_AREA_DESC',
      p_impact => 'Not setting the recovery area location will result in a divided storage location for all recovery components.',
      p_impact_nlsid => 'NO_RECOVERY_AREA_IMPACT',
      p_recommendation => 'It is recommended the recovery area location be set to provide a unified storage location for all recovery components.',
      p_recommendation_nlsid => 'NO_RECOVERY_AREA_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The recovery area location is not set. Setting the recovery area location provides a unified storage location for all recovery components.',
      p_message_nlsid => 'NO_RECOVERY_AREA_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_RECOVERY_AREA');
  
  COMMIT;

-- ----------------------------------------------------------
  -- Policy: Force Logging Disabled
  -- Uses Metric: HA_INFO
  --
  -- Category: Configuration
  -- Violation Level: Informational
  -- DB Versions: 9.2 +
  -- Agent Version: 10.1 +
  -- Description: 
  --    This policy is considered to be in violation if
  --    the Data Guard Broker is being used and the primary
  --    datbase has force logging disabled.
  -- ----------------------------------------------------------

l_conditionSql := 
     'upper(nvl(:FORCE_LOGGING, ''NULL'')) = ''NO'' ';

l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_CONFIGURATION));

l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY(
    MGMT_POLICY_KEY_VAL.NEW(
      p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL));

l_violCtxtList := MGMT_VIOL_CTXT_DEF_ARRAY(
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'FORCE_LOGGING'),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'DATABASE_ROLE'),
    MGMT_VIOL_CTXT_DEF.NEW(
      p_metric_column => 'DG_BROKER_START'));

-- ORACLE DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_FORCE_LOGGING',
      p_metric_name => 'HA_INFO',
      p_policy_label_nlsid => 'NO_FORCE_LOGGING_NAME',
      p_description => 'When Data Guard Broker is being used, checks the primary database for disabled force logging',
      p_description_nlsid => 'NO_FORCE_LOGGING_DESC',
      p_impact => 'The primary database is not in force logging mode.  As a result unlogged direct writes in the primary database cannot be propagated to the standby database.',
      p_impact_nlsid => 'NO_FORCE_LOGGING_IMPACT',
      p_recommendation => 'The primary database should be put in force logging mode using ALTER DATABASE FORCE LOGGING.',
      p_recommendation_nlsid => 'NO_FORCE_LOGGING_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The primary database is not in force logging mode.  As a result unlogged direct writes in the primary database cannot be propagated to the standby database.',
      p_message_nlsid => 'NO_FORCE_LOGGING_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_FORCE_LOGGING');

-- RAC DATABASE
  MGMT_POLICY.CREATE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_FORCE_LOGGING',
      p_metric_name => 'HA_INFO',
      p_policy_label_nlsid => 'NO_FORCE_LOGGING_NAME',
      p_description => 'When Data Guard Broker is being used, checks the primary database for disabled force logging',
      p_description_nlsid => 'NO_FORCE_LOGGING_DESC',
      p_impact => 'The primary database is not in force logging mode.  As a result unlogged direct writes in the primary database cannot be propagated to the standby database.',
      p_impact_nlsid => 'NO_FORCE_LOGGING_IMPACT',
      p_recommendation => 'The primary database should be put in force logging mode using ALTER DATABASE FORCE LOGGING.',
      p_recommendation_nlsid => 'NO_FORCE_LOGGING_RECOM',
      p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
      p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL,
      p_condition => l_conditionSql,
      p_message => 'The primary database is not in force logging mode.  As a result unlogged direct writes in the primary database cannot be propagated to the standby database.',
      p_message_nlsid => 'NO_FORCE_LOGGING_MESSAGE',
      p_auto_enable => MGMT_GLOBAL.G_TRUE,
      p_cs_consider_percentage => MGMT_GLOBAL.G_FALSE,
      p_start_type_meta_ver => l_10gR1MetaVersion,
      p_category_list => l_categoryList,
      p_dflt_param_val_list => l_defaultSettingsList,
      p_viol_ctxt_list => l_violCtxtList);

MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_FORCE_LOGGING');
  
  COMMIT;

END;
/

SET DEFINE ON

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de