Rem Rem $Header: mgwe920.sql 04-oct-2004.11:13:33 chall Exp $ Rem Rem mgwe920.sql Rem Rem Copyright (c) 2002, 2004, Oracle. All rights reserved. Rem Rem NAME Rem mgwe920.sql - downgrade MGW component from 10.1.0 to 9.2.0 Rem Rem DESCRIPTION Rem Reverts data dictionary back from MGW/RDBMS 10gR1 release to 9.2.0. Rem This script is called from $ORACLE_HOME/rdbms/admin/f0902000.sql Rem and is run in context of the "new" (10.1.0) database. Rem Rem NOTES Rem This script must be run AS SYSDBA. Rem Rem MODIFIED (MM/DD/YY) Rem chall 10/04/04 - comment change Rem chall 09/28/04 - comment change Rem chall 06/01/04 - workaround REPLACE TYPE issue for evolved type Rem (10.2 to 9.2 downgrade) Rem chall 05/14/04 - add 10gR2 downgrade hook Rem chall 07/29/03 - no STARTUP trigger for 10i (bug 2999689) Rem chall 06/04/03 - remove 10i notify queues (bug 2984956) Rem chall 02/26/03 - drop mgw$_tibrv_links and other minor changes Rem chall 12/03/02 - drop database STARTUP and SHUTDOWN triggers Rem chall 11/12/02 - comment out some debug output Rem chall 11/05/02 - chall_bug-2606429 Rem chall 10/31/02 - Created Rem Rem ========================================================================== Rem For 10.2.0 to 9.2.0 downgrade, first perform the 10.2.0 to 10.1.0 Rem downgrade actions @@mgwe101 Rem Now, perform the 10.1.0 to 9.2.0 downgrade actions execute dbms_registry.downgrading('MGW'); rem ======================================================================== rem Drop MGW configuration for TIB/RV and JMS propagation rem rem Remove all MGW schedules, subscribers, foreign queues and links: rem 1) Referenced TIB/RV links. rem 2) Links were used for JMS propagation. rem For RDBM 10iR1, only MQSeries links supported JMS propagation. rem rem Tables affected: rem mgw$_schedules, mgw$_subscribers, mgw$_foreign_queues, mgw$_tibrv_links, rem mgw$_mqseries_links, mgw$_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 Tib/Rv 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 would likely rem refuse to start due to unknown/invalid config (Tib/Rv, MQSeries JMS 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$_mqseries_links -- and mgw$_tibrv_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 MQSeries link property table delete from sys.mgw$_mqseries_links mql where (mql.link_name = v_linkname); -- remove link reference from TIB/RV link property table delete from sys.mgw$_tibrv_links rvl where (rvl.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 TIB/RV links -- DECLARE v_linkname VARCHAR2(30); --CURSOR find_tib_c IS -- select lk.link_name from sys.mgw$_links lk -- where lk.link_type = 2; -- 2 == TIB/RV link CURSOR find_tib_c IS select lk.link_name from sys.mgw$_tibrv_links lk; BEGIN FOR lk_rec IN find_tib_c LOOP -- iterate all TIB/RV 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 RV link: ' || v_linkname --% || ' : ' || SQLERRM); NULL; END; END LOOP; END; -- Remove all MGW entities used for JMS propagation. -- For 10iR1, only MQSeries links supported JMS propagation. -- -- interface_type : 8=JMS_QUEUE_CONNECTION, 9=JMS_TOPIC_CONNECTION, -- 10=JMS_CONNECTION -- DECLARE v_linkname VARCHAR2(30); cursor find_mqjms_c IS select mql.link_name from sys.mgw$_mqseries_links mql where (mql.interface_type IN (8, 9, 10)); BEGIN FOR lk_rec IN find_mqjms_c LOOP -- iterate all MQSeries 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 MQSeries JMS link: ' || v_linkname --% || ' : ' || SQLERRM); NULL; END; END LOOP; END; END; / drop function mgwi_getlink; rem ======================================================================== rem Drop queues/queue tables used by MGW. rem rem 1) Drop JMS log queues and queue tables used by normal AQ JMS link. rem 2) Drop notification queues added for 10i when pipe notification was rem replaced by queue notification. Do _NOT_ drop the queue table since rem one notify queue still remains and all notify queues used same table. rem ======================================================================== declare PROCEDURE drop_queue ( p_qname IN VARCHAR2, -- must be non-NULL p_qtable IN VARCHAR2 ) -- if non-NULL, queue table is dropped -- if NULL, queue table is not dropped IS BEGIN -- stop and drop the queue BEGIN dbms_aqadm.stop_queue(queue_name =>p_qname, wait=>TRUE); dbms_aqadm.drop_queue(queue_name =>p_qname, auto_commit=>TRUE); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -24010 THEN -- no queue exists null; ELSE null; --%dbms_output.put_line('drop log queue: '||p_qname||' : '||SQLERRM); --%RAISE; END IF; END; -- drop the queue table, if table name was specified IF p_qtable IS NOT NULL THEN BEGIN dbms_aqadm.drop_queue_table(queue_table=>p_qtable, force=>TRUE); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -24002 THEN -- no queue table exists null; ELSE null; --%dbms_output.put_line('drop qtable: '||p_qtable||' : '||SQLERRM); --%RAISE; END IF; END; END IF; END drop_queue; begin -- drop JMS log queues and queue tables drop_queue('SYS.MGW_JMS_SEND_LOG', 'SYS.MGW_JMS_SEND_LOG_QT'); drop_queue('SYS.MGW_JMS_RECV_LOG', 'SYS.MGW_JMS_RECV_LOG_QT'); -- drop notify queues added for 10i, but leave the queue table alone drop_queue('SYS.MGW_CONFIG_QUEUE', NULL); drop_queue('SYS.MGW_RESPONSE_QUEUE', NULL); end; / --%begin --% mgwi_admin.task(mgwi_admin.TSK_DROP_AQ_JMS_LOG); --%end; --%/ rem ======================================================================== rem Drop triggers rem ======================================================================== drop trigger mgw_dbshutdown_trig; rem ======================================================================== rem Reverse Table Modifications rem ======================================================================== -- columns added for 10iR1; set to default values -- update mgw$_gateway set max_threads=1, flags=0, job_flags=0, agent_instance=null, agent_start_time=null, last_instance=null, last_start_time=null, last_agent_exitcode=null; update mgw$_links set flags=0; update mgw$_foreign_queues set flags=0; update mgw$_subscribers set flags=0, options=null; update mgw$_schedules set flags=0; commit; -- mgw$_tibrv_links should have no rows since any RV links would have been -- cleaned/removed by prior step --%delete from mgw$_tibrv_links; --%commit; rem ======================================================================== rem Revoke various grants given. rem ======================================================================== -- note: will drop this table so revoke not needed --%revoke select, update on mgw$_tibrv_links from MGW_AGENT_ROLE; -- revoke privileges needed for OJMS LOB workaround revoke UPDATE ANY TABLE 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 NOTE: For 10.1, new attributes were added to the four MGW internal types, rem mgwi_msglink, mgwi_foreignqueue, mgw_subscriber, and mgwi_schedule and rem a CREATE OR REPLACE used to replace the type rather than using ALTER TYPE. rem Even worse, an attribute was even added in the middle of the existing rem attributes for mgwi_msglink. rem rem For 10.2 (and future releases), ALTER TYPE is used to evolve MGW types. rem To get around the evolve/replace issue for a 10.2 to 9.2 downgrade, a rem couple changes are needed for the mgwu920 and mgwe920 scripts shipped rem with the 10.2 release. First, a DROP TYPE is added before the rem CREATE OR REPLACE TYPE since the REPLACE fails for an evolved type. rem Second, necessary privileges on the types must be re-granted after the rem type is re-created. rem rem From 10.2 onward, MGW should _always_ use ALTER TYPE for any type rem modifications and never replace the entire type. rem ======================================================================== -- mgwi_msglink -- drop type mgwi_msglink; CREATE OR REPLACE TYPE mgwi_msglink AS OBJECT ( m_linkname VARCHAR2(30), m_linktype INTEGER, m_mqseriesProp mgw_mqseries_properties, m_options mgw_properties, m_comment VARCHAR2(2000), m_guid RAW(16), STATIC FUNCTION entry_exists ( p_linkname IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_linkname IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_msglink, STATIC FUNCTION construct ( p_linkname IN VARCHAR2, p_properties IN mgw_mqseries_properties, p_options IN mgw_properties DEFAULT NULL, p_comment IN VARCHAR2 DEFAULT NULL ) RETURN mgwi_msglink, STATIC FUNCTION load_entry ( p_linkname IN VARCHAR2 ) RETURN mgwi_msglink, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_msglink, STATIC FUNCTION load_entry_decrypt_text ( p_linkname IN VARCHAR2, p_key IN RAW ) RETURN mgwi_msglink, MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE updateData, MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE decryptData ( p_keyText IN RAW, p_keyRaw IN RAW ), MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ), MEMBER PROCEDURE loadMqseries, MEMBER PROCEDURE insertMqseries, MEMBER PROCEDURE deleteMqseries, MEMBER PROCEDURE updateMqseries, MEMBER PROCEDURE decryptMqseries ( p_keyText IN VARCHAR2, p_keyRaw IN RAW ) ); / grant execute on mgwi_msglink to MGW_AGENT_ROLE; -- mgwi_foreignqueue -- drop type mgwi_foreignqueue; CREATE OR REPLACE TYPE mgwi_foreignqueue AS OBJECT ( m_name VARCHAR2(30), m_link VARCHAR2(30), m_provider_queue VARCHAR2(128), m_domain INTEGER, m_options mgw_properties, m_comment VARCHAR2(2000), m_guid RAW(16), STATIC FUNCTION entry_exists( p_qname IN VARCHAR2, p_linkname IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_qname IN VARCHAR2, p_linkname IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_foreignqueue, STATIC FUNCTION construct ( p_name IN VARCHAR2, p_link IN VARCHAR2, p_provider_queue IN VARCHAR2 DEFAULT NULL, p_domain IN INTEGER DEFAULT NULL, p_options IN mgw_properties DEFAULT NULL, p_comment IN VARCHAR2 DEFAULT NULL ) RETURN mgwi_foreignqueue, STATIC FUNCTION load_entry ( p_name IN VARCHAR2, p_link IN VARCHAR2 ) RETURN mgwi_foreignqueue, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_foreignqueue, MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ) ); / grant execute on mgwi_foreignqueue to MGW_AGENT_ROLE; -- mgwi_subscriber -- drop type mgwi_subscriber; CREATE OR REPLACE TYPE mgwi_subscriber AS OBJECT ( m_id VARCHAR2(30), m_propType INTEGER, m_source VARCHAR2(61), m_destination VARCHAR2(61), m_rule VARCHAR2(4000), m_transformation VARCHAR2(61), m_exceptionQueue VARCHAR2(61), m_guid RAW(16), m_configState INTEGER, STATIC FUNCTION entry_exists ( p_id IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_id IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_subscriber, STATIC FUNCTION construct ( p_id IN VARCHAR2, p_propType IN INTEGER, p_source IN VARCHAR2, p_destination IN VARCHAR2, p_rule IN VARCHAR2 DEFAULT NULL, p_transformation IN VARCHAR2 DEFAULT NULL, p_exceptionQueue IN VARCHAR2 DEFAULT NULL ) RETURN mgwi_subscriber, STATIC FUNCTION load_entry ( p_id IN VARCHAR2 ) RETURN mgwi_subscriber, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_subscriber, STATIC PROCEDURE delete_guid ( p_guid IN RAW ), MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE updateData, MEMBER PROCEDURE update_configState ( p_state IN BINARY_INTEGER ), MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ), MEMBER FUNCTION isOutbound RETURN INTEGER ); / grant execute on mgwi_subscriber to MGW_AGENT_ROLE; -- mgwi_schedule -- drop type mgwi_schedule; CREATE OR REPLACE TYPE mgwi_schedule AS OBJECT ( m_id VARCHAR2(30), m_propType INTEGER, m_source VARCHAR2(61), m_destination VARCHAR2(61), m_startTime DATE, m_duration NUMBER, m_nextTime VARCHAR2(200), m_latency NUMBER, m_state INTEGER, m_guid RAW(16), STATIC FUNCTION entry_exists ( p_id IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_id IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_schedule, STATIC FUNCTION construct ( p_id IN VARCHAR2, p_propType IN INTEGER, p_source IN VARCHAR2, p_destination IN VARCHAR2, p_startTime IN DATE, p_duration IN NUMBER, p_nextTime IN VARCHAR2, p_latency IN NUMBER, p_state IN INTEGER ) RETURN mgwi_schedule, STATIC FUNCTION load_entry ( p_id IN VARCHAR2 ) RETURN mgwi_schedule, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_schedule, MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE updateData, MEMBER PROCEDURE updateState, MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ) ); / grant execute on mgwi_schedule to MGW_AGENT_ROLE; -- Drop type bodies for any types for which new methods were added. drop type body mgwi_msglink; drop type body mgwi_foreignqueue; drop type body mgwi_subscriber; drop type body mgwi_schedule; rem ======================================================================== rem Drop types, packages, views rem ======================================================================== -- drop new views drop view sys.mgw_tibrv_links; drop public synonym mgw_tibrv_links; -- drop new types drop type sys.mgw_tibrv_properties force; drop type sys.mgw_tibrv_msg_t force; drop type sys.mgw_tibrv_ifields_t force; drop type sys.mgw_tibrv_ifield_t force; drop type sys.mgw_tibrv_field_t force; drop type sys.mgw_number_array_t force; -- drop new tables drop table sys.mgw$_tibrv_links; rem ======================================================================== rem Downgrade complete rem ======================================================================== execute dbms_registry.downgraded('MGW', '9.2.0'); Rem === End file: mgwe920.sql ================================================