Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\mgw\admin\mgwe101.sql

Rem
Rem $Header: mgwe101.sql 30-mar-2006.11:21:14 chall Exp $
Rem
Rem mgwe101.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem  NAME
Rem    mgwe101.sql - downgrade MGW component to 10.1.0
Rem
Rem  DESCRIPTION
Rem    Reverts data dictionary back from MGW/RDBMS n.n.n release to 10.1.0.
Rem    This script is run in the context of the "new" (e.g., 10.2.0) database.
Rem
Rem  NOTES
Rem    This script must be run AS SYSDBA.
Rem
Rem  MODIFIED   (MM/DD/YY)
Rem  chall       03/30/06 - chain to mgwe102.sql for 11.0 to 10.1.0 downgrade 
Rem  chall       09/17/04 - Windows only; restore original file name 
Rem                         (libmgwagent.dll) for MGW agent library object 
Rem  chall       05/19/04 - MSMQ support 
Rem  chall       05/14/04 - chall_mgw_mighook_10r2
Rem  chall       05/14/04 - Created
Rem
Rem ==========================================================================

Rem For NNN+2 to NNN (10.1.0) downgrade, first perform the NNN+2 to NNN+1
Rem (11.1.0 to 10.2.0) downgrade actions
@@mgwe102

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

rem ========================================================================
rem Drop MGW configuration for MSMQ.
rem
rem Remove all MGW schedules, subscribers, foreign queues and links that
rem reference MSMQ links.
rem
rem Tables affected:
rem mgw$_schedules, mgw$_subscribers, mgw$_foreign_queues, mgw$_links,
rem mgw$_msmq_links
rem
rem ----------
rem
rem Note: Alternate to below is to use dbms_mgwadm procedures to remove
rem the schedules, subscribers, foreign queues and links, rather than
rem directly removing the rows.
rem
rem Advantage: It removes the MGW configuration in a "clean" manner.
rem For example, it would remove the AQ subscriber created by MGW admin
rem for outbound propagation to MSMQ messaging system.
rem
rem Disadvantage: It would only work the first time this script is called.
rem On subsequent calls, the dbms_mgwadm package would be INVALID due to
rem other downgrade actions done later on in this script.
rem
rem Disadvantage: If dbms_mgwadm package is invalid _first_ time this is
rem called, nothing would be done for that link and MGW agent may refuse
rem to start due to unknown/invalid config (MSMQ link).
rem
rem For now, remove the rows directly. Downgrades are typically done after
rem a user has upgraded, had problems with the upgrade, and then wanted to
rem downgrade (revert back) to "older" version. In theory, user would not
rem typically downgrade after they started using "new" features. If we do 
rem want to switch to the alternative approach, would need to catch the
rem exception thrown if dbms_mgwadm is INVALID.
rem 
rem ========================================================================

-- Only stored procedure, package procedure can be used in SQL statement.(?)
create or replace function mgwi_getlink(str in varchar2)
return varchar2
is
  v_atpos binary_integer;
begin
  v_atpos := instrb(str, '@');
  if v_atpos > 0 then
    return substrb(str, v_atPos+1);
  else
    --return ' ';
    return null;
  end if;
end mgwi_getlink;
/

DECLARE

-- 
  -- Cleans MGW configuration of all entites referencing given link name.
  -- This commits the work after all entities for this link are removed.
  --
  -- NOTE: Should (attempt to) remove link name from mgw$_msmq_links 
  -- _BEFORE_  removing from mgw$_links, although not really needed due 
  -- to the 'DELETE CASCADE' option on those tables.
  --
  -- prop_type: 1=outbound, 2=inbound
  --
  procedure clean_link_config( p_linkname IN VARCHAR2 )
  is
    v_linkname VARCHAR2(30) := NLS_UPPER(p_linkname);
    --v_linkname VARCHAR2(30) := p_linkname;
  begin

-- remove all references from schedules table
    delete 
      from sys.mgw$_schedules sch
      where (sch.prop_type = 1 AND 
             mgwi_getlink(sch.destination) = v_linkname) OR
            (sch.prop_type = 2 AND 
             mgwi_getlink(sch.source) = v_linkname);

-- remove all references from subscribers table
    delete 
      from sys.mgw$_subscribers sub
      WHERE (sub.prop_type = 1 AND 
             mgwi_getlink(sub.destination) = v_linkname) OR
            (sub.prop_type = 2 AND 
             mgwi_getlink(sub.queue_name) = 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 MSMQ link property table
    delete 
      from sys.mgw$_msmq_links msmql
      where (msmql.link_name = v_linkname);

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

commit;

end clean_link_config;

BEGIN

-- remove all MGW entities for any MSMQ links
  --
  DECLARE
    v_linkname VARCHAR2(30);

--CURSOR find_msmq_c IS 
    --  select lk.link_name from sys.mgw$_links lk
    --  where lk.link_type = 3;       -- 3 == MSMQ link
    CURSOR find_msmq_c IS 
      select lk.link_name from sys.mgw$_msmq_links lk;

BEGIN

FOR lk_rec IN find_msmq_c LOOP         -- iterate all MSMQ links
      BEGIN
        v_linkname := lk_rec.link_name;
        --%dbms_output.put_line('link_name=' || v_linkname);
        clean_link_config(v_linkname);
        --commit;
      EXCEPTION
        WHEN OTHERS THEN
          --%dbms_output.put_line('Clean MSMQ link: ' || v_linkname
          --%                     || ' : ' || SQLERRM);
          NULL;
      END;
    END LOOP;
  END;

END;
/

drop function mgwi_getlink;

rem ========================================================================
rem Restore original library name for MGW agent library (Windows Only)
rem
rem For the 10.2.0 release, the name of the MGW agent library on Windows 
rem was changed from "libmgwagent.dll" to "oramgwagent.dll" be conform to
rem Oracle standards. During a downgrade to 10.1.0 we need to restore the
rem old name ("libmgwagent.dll"). This only applies to Windows and no such
rem change is needed for other platforms.
rem
rem Our downgrade logic attempts to mininimize what it "knows" about the
rem library file spec. It assumes the location of the library is the same
rem for 10.1.0 and 10.2.0, but makes no assumptions as to what the location
rem actually is.
rem
rem Starting with 10.2.0, mgwlib.sql is called from mgwdbmig.sql and 
rem mgwrelod.sql so the database link and library objects are always 
rem reloaded during an upgrade or reload (downgrade). We should not need
rem to do anything special in mgwu101.sql for an upgrade to 10.2.0, nor
rem should we need to do anything at all in the future.
rem
rem ========================================================================

declare
  v_ps  VARCHAR2(100);

procedure migrate_mgwagent_lib( p_oldname varchar2, p_newname varchar2,
                                  p_add_agent boolean default false )
  is
    v_oldspec VARCHAR2(200);
    v_newspec VARCHAR2(200);
    v_sql     VARCHAR2(500);
  begin
    --%dbms_output.put_line('old name: ' || p_oldname);
    --%dbms_output.put_line('new name: ' || p_newname);

-- just replace library name in the old file spec to keep path info same
    select file_spec into v_oldspec from dba_libraries 
        where library_name='DBMS_MGWADM_AGENT_LIB';
    v_newspec := regexp_replace(v_oldspec,p_oldname,p_newname,1,1,'i');

-- statement to replace library object, optionally adding AGENT clause
    v_sql := 'create or replace library dbms_mgwadm_agent_lib as '||
             ''''||v_newspec||'''';
    if (p_add_agent) then
      v_sql := v_sql || ' agent ''MGW_AGENT''';
    end if;

--%dbms_output.put_line('old spec: ' || v_oldspec);
    --%dbms_output.put_line('new spec: ' || v_newspec);
    --%dbms_output.put_line('sql: '||v_sql);
    execute immediate v_sql;

-- paranoia check, make sure file spec contains the new name
    select file_spec into v_newspec from dba_libraries
        where library_name = 'DBMS_MGWADM_AGENT_LIB';
    --%dbms_output.put_line('post create spec:' || v_newspec);
    if (regexp_instr(v_newspec,p_newname,1,1,0,'i') = 0) then
      raise_application_error(-20000, 
        'ERROR: unexpected file spec after MGW agent library replaced: '||
        v_newspec);
    end if;

end migrate_mgwagent_lib;

begin
    v_ps := dbms_utility.port_string;
    --%dbms_output.put_line('port_string: ' || v_ps);
    if (v_ps like '%WIN_NT%') then
      migrate_mgwagent_lib('oramgwagent.dll', 'libmgwagent.dll');
    end if;
end;
/

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

-- mgw$_foreign_queues
-- Revert provider_queue back to old size (128).
alter table sys.mgw$_foreign_queues
  modify (provider_queue varchar2(128));

-- mgw$_msmq_links should have no rows since any MSMQ links would have been
-- cleaned/removed by prior step
--%delete from mgw$_msmq_links;
--%commit;

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

-- note: will drop this table so revoke not needed
--%revoke select, update on mgw$_msmq_links from MGW_AGENT_ROLE;

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_msmqProp
cascade;

alter type sys.mgwi_msglink
  DROP STATIC FUNCTION construct (
        p_linkname   IN VARCHAR2,
        p_properties IN sys.mgw_msmq_properties,
        p_options    IN sys.mgw_properties DEFAULT NULL,
        p_comment    IN VARCHAR2 DEFAULT NULL )
      RETURN mgwi_msglink,
  DROP MEMBER PROCEDURE loadMsmq,
  DROP MEMBER PROCEDURE insertMsmq,
  DROP MEMBER PROCEDURE deleteMsmq,
  DROP MEMBER PROCEDURE updateMsmq,
  DROP MEMBER PROCEDURE decryptMsmq(p_keyText IN VARCHAR2, p_keyRaw IN RAW)
cascade;

-- mgwi_foreignqueue
-- Only widening of attribute size allowed.

-- mgw_property
-- Only widening of attribute size allowed.

-- mgw_properties
-- Limit of varray can only be increased.

-- Drop type bodies for any types for which new methods were added.
-- Q: Is this needed? Okay to drop since mgwrelod should reload body?
drop type body mgwi_msglink;

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

-- drop new views
drop view sys.mgw_msmq_links;
drop public synonym mgw_msmq_links;

-- drop new types
drop type sys.mgw_msmq_properties force;

-- drop new tables
drop table sys.mgw$_msmq_links;

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

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

Rem === End file: mgwe101.sql ================================================

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de