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