Rem
Rem $Header: mgw/src/plsql/admin/mgwe102.sql /mgw_main/7 2008/08/24 09:37:36 chall Exp $
Rem
Rem mgwe102.sql
Rem
Rem Copyright (c) 2005, 2008, Oracle. All rights reserved.
Rem
Rem  NAME
Rem    mgwe102.sql - downgrade MGW component to 10.2.0 (from 11.1.0)
Rem
Rem  DESCRIPTION
Rem    Reverts data dictionary for MGW component to the 10.2.0 release.
Rem    This script is run in the context of the "new" (11.1.0) database.
Rem
Rem  NOTES
Rem    This script must be run AS SYSDBA.
Rem
Rem  MODIFIED   (MM/DD/YY)
Rem  chall       08/24/08 - 11.2 upgrade/downgrade support
Rem  chall       11/20/06 - undo usage of dbms_crypto and RAW fields
Rem  chall       10/11/06 - set OCI connection type as default for downgrade
Rem  chall       09/29/06 - drop mgw_agent_options view, table
Rem  chall       06/08/06 - downgrade actions for Scheduler feature 
Rem  chall       04/18/06 - comments field added to subscribers table 
Rem  chall       12/13/05 - Downgrade script to 10.2.0 
Rem  chall       12/13/05 - Created
Rem
Rem ==========================================================================

Rem For NNN+2 to NNN (11.2 to 10.2) downgrade, first perform the
Rem NNN+2 to NNN+1 (11.2 to 11.1) downgrade actions
@@mgwe111

Rem Now, perform the 11.1.0 to 10.2.0 downgrade actions
execute dbms_registry.downgrading('MGW');

rem ========================================================================
rem Clean schedule and subscriber entries that may have been affected by
rem the propagation job APIs added for 11gR1 (create_job, remove_job, etc).
rem
rem a) Drop all new-style propagation jobs (those created via create_job).
rem b) Clear certain job-related flags of schedule and subscriber entries.
rem
rem Tables affected:
rem mgw$_schedules, mgw$_subscribers
rem ========================================================================

-- delete all entries flagged as PROPJOB from schedules table
delete from sys.mgw$_schedules sch where (bitand(sch.flags, 2) = 2);
commit;

-- delete all entries flagged as PROPJOB from subscribers table
delete from sys.mgw$_subscribers sub where (bitand(sub.flags, 2) = 2);
commit;

-- clear DELETE_PENDING, PROPJOB, REMOVE_JOB bits of schedule flags
-- note: entries flagged as PROPJOB should have been deleted above
update sys.mgw$_schedules sch
  set flags = sch.flags - bitand(sch.flags,1+2+4)
  where (bitand(sch.flags, 1+2+4) != 0);
commit;

-- clear PROPJOB, REMOVE_JOB bits of subscriber flags
-- note: entries flagged as PROPJOB should have been deleted above
update sys.mgw$_subscribers sub
  set flags = sub.flags - bitand(sub.flags,2+4)
  where (bitand(sub.flags, 2+4) != 0);
commit;

rem ========================================================================
rem Drop MGW configuration for any named agents.
rem
rem Remove all MGW configuration associated with a named agent. This includes
rem any MGW configuration associated with links associated with a named agent.
rem
rem Drop other database objects associated with named agent; e.g., Scheduler
rem job class, Scheduler job, AQ notify queues, AQ log queues, and so on.
rem
rem No action needed for mgw$_agent_options table since the entire table
rem will be dropped later in the script. Also, the foreign key constraint 
rem with ON DELETE CASCADE clause results in Oracle automatically removing
rem the associated rows when an agent is dropped from mgw$_gateway.
rem
rem When finished, MGW$_GATEWAY table should contain only one row for the 
rem default agent.
rem
rem Also, drop any other Scheduler objects created by MGW. Includes the
rem Scheduler job class and job used for default agent. Drop Scheduler 
rem program used for the startup job procedure.
rem
rem Tables affected:
rem mgw$_gateway, mgw$_foreign_queues, mgw$_subscribers, mgw$_schedules
rem ========================================================================

-- Drop a Scheduler object
--
-- p_which : 1=job, 2=job class, 3=program
-- p_name  : name of object to be dropped
-- p_force : TRUE for a forced drop, FALSE for a non-forced drop
--
create or replace procedure mgwmig_drop_scheduler_obj ( 
  p_which in pls_integer, 
  p_name  in varchar2,
  p_force in boolean default TRUE )
is
begin

  begin
    if p_which = 1 then           -- ORA-27475 if no such job
      dbms_scheduler.drop_job(p_name, p_force);
    elsif p_which = 2 then        -- ORA-27476 if no such job class
      dbms_scheduler.drop_job_class(p_name, p_force);
    elsif p_which = 3 then        --  ORA-27476 if no such program
      dbms_scheduler.drop_program(p_name, p_force);
    else
      dbms_output.put_line(
          'mgwmig_drop_scheduler_obj: unknown value for p_which; '||p_which);
    end if;
  exception
    when others then
      if SQLCODE = -27475 then null;
      elsif SQLCODE = -27476 then null;
      else
        dbms_output.put_line(
          'mgwmig_drop_scheduler_obj: name='||p_name||', which='||p_which);
        dbms_output.put_line(SQLERRM);
      end if;
  end;

end mgwmig_drop_scheduler_obj;
/

-- Determine MGW link referenced by a propagation triplet.
--
create or replace function mgwmig_link_refby (
  p_prop_type   in pls_integer,
  p_source      in varchar2,
  p_destination in varchar2 )
return varchar2
is
  v_link varchar2(30) := null;

  -- parses string for the link portion
  function i_getlink( str in varchar2 )
  return varchar2
  is
    v_atpos pls_integer;
  begin
    v_atpos := instrb(str, '@');
    if v_atpos > 0 then 
      return substrb(str, v_atPos+1);
    else --%return ' ';
      return null;
    end if;
  end i_getlink;

begin

  if p_prop_type = 1 then       -- outbound propagation
    v_link := i_getlink(p_destination);
  elsif p_prop_type = 2 then    -- inbound propagation
    v_link := i_getlink(p_source);
  end if;

  return v_link;

end mgwmig_link_refby;
/

-- Clean MGW configuration of all entites referencing given link name.
-- This commits the work after all entities for the link are removed.
--
-- For simplicity this attempts to remove an entry from MQSeries, TIB/Rv, 
-- and MSMQ link table, where in fact, only one of them should have the link.
--
create or replace procedure mgwmig_clean_link_config ( p_linkname IN varchar2 )
is
  v_linkname varchar2(30) := NLS_UPPER(p_linkname);
begin

  begin
    -- remove all references from schedules table
    delete from sys.mgw$_schedules sch
      where (mgwmig_link_refby(sch.prop_type,sch.source,sch.destination) =
             v_linkname);

    -- remove all references from subscribers table
    delete from sys.mgw$_subscribers sub
      where (mgwmig_link_refby(sub.prop_type,sub.queue_name,sub.destination) =
             v_linkname);

    -- remove all references from foreign queues table
    delete from sys.mgw$_foreign_queues fq
      where (fq.link_name = v_linkname);

    -- remove all references from MQSeries link properties table
    delete from sys.mgw$_mqseries_links lk
      where (lk.link_name = v_linkname);

    -- remove all references from TIB/Rv link properties table
    delete from sys.mgw$_tibrv_links lk
      where (lk.link_name = v_linkname);

    -- remove all references from MSMQ link properties table
    delete from sys.mgw$_msmq_links lk
      where (lk.link_name = v_linkname);

    -- remove link reference from mgw$_links
    delete from sys.mgw$_links lk
      where (lk.link_name = v_linkname);

    commit;
  exception 
    when others then
      rollback;
      raise;
  end;

end mgwmig_clean_link_config;
/

-- Stop and drop an AQ queue.
--
-- If p_ignore_error is FALSE, this catches and discards only the exception
-- raised if the queue does not exist. Any other exception is not handled.
-- If TRUE, this ignores any exception and always attempts the operations.
--
create or replace procedure mgwmig_drop_queue (
  p_queue in varchar2,
  p_ignore_error in boolean default false )
is

  -- no such AQ queue exists
  e_NO_QUEUE_EXISTS  exception;
  pragma EXCEPTION_INIT(e_NO_QUEUE_EXISTS, -24010);

  v_do_drop boolean := true;          -- T=attempt to drop queue

begin

  --%dbms_output.put_line('mgwmig_drop_queue: '||p_queue);

  if not p_ignore_error then
    -- only ignore if queue not exist, caller sees any other error
    begin
      dbms_aqadm.stop_queue(queue_name=>p_queue, wait=>true);
      dbms_aqadm.drop_queue(queue_name=>p_queue, auto_commit=>true);
    exception
      when e_NO_QUEUE_EXISTS THEN
        null;
        --%dbms_output.put_line(
        --%    'mgwmig_drop_queue: queue does not exist: '||p_queue);
    end;
  else
    -- ignore all errors
    v_do_drop := TRUE;
    begin
      dbms_aqadm.stop_queue(queue_name=>p_queue, wait=>true);
    exception 
      when e_NO_QUEUE_EXISTS THEN
        null;
        v_do_drop := FALSE;
        --%dbms_output.put_line(
        --%    'mgwmig_drop_queue: queue does not exist: '||p_queue);
      when others then 
        null;
        --%dbms_output.put_line(
        --%    'mgwmig_drop_queue: error stopping queue: '||p_queue);
        --%dbms_output.put_line(SQLERRM);
    end;

    begin
      if v_do_drop then
        dbms_aqadm.drop_queue(queue_name=>p_queue, auto_commit=>true);
      end if;
    exception 
      when others then 
        null;
        --%dbms_output.put_line(
        --%    'mgwmig_drop_queue: error dropping queue:'||p_queue);
        --%dbms_output.put_line(SQLERRM);
    end;
  end if;

end mgwmig_drop_queue;
/

-- Drop AQ notification queues and propagation log queues used by a MGW
-- named agent. This does not drop the underlying queue table.
--
-- If p_ignore_drop_error is FALSE, only the excecption raised if the 
-- queue does not exist is ignored and any other exception is not caught. 
-- If TRUE, any exception raised when attempting to drop a queue is 
-- caught and ignored.
--
create or replace procedure mgwmig_drop_agent_queues (
  p_agent in varchar2,
  p_ignore_drop_error in boolean default false )
is
  v_queue varchar2(61);
begin

  -- command notification queue
  v_queue := 'SYS.MGW_NFY_' || p_agent;
  mgwmig_drop_queue(v_queue, p_ignore_error=>p_ignore_drop_error);

  -- config notification queue
  v_queue := 'SYS.MGW_CFG_' || p_agent;
  mgwmig_drop_queue(v_queue, p_ignore_error=>p_ignore_drop_error);

  -- notification response queue
  v_queue := 'SYS.MGW_RSP_' || p_agent;
  mgwmig_drop_queue(v_queue, p_ignore_error=>p_ignore_drop_error);

  -- native inbound log queue
  v_queue := 'SYS.MGW_ILOG_' || p_agent;
  mgwmig_drop_queue(v_queue, p_ignore_error=>p_ignore_drop_error);

  -- native outbound log queue
  v_queue := 'SYS.MGW_OLOG_' || p_agent;
  mgwmig_drop_queue(v_queue, p_ignore_error=>p_ignore_drop_error);

  -- JMS inbound log queue
  v_queue := 'SYS.MGW_JILOG_' || p_agent;
  mgwmig_drop_queue(v_queue, p_ignore_error=>p_ignore_drop_error);

  -- JMS outbound log queue
  v_queue := 'SYS.MGW_JOLOG_' || p_agent;
  mgwmig_drop_queue(v_queue, p_ignore_error=>p_ignore_drop_error);

end mgwmig_drop_agent_queues;
/

-- Drop extraneous database objects associated with a MGW named agent.
--
create or replace procedure mgwmig_drop_agent_objects ( p_agent IN varchar2 )
is
  v_agent varchar2(30) := NLS_UPPER(p_agent);
begin

  -- drop AQ queues (notify, log, etc) associated with agent; ignore errors
  mgwmig_drop_agent_queues(v_agent, TRUE);

  -- drop agent's Scheduler job and job class
  mgwmig_drop_scheduler_obj(1, 'SYS.MGW_JOB_'||v_agent);    -- job
  mgwmig_drop_scheduler_obj(2, 'SYS.MGW_JOBCLS_'||v_agent); -- job class

end mgwmig_drop_agent_objects;
/

-- Remove all MGW configuration associated with a named agent.
--
create or replace procedure mgwmig_clean_agent_config ( p_agent varchar2 )
is

  v_linkname VARCHAR2(30);

  -- query for all links that reference a given named agent
  CURSOR find_agent_links_c(p_agent varchar2) IS 
      select lk.link_name, lk.agent_name from sys.mgw$_links lk 
        where lk.agent_name = p_agent;

begin

  FOR lk_rec IN find_agent_links_c(p_agent) LOOP
    BEGIN
      v_linkname := lk_rec.link_name;
      --%dbms_output.put_line('mgwmig_clean_agent_config: '||
      --%  'clean link config; link='||v_linkname||
      --%  ', agent='||lk_rec.agent_name);
      mgwmig_clean_link_config(v_linkname);
    EXCEPTION
      WHEN OTHERS THEN
        --%dbms_output.put_line(
        --%    'mgwmig_clean_agent_config: error cleaning link '||v_linkname);
        dbms_output.put_line(SQLERRM);
        NULL;
     END;
  END LOOP;

END mgwmig_clean_agent_config;
/

-- Remove MGW configuration and database objects associated with named agents.
--
DECLARE

  -- query for all MGW named agents
  CURSOR find_named_agents_c IS 
      select gw.agent_name from sys.mgw$_gateway gw  
        where NVL(gw.agent_name, 'DEFAULT_AGENT') != 'DEFAULT_AGENT';

  v_agent VARCHAR2(30);

BEGIN

  FOR na_rec IN find_named_agents_c LOOP

   v_agent := na_rec.agent_name; 

   BEGIN
      --%dbms_output.put_line('removing config for MGW agent: '||v_agent);
      mgwmig_clean_agent_config(v_agent);

      --%dbms_output.put_line('dropping database objects for MGW agent: '||
      --%                     v_agent);
      mgwmig_drop_agent_objects(v_agent);

      --commit;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
        --%dbms_output.put_line('error while cleaning MGW agent: '||v_agent);
        --%dbms_output.put_line(SQLERRM);
     END;

    -- remove agent row in mgw$_gateway
    delete from sys.mgw$_gateway where agent_name = v_agent;
    commit;

  END LOOP;

END;
/

-- Drop Scheduler job and job class used for default agent.
-- Drop Scheduler program used for MGW startup job procedure.
--
BEGIN
  mgwmig_drop_scheduler_obj(1, 'SYS.MGW_JOB_DEFAULT_AGENT');    -- job
  mgwmig_drop_scheduler_obj(2, 'SYS.MGW_JOBCLS_DEFAULT_AGENT'); -- job class
  mgwmig_drop_scheduler_obj(3, 'SYS.MGW_STARTUP_PROGRAM');      -- program
END;
/

-- there should not be any links associated with a named agent
--%select link_name, agent_name from sys.mgw$_links
--%  where NVL(agent_name, 'DEFAULT_AGENT') != 'DEFAULT_AGENT';

drop procedure mgwmig_clean_agent_config;
drop procedure mgwmig_drop_agent_objects;
drop procedure mgwmig_drop_agent_queues;
drop procedure mgwmig_drop_queue;
drop procedure mgwmig_clean_link_config;
drop function mgwmig_link_refby;
drop procedure mgwmig_drop_scheduler_obj;

rem ========================================================================
rem Restore encryption to that used for MGW 9i and 10g releases.
rem
rem mgwicry.plb, mgwicryb.plb specify a MGW encryption support package.
rem It is large of 11g but has minimal dependencies and can be loaded during
rem an downgrade as long as the underlaying MGW tables have 11g format from
rem a 11g upgrade or MGW 11g component install. That should be the case if
rem this script is being called so package should load fine.
rem
rem Tables affected:
rem mgw$_gateway, mgw$_mqseries_links
rem ========================================================================

-- load MGW encryption support package
-- unloaded later on so may not exist if script called multiple times
@@mgwicry.plb
@@mgwicryb.plb

--%exec dbms_output.put_line('------ mgw$_gateway before -----')
--%select agent_name, agent_password, enc_agent_password from sys.mgw$_gateway;

--%exec dbms_output.put_line('------ mgw$_mqseries_links before -----')
--%select link_name, password, enc_password from sys.mgw$_mqseries_links;

-- restore MGW encrypted data to that used prior to 11g
begin

  -- agent password
  update sys.mgw$_gateway 
    set agent_password = 
            sys.mgwi_crypto.migenc_11g_to_10g(enc_agent_password),
        enc_agent_password = NULL
    where enc_agent_password IS NOT NULL and agent_password IS NULL;

  -- MQSeries password
  update sys.mgw$_mqseries_links
    set password = sys.mgwi_crypto.migenc_11g_to_10g(enc_password),
        enc_password = NULL
    where enc_password IS NOT NULL;

  -- pre-11g implementation wound up storing an encypted NULL value for
  -- link password. We should be okay if we skipped this part as well.
  update sys.mgw$_mqseries_links
    set password = sys.mgwi_crypto.encrypt_str_10g(NULL)
    where password IS NULL;

  -- skip sys.mgw$_msmq_links since not implemented for 10g and thus no data

  commit;

exception 
  when others then
    rollback;
    raise;
end;
/

--%exec dbms_output.put_line('------ mgw$_gateway after -----')
--%select agent_name, agent_password, enc_agent_password from sys.mgw$_gateway;

--%exec dbms_output.put_line('------ mgw$_mqseries_links after -----')
--%select link_name, password, enc_password from sys.mgw$_mqseries_links;

-- package dropped later on in script
--%drop package sys.mgwi_crypto;

rem ========================================================================
rem Reverse Table Modifications
rem ========================================================================

rem --- Drop constraints added to MGW$_GATEWAY.
rem
ALTER TABLE mgw$_gateway
  DROP CONSTRAINT mgw$_gateway_pk CASCADE;

ALTER TABLE mgw$_gateway
  DROP CONSTRAINT mgw$_gateway_u1 CASCADE;

rem --- Set fields added for 11.1.0 to default values.
rem

update sys.mgw$_schedules
  set link_name = null;
commit;

update sys.mgw$_subscribers 
  set link_name = null, comments = null;
commit;

update sys.mgw$_links
  set agent_name = null;
commit;

update sys.mgw$_gateway
  set job_instance_id=null, agent_name=null, service=null, 
      initfile=null, comments=null;
commit;

-- clear JDBC_DRIVER bit of agent flags to default to OCI
-- note: only default agent should remain since named agents deleted above
update sys.mgw$_gateway gw
  set flags = gw.flags - bitand(gw.flags,1)
  where (bitand(gw.flags, 1) != 0);
commit;

rem ========================================================================
rem Revoke various grants given.
rem ========================================================================


rem ========================================================================
rem Restore types to previous state
rem
rem CAUTION: Once a type has evolved by adding a new attribute, widening an
rem attribute, and so on, a CREATE OR REPLACE TYPE or ALTER TYPE REPLACE
rem _cannot_ be used to replace the type. An attempt to do so fails with:
rem   ORA-22308: operation not allowed on an evolved type
rem The error is not displayed if database is opened as STARTUP MIGRATE and
rem it _appears_ the type is successfully replaced, but in fact, it remains
rem unchanged. Just adding/dropping type methods does not evolve the type.
rem
rem From 10.2 onward, MGW should _always_ use ALTER TYPE for any type 
rem modifications and never replace the entire type. See additional notes
rem in mgwu101.sql script.
rem ========================================================================

-- mgwi_msglink
--
alter type sys.mgwi_msglink
  DROP attribute m_agent
cascade;

rem ========================================================================
rem Drop types, packages, views, tables
rem ========================================================================

-- drop new views

drop view sys.mgw_agent_options;
drop public synonym mgw_agent_options;

drop view sys.mgw_jobs;
drop public synonym mgw_jobs;

-- drop new types

-- drop new packages

drop package sys.mgwi_crypto;

-- drop new tables

drop table sys.mgw$_config;
drop table sys.mgw$_agent_options;

rem ========================================================================
rem Downgrade complete
rem ========================================================================

execute dbms_registry.downgraded('MGW', '10.2.0');

Rem === End file: mgwe102.sql ================================================