Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\xdbdbmig.sql
Rem Rem $Header: rdbms/admin/xdbdbmig.sql /st_rdbms_11.2.0.1.0_gen/1 2010/01/13 05:17:43 sinaraya Exp $ Rem Rem xdbdbmig.sql Rem Rem Copyright (c) 2002, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem xdbdbmig.sql - Xml DB DataBase MIGrate Rem Rem DESCRIPTION Rem Upgrade script for XDB from all supported prior releases. Rem Rem NOTES Rem None Rem Rem MODIFIED (MM/DD/YY) Rem spetride 12/02/09 - 9144511: disable sch validation for XS Rem aime 01/06/10 - XbranchMerge spetride_bug-9144511_main from main Rem rburns 09/30/07 - add 11.1 upgrade Rem mrafiq 06/29/07 - fix for lrg 3019679 Rem pthornto 10/04/06 - add call to xsdbmig.sql for eXtensible Security Rem pkgs Rem spetride 10/13/06 - not mark XDB invalid, recovery checks Rem run if invalid config/acl rows anyway Rem spetride 08/04/06 - enable validate during upgrade Rem vmedi 06/19/06 - tempfix: disable validate during upgrade Rem mrafiq 05/23/06 - fixing status and version numbers Rem petam 04/10/06 - upgrade Fusion Security after XDB fully upgraded Rem abagrawa 03/14/06 - Rem vkapoor 01/25/05 - Adding 102 upgrade script Rem mrafiq 02/23/06 - fix for lrg 2070764: default value for Rem script_name Rem mrafiq 10/04/05 - adding 102 upgrade script Rem fge 10/27/04 - add 10gr2 upgrade script Rem vkapoor 02/15/05 - LRG 1830972. xdbreload needs to be called if upgrade Rem is rerun. Rem pnath 01/19/05 - call xdbinst.sql instead of xdbinstlltab.sql Rem vkapoor 12/16/04 - A new script for upgrade reload Rem pnath 11/22/04 - delete all objects introduced in xdb Rem installation Rem pnath 10/25/04 - Make SYS the owner of DBMS_REGXDB package Rem spannala 05/04/04 - drop xdbhi_idx and recreate later Rem thbaby 01/30/04 - adding 10GR1 upgrade Rem spannala 10/20/03 - migrate status at the beginning of upgrade Rem should be set correctly as per release Rem spannala 06/18/03 - making xdbreload generic enough for all upgrades Rem spannala 06/09/03 - in 9201 upgd, call xdbptrl2 at the end Rem njalali 04/16/03 - removing ?/ notation Rem njalali 04/02/03 - not calling xdbrelod twice on 9.2.0.1 upgrade Rem njalali 03/27/03 - dropping xdb$patchupschema Rem njalali 02/10/03 - enabling upgrade from 9.2.0.3 to 10i Rem njalali 01/16/03 - bug 2744444 Rem njalali 11/21/02 - njalali_migscripts_10i Rem njalali 11/21/02 - Incorporated review comments Rem njalali 11/14/02 - Created Rem Rem Clean up any shared memory taken by JavaVM or anyone else alter system flush shared_pool; alter system flush shared_pool; alter system flush shared_pool; --alter session set events '31150 trace name context forever, level 0x4000'; WHENEVER SQLERROR EXIT; EXECUTE dbms_registry.check_server_instance; WHENEVER SQLERROR CONTINUE; Rem Note that a trigger needs to be added in future releases Rem so that all objects created during upgrade is added Rem to the table xdb_installation_tab, and the dropping Rem of these objects are to be handled in catnoqm.sql Rem Create the table xdb_installation_tab if it does not exist Rem This table ensures that all objects are deleted Rem during the time of XDB un-installation, which were Rem created as a part of XDB installation. @@xdbinst -- Create the table that keeps track of how far along we are in the migration begin execute immediate 'select count(*) from xdb.migr9202status'; exception when others then execute immediate 'create table xdb.migr9202status (n integer)'; execute immediate 'insert into xdb.migr9202status values (400)'; commit; end; / Rem XDB$PATCHUPSCHEMA will have errors drop procedure xdb$patchupschema; Rem display start status select * from xdb.migr9202status; Rem Setup component script filename variable COLUMN :relo_name NEW_VALUE relo_file NOPRINT VARIABLE relo_name VARCHAR2(50) COLUMN :script_name NEW_VALUE comp_file NOPRINT VARIABLE script_name VARCHAR2(50) -- DROP package xdb.dbms_regxdb drop package xdb.dbms_regxdb; -- Create the registry package and the validation procedure @@dbmsxreg -- Load package sys.dbms_regxdb @@prvtxreg.plb set echo on serveroutput on Rem We must run xdbrelod.sql at the end of the upgrade Rem in order to reload the XML DB packages. Rem DECLARE start_status integer := 100000; version varchar2(60); BEGIN :relo_name := '@xdbrelod.sql'; :script_name := '@nothing.sql'; select dbms_registry.version('XDB') into version from dual; IF substr(version, 1, 5) = '9.2.0' THEN -- The upgrade script for all other 92 versions :script_name := '@xdbu9202.sql'; :relo_name := '@xdbrlu.sql'; start_status := 400; ELSIF substr(version, 1, 6) = '10.1.0' THEN :script_name := '@xdbu101.sql'; :relo_name := '@xdbrlu.sql'; start_status := 500; ELSIF substr(version, 1, 6) = '10.2.0' THEN :script_name := '@xdbu102.sql'; :relo_name := '@xdbrlu.sql'; start_status := 700; ELSIF substr(version, 1, 6) = '11.1.0' THEN :script_name := '@xdbu111.sql'; :relo_name := '@xdbrlu.sql'; END IF; dbms_output.put_line('version = ' || version || ' start_status = ' || to_char(start_status)); -- Set the start status correctly if this script is being run for the -- first time. IF dbms_registry.status('XDB') = 'VALID' THEN -- This change will get committed along with the dbms_registry.upgrading -- commit below. IF start_status != 100000 THEN update xdb.migr9202status set n = start_status; END IF; dbms_output.put_line('xdb was valid'); ELSE :relo_name := '@xdbrelod.sql'; dbms_output.put_line('xdb was NOT valid'); END IF; END; / -- This sets the stauts to upgrading and commits the above change -- to migr9202status, if any EXECUTE dbms_registry.upgrading('XDB', 'Oracle XML Database', 'DBMS_REGXDB.VALIDATEXDB'); -- Drop the xdbhi_idx index This will get recreated later in the upgrade declare ct number; begin select count(*) into ct from dba_indexes where owner = 'XDB' and index_name = 'XDBHI_IDX'; if ct > 0 then dbms_output.put_line('dissociating statistics'); execute immediate 'disassociate statistics from ' || 'indextypes xdb.xdbhi_idxtyp force'; execute immediate 'disassociate statistics from ' || 'packages xdb.xdb_funcimpl force'; execute immediate 'drop index xdb.xdbhi_idx'; end if; end; / SELECT :script_name FROM DUAL; @&comp_file SELECT :relo_name FROM DUAL; @&relo_file EXECUTE dbms_registry.upgraded('XDB'); -- drops error tables if error tables for XDB$ACL or XDB$CONFIG empty declare aclnoinv number := 0; resnoinv number := 0; stmtchk varchar2(2000); stmtdrop varchar2(2000); begin begin stmtchk := 'select count(*) from XDB.INVALID_XDB$CONFIG'; dbms_output.put_line(stmtchk); execute immediate stmtchk into resnoinv; if (resnoinv = 0) then stmtdrop := 'drop table XDB.INVALID_XDB$CONFIG'; dbms_output.put_line(stmtdrop); execute immediate stmtdrop; commit; end if; exception when others then -- table already dropped NULL; end; begin stmtchk := 'select count(*) from XDB.INVALID_XDB$ACL'; dbms_output.put_line(stmtchk); execute immediate stmtchk into aclnoinv; if (aclnoinv = 0) then stmtdrop := 'drop table XDB.INVALID_XDB$ACL'; dbms_output.put_line(stmtdrop); execute immediate stmtdrop; commit; end if; exception when others then -- table already dropped NULL; end; end; / Rem EXECUTE sys.dbms_regxdb.validatexdb(); Rem Set XDB to a valid state. Rem We cannot use sys.dbms_regxdb.validatexdb() because Rem resource_view is unusable until the DB is restarted. execute sys.dbms_registry.valid('XDB'); Rem Upgrade Fusion Security after XDB is fully upgraded Rem We run XS upgrade with binary XML schema valiadtion off. Rem For this, remember the level for 31150 event and reset in the end set serveroutput on variable lvl number; variable lvls varchar2(20); declare H VARCHAR2(64) :=''; N INTEGER ; BEGIN dbms_system.read_ev(31150, :lvl); N := :lvl; LOOP SELECT RAWTOHEX(CHR(N))||H INTO H FROM dual; N := TRUNC(N / 256); EXIT WHEN N=0; END LOOP; :lvls := ltrim(H,'0'); end; / select :lvls from dual; Rem set level 4000 to disable schema validation begin if (:lvls is NULL) then execute immediate 'alter session set events ''31150 trace name context forever, level 0x' || '4000' || ''' '; else begin if (length(:lvls)>3) then execute immediate 'alter session set events ''31150 trace name context forever, level 0x' || substr(:lvls, 0, length(:lvls)-4) || '4' || substr(:lvls, length(:lvls)-2) || ''' '; else execute immediate 'alter session set events ''31150 trace name context forever, level 0x' || '4' || lpad(:lvls, 3, '0') || ''' '; end if; end; end if; end; / DECLARE lev BINARY_INTEGER; BEGIN dbms_system.read_ev(31150, lev); dbms_output.put_line(lev); END; / @@xsdbmig.sql begin if (:lvls is not NULL) then execute immediate 'alter session set events ''31150 trace name context forever, level 0x' || :lvls || ''' '; else execute immediate 'alter session set events ''31150 trace name context off'' '; end if; end; / DECLARE lev BINARY_INTEGER; BEGIN dbms_system.read_ev(31150, lev); dbms_output.put_line(lev); END; / set serveroutput off set echo off serveroutput off
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de