Rem drv: Rem Rem $Header: policy_downgrade.sql 29-jul-2005.09:23:52 rreilly Exp $ Rem Rem policy_downgrade.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem policy_downgrade.sql - Rem Rem DESCRIPTION Rem Moving downgrade script from v102010 folder since it did not make Rem the BETA cut. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rreilly 07/29/05 - Rem rreilly 07/14/05 - rreilly_bug-4373323 Rem rreilly 07/14/05 - Created Rem SET DEFINE OFF BEGIN DECLARE l_ruleObj ECM_POLICY_RULE; l_colList ECM_RULE_COLUMN_LIST; l_paramList ECM_RULE_PARAM_LIST; l_ruleCriteria ECM_POLICY_RULE_CRITERIA; l_targetCriteria ECM_POLICY_TARGET_CRITERIA; l_policyId MGMT_POLICY_GROUP.POLICY_ID%TYPE; BEGIN -- ------------------------------------------------------------------------- -- Database Configuration Policy Rules and Group -- ------------------------------------------------------------------------- -- Use of non-standard initialization parameters -- bug 3763983 ignore hidden params with two underscores l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('PARAM_NAME', 'Y', 'Y', 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=^PARAM_NAME^', 'Parameter Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PARAM_VALUE', 'N', 'Y', NULL, 'Parameter Value', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Configuration', 'Use of Non-Standard Initialization Parameters', '1.0', 'Checks for use of non-standard initialization parameters', 'QUERY', 'select p.name as PARAM_NAME, ' || 'p.value as PARAM_VALUE, ' || 's.target_guid as TARGET_GUID ' || 'from mgmt_db_init_params_ecm p, ' || 'mgmt_ecm_gen_snapshot s ' || 'where p.name like ''\_%'' ESCAPE ''\'' and ' || 'p.name not like ''\_\_%'' ESCAPE ''\'' and ' || 'p.ecm_snapshot_id = s.snapshot_guid and ' || 's.is_current = ''Y''', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'Non-standard initialization parameters are being used. ' || 'These may have been implemented based on poor advice or incorrect ' || 'assumptions. In particular, parameters associated with SPIN_COUNT ' || 'on latches and undocumented optimizer features can cause a great ' || 'deal of problem that can require considerable investigation.', 'Avoid use of non-standard initialization parameters.', l_colList, null, 'HIDDEN_PARAMS'); COMMIT; -- Insufficient number of control files. Should be at least 2. l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('INSUFF_CONTROL_FILES', 'Y', 'N', NULL, 'Insufficient Control Files', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('CONTROL_FILE_COUNT', 'Y', 'Y', 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=control_files', 'Control File Count', MGMT_ECM_POLICY.CONS_NUMBER_FORMAT), ECM_RULE_COLUMN('FILE_NAME', 'N', 'Y', NULL, 'File Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Configuration', 'Insufficient Number of Control Files', '1.0', 'Checks for use of a single control file', 'QUERY', 'select ''INSUFF_CONTROL_FILES'' as INSUFF_CONTROL_FILES, c.cnt as CONTROL_FILE_COUNT, f.file_name as FILE_NAME, s.target_guid as TARGET_GUID from mgmt_db_controlfiles_ecm f, mgmt_ecm_gen_snapshot s, (select count(*) as CNT, ecm_snapshot_id as ECM_SNAPSHOT_ID from mgmt_db_controlfiles_ecm group by ecm_snapshot_id) c where f.ecm_snapshot_id = c.ecm_snapshot_id and c.cnt = 1 and f.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO'')))', 'rownum > 0', MGMT_ECM_POLICY.CONS_HIGH, null, -- Impact of Problem -- Insufficient Number of Control Files 'Use at least two control files that are multiplexed on different disks.', l_colList, null, 'INSUFF_CONTROL_FILES'); COMMIT; -- Insufficient number of redo logs. Should be at least 3. l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('INSUFF_REDO_LOGS', 'Y', 'N', NULL, 'Insufficient Redo Logs', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('REDO_LOG_COUNT', 'Y', 'Y', 'database/databaseObjectsSearch?event=search&otype=redolog&target=^TARGET_NAME^&type=^TARGET_TYPE^', 'Redo Log Count', MGMT_ECM_POLICY.CONS_NUMBER_FORMAT), ECM_RULE_COLUMN('FILE_NAME', 'N', 'Y', 'database/storage/redolog?oname=^GROUP_NUM^&event=edit&otype=REDOLOG&target=^TARGET_NAME^&type=^TARGET_TYPE^', 'File Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('GROUP_NUM', 'N', 'N', NULL, 'Group Num', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Configuration', 'Insufficient Number of Redo Logs', '1.0', 'Checks for use of less than three redo logs', 'QUERY', 'select ''INSUFF_REDO_LOGS'' as INSUFF_REDO_LOGS, c.cnt as REDO_LOG_COUNT, ecm_util.concat_col( ''f.file_name'', ''mgmt_db_redologs_ecm f'', ''f.ecm_snapshot_id = '''''' || s.snapshot_guid || '''''' ORDER BY f.group_num, f.file_name'', '','', ''2'') as FILE_NAME, ecm_util.concat_col( ''f.group_num'', ''mgmt_db_redologs_ecm f'', ''f.ecm_snapshot_id = '''''' || s.snapshot_guid || '''''' ORDER BY f.group_num, f.file_name'', '','', ''2'') as GROUP_NUM, s.target_guid as TARGET_GUID from mgmt_ecm_gen_snapshot s, (select count(*) as CNT, ecm_snapshot_id as ECM_SNAPSHOT_ID from mgmt_db_redologs_ecm group by ecm_snapshot_id) c where c.cnt < 3 and c.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO'')))', 'rownum > 0', MGMT_ECM_POLICY.CONS_MEDIUM, null, -- Impact of Problem -- Insufficient Number of Redo Logs, 'Use a minimum of two redo log files to guarantee that one is ' || 'available for writing while the other is being archived.', l_colList, null, 'INSUFF_REDO_LOGS'); COMMIT; -- Insufficient redo log size. Should be at least 1 Mb. l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('GROUP_NUM', 'N', 'N', NULL, 'Group Num', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('FILE_NAME', 'Y', 'Y', 'database/storage/redolog?oname=^GROUP_NUM^&event=edit&otype=REDOLOG&target=^TARGET_NAME^&type=^TARGET_TYPE^', 'File Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('FILE_SIZE_MB', 'N', 'Y', NULL, 'File Size (MB)', MGMT_ECM_POLICY.CONS_NUMBER_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Insufficient Redo Log Size', '1.0', 'Checks for redo log files less than 1 Mb', 'QUERY', 'select f.group_num as GROUP_NUM, f.file_name as FILE_NAME, f.logsize/1024/1024 as FILE_SIZE_MB, s.target_guid as TARGET_GUID from mgmt_db_redologs_ecm f, mgmt_ecm_gen_snapshot s where f.logsize < (1024 * 1024) and f.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO'')))', 'rownum > 0', MGMT_ECM_POLICY.CONS_HIGH, 'Redo logs are too small. Small redo logs cause system ' || 'checkpoints to continuously put a high load on the buffer cache ' || 'and I/O system.', 'Increase size of the redo logs to at least 1 Mb.', l_colList, null, 'SMALL_REDO_LOGS'); COMMIT; -- Spfile usage l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('PARAM_NAME', 'Y', 'Y', 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=^PARAM_NAME^', 'Parameter Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PARAM_VALUE', 'N', 'Y', NULL, 'Parameter Value', MGMT_ECM_POLICY.CONS_NUMBER_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Configuration', 'Not Using Spfile', '1.0', 'Checks for spfile not being used', 'QUERY', 'select p.name as PARAM_NAME, ' || 'nvl(p.value, ''null'') as PARAM_VALUE, ' || 's.target_guid as TARGET_GUID ' || 'from mgmt_db_init_params_ecm p, ' || 'mgmt_ecm_gen_snapshot s ' || 'where p.name=''spfile'' and ' || 'p.value is null and ' || 'p.ecm_snapshot_id = s.snapshot_guid and ' || 's.is_current = ''Y''', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, null, -- Impact of Problem -- Not Using Spfile 'Use server side parameter file to update changes dynamically.', l_colList, null, 'NO_SPFILE'); COMMIT; -- Rollback segments in SYSTEM tablespace -- Exclude the default SYSTEM rbs -- Only check targets that has undo_management init param set to MANUAL OR pre-9i dbs. l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('RBS_NAME', 'Y', 'Y', NULL, 'Rollback Segment Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Rollback in SYSTEM Tablespace', '1.0', 'Checks for rollback segments in SYSTEM tablespace', 'QUERY', 'select r.rollname as RBS_NAME, s.target_guid as TARGET_GUID from mgmt_db_rollback_segs_ecm r, mgmt_db_init_params_ecm p, mgmt_ecm_gen_snapshot s where r.tablespace_name = ''SYSTEM'' and r.rollname != ''SYSTEM'' and r.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO''))) and r.ecm_snapshot_id = p.ecm_snapshot_id (+) and p.name (+) = ''undo_management'' and upper(nvl(p.value,''MANUAL'')) = ''MANUAL''', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, null, -- Impact of Problem -- Rollback in SYSTEM Tablespace 'Use a tablespace dedicated to undo instead of the SYSTEM tablespace.', l_colList, null, 'RBS_IN_SYSTEM'); COMMIT; -- Non-uniform extent size on dictionary managed tablespace -- Check for PERMANENT, NON-SYSTEM DICTIONARY tablespace that has: -- 1. Pct_Increase != 0 or -- 2. Next Extent value is not the multiple of the Initial Extent. l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('TBSP_NAME', 'Y', 'Y', 'database/storage/tablespace?oname=^TBSP_NAME^&otype=TABLESPACE&event=edit&cancelURL=^CANCEL_URL^&target=^TARGET_NAME^&type=^TARGET_TYPE^', 'Tablespace Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PCTINCREASE', 'N', 'Y', NULL, 'PctIncrease', MGMT_ECM_POLICY.CONS_NUMBER_FORMAT), ECM_RULE_COLUMN('INITIALEXTENT', 'N', 'Y', NULL, 'Initial Extent', MGMT_ECM_POLICY.CONS_NUMBER_FORMAT), ECM_RULE_COLUMN('NEXTEXTENT', 'N', 'Y', NULL, 'Next Extent', MGMT_ECM_POLICY.CONS_NUMBER_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Non-uniform Default Extent Size', '1.0', 'Checks for tablespaces with non-uniform default extent size', 'QUERY', 'select tbsp.tablespace_name as TBSP_NAME, tbsp.initial_ext_size as INITIALEXTENT, tbsp.next_extent as NEXTEXTENT, tbsp.increment_by as PCTINCREASE, s.target_guid as TARGET_GUID from mgmt_db_tablespaces_ecm tbsp, mgmt_ecm_gen_snapshot s where tbsp.tablespace_name != ''SYSTEM'' and tbsp.contents = ''PERMANENT'' and tbsp.extent_management = ''DICTIONARY'' and (tbsp.increment_by != 0 OR (tbsp.initial_ext_size > 0 AND MOD(tbsp.next_extent, tbsp.initial_ext_size) != 0)) and tbsp.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO'')))', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'Tablespaces using a non-uniform default extent size exist. ' || 'Extents in a tablespace should be the same size. This ensures ' || 'that any free extent in the tablespace can always be used for any ' || 'segment in the tablespace.', 'To ensure uniform extent sizes set each tablespace''s default storage ' || 'clause such that the NEXT should equal to or be multiple of the INITIAL value and the ' || 'PCTINCREASE value is set to zero. Then never explicitly specify a ' || 'storage clause at the segment level. Instead, let the storage values ' || 'for the segments be inherited from the default storage clause of the ' || 'tablespace.', l_colList, null, 'NON_UNIFORM_TBSP'); COMMIT; -- Unlimited autoextension -- NOTE: SHOULD BE EXCLUDING TABLESPACES THAT ARE READONLY l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('TBSP_NAME', 'Y', 'Y', 'database/storage/tablespace?oname=^TBSP_NAME^&otype=TABLESPACE&event=edit&cancelURL=^CANCEL_URL^&target=^TARGET_NAME^&type=^TARGET_TYPE^', 'Tablespace Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('FILE_NAME', 'Y', 'Y', 'database/storage/datafile?oname=^FILE_NAME^&otype=DATAFILE&event=edit&cancelURL=^CANCEL_URL^&target=^TARGET_NAME^&type=^TARGET_TYPE^', 'File Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Use of Unlimited Autoextension', '1.0', 'Checks for tablespaces with at least one datafile whose size is unlimited', 'QUERY', 'select d.tablespace_name as TBSP_NAME, d.file_name as FILE_NAME, s.target_guid as TARGET_GUID from mgmt_db_datafiles_ecm d, mgmt_ecm_gen_snapshot s where d.autoextensible = ''YES'' and d.max_file_size = 0 and d.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO'')))', 'rownum > 0', MGMT_ECM_POLICY.CONS_MEDIUM, null, -- Impact of Problem -- Use of Unlimited Autoextension 'Specify a maximum file size for the autoextensible datafile.', l_colList, null, 'AUTOEXTEND_DATAFILE'); COMMIT; -- Undo Space Management -- NOTE: DO WE REALLY NEED TO UPPER THE P.VALUE? IF SO, DO WE NEED TO DO -- THIS WHEN TESTING ALL PARAMETER VALUES; FOR EXAMPLE, IN THE -- CHECK FOR CONTROLFILE AUTOBACKUP USAGE? l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('PARAM_NAME', 'Y', 'Y', 'database/instance/initParameters?event=findParameter&target=^TARGET_NAME^&type=^TARGET_TYPE^&initParamName=^PARAM_NAME^', 'Parameter Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PARAM_VALUE', 'N', 'Y', NULL, 'Parameter Value', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Not Using Undo Space Management', '1.0', 'Checks for undo space management not being used', 'QUERY', 'select ' || 'p.name as PARAM_NAME, ' || 'nvl(p.value, ''null'') as PARAM_VALUE, ' || 's.target_guid as TARGET_GUID ' || 'from mgmt_db_init_params_ecm p, ' || 'mgmt_ecm_gen_snapshot s ' || 'where p.name = ''undo_management'' and ' || 'upper(nvl(p.value, ''MANUAL'')) = ''MANUAL'' and ' || 'p.ecm_snapshot_id = s.snapshot_guid and ' || 's.is_current = ''Y''', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, null, -- Impact of Problem -- Not Using Undo Space Management 'Use automatic undo space management instead of manual undo or ' || 'rollback segments.', l_colList, null, 'NO_UNDO_TBSP'); COMMIT; -- Check 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 l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('MAJOR_VERSION', 'N', 'N', NULL, 'Major version', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('HOME_LOCATION', 'N', 'N', NULL, 'Home Location', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('HOST_NAME', 'N', 'N', NULL, 'Host Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('ORACLE_HOME', 'Y', 'Y', 'ecm/track/view/oracleHome$target=^HOST_NAME^$type=host$location=^HOME_LOCATION^?event=doLoad', 'Oracle Home', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Installation', 'Installation of JAccelerator (NCOMP)', '1.0', 'Checks for installation of JAccelerator (NCOMP) that ' || 'improves Java Virtual Machine performance by running natively compiled (NCOMP) classes', 'QUERY', 'SELECT t.target_guid, h.container_location || '' ('' || h.container_name || '')'' AS ORACLE_HOME, TO_NUMBER(SUBSTR( rc.version,0, INSTR(rc.version, ''.'')-1)) as MAJOR_VERSION, h.container_location AS HOME_LOCATION, t.host_name AS HOST_NAME FROM mgmt_targets t, mgmt_target_properties p, mgmt_ecm_snapshot s, mgmt_inv_container h, mgmt_target_type_component_map map, mgmt_inv_component rc WHERE t.target_guid = p.target_guid AND p.property_name = ''OracleHome'' AND p.property_value=h.container_location AND s.target_name = t.host_name AND s.target_type = ''host'' AND s.is_current = ''Y'' AND s.snapshot_type = ''host_configuration'' AND h.snapshot_guid = s.snapshot_guid AND map.target_type = t.target_type AND rc.name = map.component_name AND rc.container_guid = h.container_guid AND NOT EXISTS ( select * from mgmt_inv_component c where h.container_guid = c.container_guid AND c.name = ''oracle.java.javavm.ncomp'' AND h.container_location = p.property_value )', 'major_version >= 10', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'Java VM performance will be sub-optimal', -- Impact of Problem -- JAccelerator is not installed 'Oracle recommends installing JAccelerator(NCOMP) which typically contains Natively compiled (NCOMP) classes for improved Java Virtual Machine performance. Please refer to the Post-installation Tasks section in the Database Install Guide for instructions on how to install JAccelerator.', l_colList, null, 'INSTALL_NCOMPS'); COMMIT; -- Force Logging l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('FORCE_LOGGING', 'Y', 'Y', NULL, 'Force Logging', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('DB_ROLE', 'N', 'Y', NULL, 'Database Role', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Configuration', 'Force Logging Disabled', '1.0', 'When Data Guard Broker is being used, checks primary databases for ' || 'disabled force logging', 'QUERY', 'select ha.force_logging as FORCE_LOGGING, ha.database_role as DB_ROLE, s.target_guid as TARGET_GUID from mgmt_ha_init_params_ecm p, mgmt_ha_info_ecm ha, mgmt_ecm_gen_snapshot s where p.ecm_snapshot_id = ha.ecm_snapshot_id and p.name = ''dg_broker_start'' and p.value = ''TRUE'' and ha.database_role = ''PRIMARY'' and ha.force_logging = ''NO'' and p.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO'')))', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, '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.', 'The primary database should be put in force logging mode using ' || 'ALTER DATABASE FORCE LOGGING.', l_colList, null, 'NO_FORCE_LOGGING'); COMMIT; -- Recovery Area Location l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('PARAM_NAME', 'Y', 'Y', '^RECOVERY_URL^?target=^TARGET_NAME^&type=^TARGET_TYPE^', 'Parameter Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PARAM_VALUE', 'N', 'Y', NULL, 'Parameter Value', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('RECOVERY_URL', 'N', 'N', NULL, 'Recovery Url', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Configuration', 'Recovery Area Location Not Set', '1.0', 'Checks for recovery area not set', 'QUERY', 'select p.name as PARAM_NAME, nvl(p.value, ''null'') as PARAM_VALUE, case when s.target_type = ''oracle_database'' then ''database/instance/recovery'' else ''rac/racRecovery'' end as RECOVERY_URL, s.target_guid as TARGET_GUID from mgmt_ha_init_params_ecm p, mgmt_ecm_gen_snapshot s where p.name=''db_recovery_file_dest'' and p.value is null and p.ecm_snapshot_id = s.snapshot_guid and s.is_current = ''Y'' and ((s.target_type = ''rac_database'') or (s.target_type = ''oracle_database'' and exists ( select * from mgmt_target_properties tp where s.target_guid = tp.target_guid and tp.property_name = ''RACOption'' and tp.property_value = ''NO'')))', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'The recovery area location is not set.', 'It is recommended the recovery area location be set to provide a ' || 'unified storage location for all recovery components. The ' || 'recovery area location can be set by setting: ' || 'DB_RECOVERY_FILE_DEST = .', l_colList, null, 'RECOVERY_AREA_NOT_SET'); COMMIT; -- create 'Best Database Configuration' policy -- we need to evaluate 'installation' category policy rules too as part of this config refresh -- hence add this rule criterion l_ruleCriteria := ECM_POLICY_RULE_CRITERIA( ECM_POLICY_RULE_CRITERION('Oracle', 'oracle_database', 'Configuration', '%'), ECM_POLICY_RULE_CRITERION('Oracle', 'oracle_database', 'Installation', '%')); l_targetCriteria := ECM_POLICY_TARGET_CRITERIA( ECM_POLICY_TARGET_CRITERION('N', 'oracle_database', '%', 'like'), ECM_POLICY_TARGET_CRITERION('N', 'rac_database', '%', 'like')); l_policyId := MGMT_ECM_POLICY.CREATE_POLICY_GROUP( 'Oracle', 'Best Configuration Practices', '1.0', 'Oracle recommended configuration practices must be adhered to', l_ruleCriteria, l_targetCriteria, null, 'BEST_CONFIG_PRACTICES'); COMMIT; INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_dbconfig', 'oracle_database'); COMMIT; INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_racconfig', 'rac_database'); COMMIT; -- ------------------------------------------------------------------------- -- Database Storage Policy Rules and Group -- ------------------------------------------------------------------------- -- Users with a permanent tablespace as their temporary tablespace l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('USER_NAME', 'Y', 'Y', 'database/security/user?oname=^USER_NAME^&event=edit&otype=USER&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', 'User Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('TABLESPACE', 'N', 'Y', null, 'Tablespace', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Users with Permanent Tablespace as Temporary Tablespace', '1.0', 'Checks for users using a permanent tablespace as the temporary tablespace', 'QUERY', 'select u.user_name as USER_NAME, ' || 'u.tablespace as TABLESPACE, ' || 'ss.target_guid as TARGET_GUID ' || 'from mgmt_db_recusersettings_ecm u, mgmt_ecm_gen_snapshot ss ' || 'where u.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' and ' || 'u.problem_code = 1', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'These users use a permanent tablespace as the temporary tablespace. ' || 'Using temporary tablespaces allows space management for sort operations ' || 'to be more efficient. Using a permanent tablespace for these operations ' || 'may result in performance degradation, especially for Real Application Clusters.', 'Change the temporary tablespace for these users to specify a tablespace of type "Temporary."', l_colList, null, 'PERM_AS_TEMP_TBSP'); COMMIT; -- Non-system user uses system tablespace as a default tablespace l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('USER_NAME', 'Y', 'Y', 'database/security/user?oname=^USER_NAME^&event=edit&otype=USER&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', 'User Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('TABLESPACE', 'N', 'Y', null, 'Tablespace', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Users with System Tablespace as Default Tablespace', '1.0', 'Checks for non-system users using SYSTEM or SYSAUX as the default tablespace', 'QUERY', 'select u.user_name as USER_NAME, ' || 'u.tablespace as TABLESPACE, ' || 'ss.target_guid as TARGET_GUID ' || 'from mgmt_db_recusersettings_ecm u, mgmt_ecm_gen_snapshot ss ' || 'where u.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' and ' || 'u.problem_code = 2', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'These users use a system tablespace (SYSTEM or SYSAUX) as the default ' || 'tablespace. This violation will result in non-system data segments ' || 'being added to the system tablespace, making it more difficult to ' || 'manage these data segments and possibly resulting in performance ' || 'degradation in the system tablespace.', 'Change the default tablespace for these users to specify a non-system tablespace.', l_colList, null, 'SYSTEM_AS_DEFAULT_TBSP'); COMMIT; -- System tablespace contains non-system data segments -- OBSOLETE - removed in favor of "Non-System Data Segments -- in System Tablespaces" rule below. -- Message ID 'NON_SYS_DATA_SEGS_IN_SYSTEM' no longer used. -- Tablespace is dictionary-managed l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('TBSP_NAME', 'Y', 'Y', 'database/reorg/reorg?event=launch<ype=makeLocal&lctx=maintPage&target=^TARGET_NAME^&type=^TARGET_TYPE^&cancelURL=^CANCEL_URL^', 'Tablespace Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Dictionary Managed Tablespaces', '1.0', 'Checks for dictionary managed tablespaces (other than SYSTEM and SYSAUX)', 'QUERY', 'select t.tablespace as TBSP_NAME, ' || 'ss.target_guid as TARGET_GUID ' || 'from mgmt_db_rectssettings_ecm t, mgmt_ecm_gen_snapshot ss ' || 'where t.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' and ' || 't.problem_code = 2', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'These tablespaces are dictionary managed. Oracle recommends ' || 'using locally managed tablespaces to enhance performance ' || 'and ease of space management.', 'Redefine these tablespaces to be locally managed.', l_colList, null, 'TBSP_DICTIONARY'); COMMIT; -- Tablespace contains both rollback and data segments l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('TBSP_NAME', 'Y', 'Y', NULL, 'Tablespace Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('NUM_ROLLBACK_SEGS', 'N', 'Y', NULL, 'Rollback Segment Count', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('NUM_DATA_SEGS', 'N', 'Y', NULL, 'Data Segment Count', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Tablespaces Containing Rollback and Data Segments', '1.0', 'Checks for tablespaces containing both rollback (other than SYSTEM) and data segments', 'QUERY', 'select t.tablespace as TBSP_NAME, ' || 't.value1 as NUM_ROLLBACK_SEGS, ' || 't.value2 as NUM_DATA_SEGS, ' || 'ss.target_guid as TARGET_GUID ' || 'from mgmt_db_rectssettings_ecm t, mgmt_ecm_gen_snapshot ss ' || 'where t.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' and ' || 't.problem_code = 3', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'These tablespaces contain 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. Use of a dedicated tablespace for rollback ' || 'segments enhances availability and performance. ' || '(The SYSTEM rollback segment is excluded from this check.)', 'Use Automatic Undo Management (in Oracle 9.0.1 or greater) ' || 'and drop the rollback segments from this tablespace; ' || 'or, create one or more tablespaces dedicated to rollback ' || 'segments and drop the rollback segments from this tablespace; ' || 'or, dedicate this tablespace to rollback segments and move ' || 'the data segments to another tablespace.', l_colList, null, 'TBSP_MIXED_SEGS'); COMMIT; -- Extent growth policy violation - segment level l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('OBJECT', 'Y', 'Y', '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^', 'Object', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_TYPE', 'Y', 'Y', NULL, 'Object Type', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_OWNER', 'N', 'N', NULL, 'Object Owner', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_NAME', 'N', 'N', NULL, 'Object Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PARTITION', 'Y', 'Y', NULL, 'Partition', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('LOB_COL', 'Y', 'Y', NULL, 'LOB Column', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('SEGMENT_TYPE', 'Y', 'Y', NULL, 'Segment Type', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('TBSP_NAME', 'N', 'Y', NULL, 'Tablespace Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PTN_PARAM', 'N', 'N', NULL, 'Partition Parameter', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Segments with Extent Growth Policy Violation', '1.0', 'Checks for segments in dictionary managed tablespaces (other ' || 'than SYSTEM and SYSAUX) having irregular extent sizes and/or ' || 'non-zero Percent Increase settings', 'QUERY', 'select s.obj_owner || ''.'' || s.obj_name as OBJECT, ' || 'DECODE(s.obj_type, 1, ''TABLE'', 2, ''INDEX'', 3, ''CLUSTER'', ' || ' ''UNHANDLED TYPE '' || s.obj_type) as OBJECT_TYPE, ' || 's.obj_owner as OBJECT_OWNER, ' || 's.obj_name as OBJECT_NAME, ' || 's.obj_partition as PARTITION, ' || 's.obj_lob_col as LOB_COL, ' || 'decode( s.segment_type, 1, ''TABLE'', 2, ''INDEX'', ' || '3, ''TABLE PARTITION'', 4, ''INDEX PARTITION'', ' || '5, ''IOT OVERFLOW'', 6, ''LOB INDEX'', 7, ''LOB COLUMN'', ' || '8, ''LOB PARTITION'', 9, ''TABLE SUBPARTITION'', ' || '10, ''INDEX SUBPARTITION'', 11, ''LOB SUBPARTITION'', ' || '12, ''CLUSTER'', 13, ''IOT OVERFLOW PARTITION'', ''UNKNOWN'') ' || 'as SEGMENT_TYPE, ' || 's.tablespace as TBSP_NAME, ' || 'decode(instr(s.obj_partition, '' ''), 1, ''&pname='', ''&pname=''||obj_partition) ' || 'as PTN_PARAM, ' || 'ss.target_guid as TARGET_GUID ' || 'from mgmt_db_recsegmentsettings_ecm s, mgmt_ecm_gen_snapshot ss ' || 'where s.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' and ' || 's.problem_code > 0 and s.problem_code < 4 and ' || -- Exclude clusters because Reorg does not support them 's.obj_type != 3', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'These segments have extents with sizes that are not multiples ' || 'of the initial extent, and/or a non-zero Percent Increase ' || 'setting. This can result in inefficient reuse of space and ' || 'fragmentation problems.', 'Redefine the tablespace(s) containing the segments to be ' || 'locally managed; or, reorganize these segments, specifying ' || 'a Next Extent value that is a multiple of Initial Extent, and ' || 'a Percent Increase value of 0.', l_colList, null, 'SEG_EXT_GROWTH_VIO'); COMMIT; -- Non-sys segments in system tablespace -- Almost identical to extent growth policy rule -- NOTE: None of the messages in this rule are currently in -- the messgae file due to NLS translation deadline. l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('OBJECT', 'Y', 'Y', '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^', 'Object', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_TYPE', 'Y', 'Y', NULL, 'Object Type', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_OWNER', 'N', 'N', NULL, 'Object Owner', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_NAME', 'N', 'N', NULL, 'Object Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PARTITION', 'Y', 'Y', NULL, 'Partition', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('LOB_COL', 'Y', 'Y', NULL, 'LOB Column', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('SEGMENT_TYPE', 'Y', 'Y', NULL, 'Segment Type', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('TBSP_NAME', 'N', 'Y', NULL, 'Tablespace Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('PTN_PARAM', 'N', 'N', NULL, 'Partition Parameter', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Storage', 'Non-System Data Segments in System Tablespaces', '1.0', 'Checks for data segments owned by non-system users located in ' || 'tablespaces SYSTEM and SYSAUX', 'QUERY', 'select s.obj_owner || ''.'' || s.obj_name as OBJECT, ' || 'DECODE(s.obj_type, 1, ''TABLE'', 2, ''INDEX'', 3, ''CLUSTER'', ' || ' ''UNHANDLED TYPE '' || s.obj_type) as OBJECT_TYPE, ' || 's.obj_owner as OBJECT_OWNER, ' || 's.obj_name as OBJECT_NAME, ' || 's.obj_partition as PARTITION, ' || 's.obj_lob_col as LOB_COL, ' || 'decode( s.segment_type, 1, ''TABLE'', 2, ''INDEX'', ' || '3, ''TABLE PARTITION'', 4, ''INDEX PARTITION'', ' || '5, ''IOT OVERFLOW'', 6, ''LOB INDEX'', 7, ''LOB COLUMN'', ' || '8, ''LOB PARTITION'', 9, ''TABLE SUBPARTITION'', ' || '10, ''INDEX SUBPARTITION'', 11, ''LOB SUBPARTITION'', ' || '12, ''CLUSTER'', 13, ''IOT OVERFLOW PARTITION'', ''UNKNOWN'') ' || 'as SEGMENT_TYPE, ' || 's.tablespace as TBSP_NAME, ' || 'decode(instr(s.obj_partition, '' ''), 1, ''&pname='', ''&pname=''||obj_partition) ' || 'as PTN_PARAM, ' || 'ss.target_guid as TARGET_GUID ' || 'from mgmt_db_recsegmentsettings_ecm s, mgmt_ecm_gen_snapshot ss ' || 'where s.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y'' and ' || 's.problem_code = 4 and ' || -- Exclude clusters because Reorg does not support them 's.obj_type != 3', 'rownum > 0', MGMT_ECM_POLICY.CONS_INFORMATIONAL, 'These segments belonging to non-system users are stored in system ' || 'tablespaces SYSTEM or SYSAUX. This violation makes it more ' || 'difficult to manage these data segments and may result in ' || 'performance degradation in the system tablespace. System users ' || 'include users that are part of the DBMS such as SYS and SYSTEM, ' || 'or that are part of Oracle-supplied facilities: for example, ' || 'CTXSYS, SYSMAN, and OLAPSYS.', 'Relocate the non-system segments to a non-system tablespace.', l_colList, null, 'SEG_NONSYS_SEG_IN_SYS_TS'); COMMIT; -- create 'Best Database Storage' policy l_ruleCriteria := ECM_POLICY_RULE_CRITERIA( ECM_POLICY_RULE_CRITERION('Oracle', 'oracle_database', 'Storage', '%')); l_targetCriteria := ECM_POLICY_TARGET_CRITERIA( ECM_POLICY_TARGET_CRITERION('N', 'oracle_database', '%', 'like'), ECM_POLICY_TARGET_CRITERION('N', 'rac_database', '%', 'like')); l_policyId := MGMT_ECM_POLICY.CREATE_POLICY_GROUP( 'Oracle', 'Best Storage Practices', '1.0', 'Oracle recommended storage practices must be adhered to', l_ruleCriteria, l_targetCriteria, null, 'BEST_STORAGE_PRACTICES'); COMMIT; INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_storage', 'oracle_database'); -- We also want to evaluate on dbconfig collection -- since some Storage rules depend on Config tables. INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_dbconfig', 'oracle_database'); COMMIT; INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_storage', 'rac_database'); -- We also want to evaluate on dbconfig collection -- since some Storage rules depend on Config tables. INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_dbconfig', 'rac_database'); COMMIT; -- ------------------------------------------------------------------------- -- Database Objects Policy Rules and Group -- ------------------------------------------------------------------------- -- Invalid objects l_colList := ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN('OBJECT', 'Y', 'Y', 'database/schema/^OBJECT_EDITOR^?sname=^OBJECT_OWNER^&oname=^OBJECT_NAME^&event=edit&otype=^EDITOR_OTYPE^&target=^TARGET_NAME^&type=^TARGET_TYPE^', 'Object', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_TYPE', 'Y', 'Y', NULL, 'Object Type', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_OWNER', 'N', 'N', NULL, 'Object Owner', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_NAME', 'N', 'N', NULL, 'Object Name', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('OBJECT_EDITOR', 'N', 'N', NULL, 'Object Editor', MGMT_ECM_POLICY.CONS_TEXT_FORMAT), ECM_RULE_COLUMN('EDITOR_OTYPE', 'N', 'N', NULL, 'Editor Otype', MGMT_ECM_POLICY.CONS_TEXT_FORMAT)); l_ruleObj := MGMT_ECM_POLICY.CREATE_POLICY_RULE( 'Oracle', 'oracle_database', 'Objects', 'Invalid Objects', '1.0', 'Checks for invalid objects', 'QUERY', 'select io.object_owner || ''.'' || io.object_name AS OBJECT, ' || 'DECODE(io.object_type, 4, ''VIEW'', 5, ''SYNONYM'', 7, ''PROCEDURE'', 8, ''FUNCTION'', ' || '9, ''PACKAGE'', 11, ''PACKAGE BODY'', 12, ''TRIGGER'', 13, ''TYPE'', 14, ''TYPE BODY'', ' || '28, ''JAVA SOURCE'', 29, ''JAVA CLASS'', 42, ''MATERIALIZED VIEW'', ' || ' ''UNHANDLED TYPE '' || io.object_type) as OBJECT_TYPE, ' || 'io.object_owner as OBJECT_OWNER, ' || 'io.object_name as OBJECT_NAME, ' || 'DECODE(io.object_type, 4, ''view'', 5, ''synonym'', 7, ''procedure'', 8, ''function'', ' || '9, ''package'', 11, ''packagebody'', 12, ''trigger'', 13, ''objecttype'', 14, ''objecttype'',' || '28, ''javaSource'', 29, ''javaClass'', 42, ''mView'', ' || ' ''noEditorForType'' || io.object_type) as OBJECT_EDITOR, ' || 'DECODE(io.object_type, 4, ''VIEW'', 5, ''SYNONYM'', 7, ''PROCEDURE'', 8, ''FUNCTION'', ' || '9, ''PACKAGE'', 11, ''PACKAGE_BODY'', 12, ''TRIGGER'', 13, ''TYPE'', 14, ''TYPE BODY'', ' || '28, ''JAVASOURCE'', 29, ''JAVACLASS'', 42, ''MATERIALIZED_VIEW'', ' || ' ''UNHANDLED_TYPE_'' || io.object_type) as EDITOR_OTYPE, ' || 'ss.target_guid as TARGET_GUID ' || 'from mgmt_db_invobjs_ecm io, mgmt_ecm_gen_snapshot ss ' || 'where io.ecm_snapshot_id = ss.snapshot_guid and ' || 'ss.is_current = ''Y''', 'rownum > 0', MGMT_ECM_POLICY.CONS_MEDIUM, 'These objects are invalid, either because of errors in their definitions ' || 'or because objects they depend on were temporarily removed. If an object is ' || 'invalid due to an error, it cannot be used until the error is corrected. If ' || 'an object is invalid because of a temporarily missing dependency, the object ' || 'will be revalidated when it is next used, or when it is compiled.', 'Edit the objects. If an object has an error, correct the error and recompile ' || 'the object, or remove the object if it is no longer needed. If an object is ' || 'invalid due to a dependency, simply recompile it.', l_colList, null, 'INV_OBJECT_VIO'); -- create 'Best Database Objects' policy l_ruleCriteria := ECM_POLICY_RULE_CRITERIA( ECM_POLICY_RULE_CRITERION('Oracle', 'oracle_database', 'Objects', '%')); l_targetCriteria := ECM_POLICY_TARGET_CRITERIA( ECM_POLICY_TARGET_CRITERION('N', 'oracle_database', '%', 'like'), ECM_POLICY_TARGET_CRITERION('N', 'rac_database', '%', 'like')); l_policyId := MGMT_ECM_POLICY.CREATE_POLICY_GROUP( 'Oracle', 'Best Object Practices', '1.0', 'Oracle recommended object practices must be adhered to', l_ruleCriteria, l_targetCriteria, null, 'BEST_OBJECT_PRACTICES'); COMMIT; INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_object', 'oracle_database'); COMMIT; INSERT INTO MGMT_POLICY_SNAPSHOT_CRITERIA ( POLICY_ID, SNAPSHOT_TYPE, TARGET_TYPE ) VALUES ( l_policyId, 'oracle_object', 'rac_database'); COMMIT; END; END; / SET DEFINE ON