Test case: upgrading 10.2.0.2 database (32-bit) to 10.2.0.3 (64-bit)
Disclaimer: this note is not meant to be a step-by-step guide for someone seeking a “recipe” of how to change the word size of the database, without really understanding the process, skipping relevant Metalink notes and Oracle documentation. Some steps are left out on purpose, because Metalink notes listed under References are a proper source of information. The sole purpose of this note is to serve me in the future as a reference, a quick reminder of what I did and how I dit it. Don’t event think of asking me to clarify the particular step, because all I’ll do is to send you a link to Metalink and Oracle docs. You’re warned;-)
I used RMAN backup of one of our smal database that runs currently on Windows 2003 (32-bit, Oracle EE 10.2.0.2) to test the upgrade path from 32-bit Windows 2003 to 64-bit Windows 2003 (EMT64). The server was Dell 2950, 16GB RAM and with 2 x Quad core CPU’s. The source version of the database was 10.2.0.2 (32-bit) and the target version was 10.2.0.3 (64-bit) with Patch 5 (see memo for a reason).
Summary of test scenario:
- install 64-bit Oracle EE 10.2.0.1 + Patchset 10.2.0.3 + Patch 5 on Windows 2003 SP2 x64
- copy RMAN backupset from production server (running 32-bit Oracle EE 10.2.0.2) to test server running Windows 2003 x64 with SP2 and Oracle EE 10.2.0.3 (Patch5) for x64 platform.
- restore rman backup set with 64-bit Oracle 10.2.0.3
- after restore, startup the database with upgrade option and invalidate PL/SQL objects in the database with utlirp.sql
- run upgrade script (catupgrd.sql)
- recompile invalid objects (utlrp.sql)
References:
Metalink Note:62290.1 “Changing between 32-bit and 64-bit Word Sizes”
Metalink Note:412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3
Trace file from point 3 onwards (with dummy DBID and database name).
###################################### Restore control file - without catalog ###################################### D:\ORACLE>set NLS_DATE_FORMAT=DD.MM.YYYY D:\ORACLE>rman target / nocatalog Recovery Manager: Release 10.2.0.3.0 - Production on +et Sep 13 11:40:34 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: TESTDB (not mounted) using target database control file instead of recovery catalog RMAN> set dbid=1234567890 executing command: SET DBID RMAN> run { 2> set controlfile autobackup format for device type disk to 'D:\ORADATA\TESTDB\BACKUP\RMAN01\TESTDB_%F.BAK'; 3> allocate channel c1 DEVICE TYPE disk; 4> restore controlfile from autobackup; 5> alter database mount; 6> } executing command: SET CONTROLFILE AUTOBACKUP FORMAT allocated channel: c1 channel c1: sid=82 devtype=DISK Starting restore at 13.09.2007 channel c1: looking for autobackup on day: 20070913 channel c1: looking for autobackup on day: 20070912 channel c1: looking for autobackup on day: 20070911 channel c1: autobackup found: D:\ORADATA\TESTDB\BACKUP\RMAN01\TESTDB_c-1059187208-20070911-00.BAK channel c1: control file restore from autobackup complete output filename=D:\ORADATA\TESTDB\DATA\DATA01\CONTROL01.CTL output filename=D:\ORADATA\TESTDB\DATA\DATA02\CONTROL02.CTL output filename=D:\ORADATA\TESTDB\DATA\CONTROL03.CTL Finished restore at 13.09.2007 database mounted released channel: c1 ########################################## Restore database ########################################## RMAN> run { 2> allocate channel c1 device type disk; 3> restore database check readonly; 4> } allocated channel: c1 channel c1: sid=82 devtype=DISK Starting restore at 13.09.2007 channel c1: starting datafile backupset restore channel c1: specifying datafile(s) to restore from backup set restoring datafile 00001 to D:\ORADATA\TESTDB\DATA\DATA01\SYSTEM01.DBF restoring datafile 00002 to D:\ORADATA\TESTDB\DATA\DATA01\UNDOTBS01.DBF restoring datafile 00003 to D:\ORADATA\TESTDB\DATA\DATA01\SYSAUX01.DBF restoring datafile 00004 to D:\ORADATA\TESTDB\DATA\DATA02\USERS01.DBF restoring datafile 00005 to D:\ORADATA\TESTDB\DATA\DATA01\BKLAS01.DBF restoring datafile 00006 to D:\ORADATA\TESTDB\DATA\DATA02\DATA1_01.DBF restoring datafile 00007 to D:\ORADATA\TESTDB\DATA\DATA02\DESIGN01.DBF restoring datafile 00008 to D:\ORADATA\TESTDB\DATA\DATA01\ZTDAT1_1.DBF restoring datafile 00009 to D:\ORADATA\TESTDB\DATA\DATA02\DATA1_02.DBF restoring datafile 00010 to D:\ORADATA\TESTDB\DATA\DATA02\DATA1_03.DBF restoring datafile 00011 to D:\ORADATA\TESTDB\DATA\DATA01\DATA2_01.DBF restoring datafile 00012 to D:\ORADATA\TESTDB\DATA\DATA02\SBSDAT01.DBF channel c1: reading from backup piece D:\ORADATA\TESTDB\BACKUP\RMAN01\TESTDB_632723409_929_1.BAK channel c1: restored backup piece 1 piece handle=D:\ORADATA\TESTDB\BACKUP\RMAN01\TESTDB_632723409_929_1.BAK tag=TAG20070908T043008 channel c1: restore complete, elapsed time: 00:16:36 Finished restore at 13.09.2007 released channel: c1 ########################################## Recover database ########################################## RMAN> run { 2> # last available log sequence + 1 3> set until sequence 668 thread 1; 4> recover database; 5> } executing command: SET until clause Starting recover at 13.09.2007 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=82 devtype=DISK channel ORA_DISK_1: starting incremental datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: D:\ORADATA\TESTDB\DATA\DATA01\SYSTEM01.DBF destination for restore of datafile 00002: D:\ORADATA\TESTDB\DATA\DATA01\UNDOTBS01.DBF destination for restore of datafile 00003: D:\ORADATA\TESTDB\DATA\DATA01\SYSAUX01.DBF destination for restore of datafile 00004: D:\ORADATA\TESTDB\DATA\DATA02\USERS01.DBF destination for restore of datafile 00005: D:\ORADATA\TESTDB\DATA\DATA01\BKLAS01.DBF destination for restore of datafile 00006: D:\ORADATA\TESTDB\DATA\DATA02\DATA1_01.DBF destination for restore of datafile 00007: D:\ORADATA\TESTDB\DATA\DATA02\DESIGN01.DBF destination for restore of datafile 00008: D:\ORADATA\TESTDB\DATA\DATA01\ZTDAT1_1.DBF destination for restore of datafile 00009: D:\ORADATA\TESTDB\DATA\DATA02\DATA1_02.DBF destination for restore of datafile 00010: D:\ORADATA\TESTDB\DATA\DATA02\DATA1_03.DBF destination for restore of datafile 00011: D:\ORADATA\TESTDB\DATA\DATA01\DATA2_01.DBF destination for restore of datafile 00012: D:\ORADATA\TESTDB\DATA\DATA02\SBSDAT01.DBF channel ORA_DISK_1: reading from backup piece D:\ORADATA\TESTDB\BACKUP\RMAN01\TESTDB_633042031_933_1.BAK channel ORA_DISK_1: restored backup piece 1 piece handle=D:\ORADATA\TESTDB\BACKUP\RMAN01\TESTDB_633042031_933_1.BAK tag=TAG20070911T210008 channel ORA_DISK_1: restore complete, elapsed time: 00:00:46 starting media recovery archive log filename=D:\ORADATA\TESTDB\REDOARH\ARHIV01\ARC667_1_582315976.LOG thread=1 sequence=667 media recovery complete, elapsed time: 00:00:01 Finished recover at 13.09.2007 ############################################# Open the database with resetlogs ############################################# RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/13/2007 13:28:13 ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== ORA-03114: not connected to ORACLE RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/13/2007 13:28:13 ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option ###################################################### Since we restored 10.2.0.2 database on Windows x64 with 10.2.0.3 installed version, we can't open the database without upgrade option - redo logs are however re-created at this time! ###################################################### SQL> startup upgrade; ORACLE instance started. Total System Global Area 343932928 bytes Fixed Size 2054808 bytes Variable Size 201327976 bytes Database Buffers 134217728 bytes Redo Buffers 6332416 bytes Database mounted. Database opened. SQL> alter system set shared_pool_size=800M scope=both; SQL> alter system set java_pool_size=250M scope=both; ###################################################### We need to invalidate PL/SQL packages, before we run catupgrd.sql script! ###################################################### SQL> @d:\oracle\ora10\rdbms\admin\utlirp.sql DOC>####################################################################### DOC>####################################################################### DOC> utlirp.sql completed successfully. All PL/SQL objects in the DOC> database have been invalidated. DOC> DOC> Shut down and restart the database in normal mode and run utlrp.sql to DOC> recompile invalid objects. DOC>####################################################################### DOC>####################################################################### Don't restart the database! ##################################################### Now we can run catupgrd.sql script. Make sure you turn on the spool, because you'll need to check the log for any errors. ##################################################### SQL> spool catupgrd.log SQL> @D:\oracle\ora10\rdbms\admin\catupgrd.sql < -x- > TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UPGRD_END 2007-09-13 14:03:54 . Oracle Database 10.2 Upgrade Status Utility 09-13-2007 14:03:54 . Component Status Version HH:MM:SS Oracle Database Server VALID 10.2.0.3.0 00:05:32 JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:01:48 Oracle XDK VALID 10.2.0.3.0 00:00:12 Oracle Database Java Packages VALID 10.2.0.3.0 00:00:06 Oracle XML Database VALID 10.2.0.3.0 00:00:30 Oracle interMedia INVALID 10.2.0.3.0 00:01:05 Oracle Expression Filter INVALID 10.2.0.3.0 00:00:06 Oracle Enterprise Manager VALID 10.2.0.3.0 00:00:56 Oracle Rule Manager INVALID 10.2.0.3.0 00:00:03 . Total Upgrade Time: 00:10:21 DOC>####################################################################### DOC>####################################################################### DOC> DOC> The above PL/SQL lists the SERVER components in the upgraded DOC> database, along with their current version and status. DOC> DOC> Please review the status and version columns and look for DOC> any errors in the spool log file. If there are errors in the spool DOC> file, or any components are not VALID or not the current version, DOC> consult the Oracle Database Upgrade Guide for troubleshooting DOC> recommendations. DOC> DOC> Next shutdown immediate, restart for normal operation, and then DOC> run utlrp.sql to recompile any invalid application objects. DOC> DOC>####################################################################### DOC>####################################################################### DOC># !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! At this time you should review the spooled log file for any errors! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ######################## Repair invalid objects ######################## SQL> shutdown SQL> startup SQL> @D:\ORACLE\ORA10\RDBMS\ADMIN\utlrp.sql ######################################### Check if the installed components are valid ######################################### SQL> column comp_name format a30 SQL> select comp_name, status, version from dba_registry; COMP_NAME STATUS VERSION ------------------------------ ----------- ------------------------------ Oracle Enterprise Manager VALID 10.2.0.3.0 Oracle interMedia VALID 10.2.0.3.0 Oracle XML Database VALID 10.2.0.3.0 Oracle Expression Filter VALID 10.2.0.3.0 Oracle Rule Manager VALID 10.2.0.3.0 Oracle Workspace Manager VALID 10.2.0.1.0 Oracle Database Catalog Views VALID 10.2.0.3.0 Oracle Database Packages and T VALID 10.2.0.3.0 ypes JServer JAVA Virtual Machine VALID 10.2.0.3.0 Oracle XDK VALID 10.2.0.3.0 Oracle Database Java Packages VALID 10.2.0.3.0 ################# END #################
Posted on 22.09.2007, in Oracle and tagged oracle. Bookmark the permalink. Comments Off on Test case: upgrading 10.2.0.2 database (32-bit) to 10.2.0.3 (64-bit).