Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.3/policy/upgrade_delete_policies.sql /st_emdbsa_11.2/1 2011/05/20 10:15:53 mpawelko Exp $
Rem
Rem upgrade_delete_policies.sql
Rem
Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      upgrade_delete_policies.sql - Upgrade delete policies SQL script
Rem
Rem    DESCRIPTION
Rem      During 11.2.0.0...11.2.0.3 upgrade, delete affected policies
Rem      before recreating them
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    mpawelko    05/17/11 - add SET DEFINE OFF/ON
Rem    mpawelko    04/27/11 - 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.

SET DEFINE OFF
-- --------------------------------------------------------
-- --------------------------------------------------------
--                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;

  COMMIT;  

END;
/

SET DEFINE ON