Rem
Rem $Header: upgrade_delete_policies.sql 09-sep-2005.15:21:44 rreilly Exp $
Rem
Rem upgrade_delete_policies.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      upgrade_delete_policies.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rreilly     09/09/05 - bug4602294 due to core change can no longer run 
Rem                           beta scripts 
Rem    rreilly     08/16/05 - rreilly_bug_4541936_upgrade
Rem    rreilly     08/16/05 - Created
Rem

SET DEFINE OFF

-- Delete Policies
--
-- NOTE: Metrics will not delete unless all associated policies
--       are first removed.  So, delete policy must run first,
--       then the metrics can be deleted.
--       After all policies and metrics are removed, then 
--       the metrics should be re-created and NEXT the
--       policies should be re-created.
-- 
-- Have to add error handling to PL/SQL because the fix for bug 4602294
-- means we cannot count on the metrics/policies being created, so
-- we have to handle the NO_SUCH_METRIC_FOUND and NO_SUCH_POLICY_FOUND 
-- exceptions.


-- --------------------------------------------------------
-- --------------------------------------------------------
--                CONFIGURATION: Delete Policies
-- --------------------------------------------------------
-- --------------------------------------------------------

BEGIN


  -- Config: Not Using Automatic Undo Management
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_UNDO_TBSP');
  COMMIT;  

  -- Config: Not Using Spfile
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_SPFILE');
  COMMIT;  

  -- Config: Not Using Spfile
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'HIDDEN_PARAMS');
  COMMIT;  

  -- Config: Not Using Automatic Shared Memory Management
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'SGA_TARGET');
  COMMIT;  

  -- Config: STATISTICS_LEVEL Set to All
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'STATISTICS_LEVEL_HIGH');
  COMMIT;  

  -- Config: Disabled Automatic Statistics Collection
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'STATISTICS_LEVEL');
  COMMIT;  

  -- Config: Not Using Automatic PGA Management
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'AUTO_PGA');
  COMMIT;  

  -- Config: Not Using Latest Version of the Optimizer
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'OPTIMIZER_VERSION');
  COMMIT;  
 
  -- Config: Insufficient Number of Redo Logs
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSUFF_REDO_LOGS');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'INSUFF_REDO_LOGS');
  COMMIT;  

  -- Config: Insufficient Number of Control Files
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSUFF_CONTROL_FILES');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'INSUFF_CONTROL_FILES');
  COMMIT;  

  -- Config: Installation of JAccelerator (NCOMP)
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSTALL_NCOMPS');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'INSTALL_NCOMPS');
  COMMIT;  

  -- Config: Recovery Area Location Not Set
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_RECOVERY_AREA');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_RECOVERY_AREA');
  COMMIT;  

  -- Config: Force Logging Disabled
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_FORCE_LOGGING');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_FORCE_LOGGING');
  COMMIT;  

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

END;
/


-- --------------------------------------------------------
-- --------------------------------------------------------
--                STORAGE: Delete Policies
-- --------------------------------------------------------
-- --------------------------------------------------------

BEGIN

  -- Storage: Rollback in SYSTEM Tablespace
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'RBS_IN_SYSTEM');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'RBS_IN_SYSTEM');
  COMMIT;  

  -- Storage: Users with Permanent Tablespace as Temporary Tablespace
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'PERM_AS_TEMP_TBSP');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'PERM_AS_TEMP_TBSP');
  COMMIT;  

  -- Storage: Users with a System Tablespace as Default Tablespace
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP');
  COMMIT;  

  -- Storage: Tablespace Not Using Automatic Segment-Space Management
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'TBSP_SEGSPACE_MGMT');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'TBSP_SEGSPACE_MGMT');
  COMMIT;  

  -- Storage: Dictionary Managed Tablespaces
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'TBSP_DICTIONARY');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'TBSP_DICTIONARY');
  COMMIT;  

  -- Storage: Tablespaces Containing Rollback and Data Segments
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'TBSP_MIXED_SEGS');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'TBSP_MIXED_SEGS');
  COMMIT;  

  -- Storage: Default Temporary Tablespace set to a System Tablespace
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'DEFAULT_TEMP_TBSP');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'DEFAULT_TEMP_TBSP');
  COMMIT;  

  -- Storage: Default Permanent Tablespace set to a System Tablespace
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'DEFAULT_PERMANENT_TBSP');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'DEFAULT_PERMANENT_TBSP');
  COMMIT;  

  -- Storage: Non-Uniform Default Extent Size for Dictionary Managed Tablespaces
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'NON_UNIFORM_TBSP');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'NON_UNIFORM_TBSP');
  COMMIT;  

  -- Storage: Non-System Data Segments in a System Tablespace
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'SEG_NONSYS_SEG_IN_SYS_TS');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'SEG_NONSYS_SEG_IN_SYS_TS');
  COMMIT;  

  -- Storage: Segment in Dictionary Tablespace with Non-Uniform Extent Size
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'SEG_EXT_GROWTH_VIO');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'SEG_EXT_GROWTH_VIO');
  COMMIT;  

  -- Storage: Insufficient Redo Log Size
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'oracle_database',
      p_policy_name => 'SMALL_REDO_LOGS');
  COMMIT;  
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'rac_database',
      p_policy_name => 'SMALL_REDO_LOGS');
  COMMIT;  

  -- ASM Storage: Disk Group Contains Disks of Significantly Different Sizes
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'osm_instance',
      p_policy_name => 'ASM_DISK_SIZE');
  COMMIT;  

  -- ASM Storage: Disk Group Depends on External Redundancy and has Unprotected Disks 
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'osm_instance',
      p_policy_name => 'ASM_DATA_PROTECTION');
  COMMIT;  

  -- ASM Storage: Disk Group Contains Disks with Different Redundancy Attributes
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'osm_instance',
      p_policy_name => 'ASM_MIXED_REDUNDANCY');
  COMMIT;  

  -- ASM Storage: Disk Group with NORMAL or HIGH Redundancy has Mirrored or Parity Protected Disks
  MGMT_POLICY.DELETE_POLICY(
      p_target_type => 'osm_instance',
      p_policy_name => 'ASM_UNNEEDED_REDUNDANCY');
  COMMIT;  


  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

END;
/

SET DEFINE ON