Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/policy/upgrade_delete_policies.sql /st_emdbsa_11.2/6 2009/06/15 12:13:34 yozhang Exp $
Rem
Rem
Rem upgrade_delete_policies.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem 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    yozhang     06/10/09 - Fix bug 8477284: AUTO_PGA
Rem    nzhao       02/24/09 - Bug backports between
Rem                           EMDBGC_10.2.0.5.0_GENERIC_081008 and
Rem                           EMDBGC_10.2.0.5.0_GENERIC_090128 to
Rem                           EMDBSA_11.2.0.0.0_LINUX_090127
Rem    yozhang     02/18/09 - Fix bug 7641873: rename policy name for
Rem                           HIDDEN_PARAMS
Rem    mnihalan    09/25/08 - Fix bug 7395749
Rem    yozhang     09/25/08 - Fix bug 6442503
Rem    yozhang     09/24/08 - Fix bug 6510791 in 11.2DBC
Rem    mnihalan    05/16/08 - Delete policies
Rem    mnihalan    05/16/08 - Created
Rem

-- 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

 BEGIN
  -- Config: Insufficient Number of Redo Logs
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'INSUFF_REDO_LOGS');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

 BEGIN

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

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

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

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

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

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'NON_UNIFORM_TBSP');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;
 
 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'rac_database',
      p_policy_name => 'NON_UNIFORM_TBSP');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;
 
 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'NO_UNDO_TBSP');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'HIDDEN_PARAMS');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'HIDDEN_PARAMS2');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'SGA_TARGET');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'OPTIMIZER_VERSION');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'rac_database',
      p_policy_name => 'SYSTEM_AS_DEFAULT_TBSP');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'SEG_NONSYS_SEG_IN_SYS_TS');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'rac_database',
      p_policy_name => 'SEG_NONSYS_SEG_IN_SYS_TS');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'SEG_EXT_GROWTH_VIO');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'rac_database',
      p_policy_name => 'SEG_EXT_GROWTH_VIO');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

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

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_RECOVERY_AREA');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

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

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'rac_database',
      p_policy_name => 'NO_FORCE_LOGGING');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END; 

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'TIMED_STATISTICS');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'TBSP_SEGSPACE_MGMT');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'rac_database',
      p_policy_name => 'TBSP_SEGSPACE_MGMT');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;

 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'AUTO_PGA');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
      p_target_type => 'oracle_database',
      p_policy_name => 'AUTO_PGA2');

  EXCEPTION
   WHEN MGMT_GLOBAL.POLICY_DOES_NOT_EXIST THEN
   NULL;
 END;

  COMMIT;  

END;
/

SET DEFINE ON