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:

1. install 64-bit Oracle EE 10.2.0.1 + Patchset 10.2.0.3 + Patch 5 on Windows 2003 SP2 x64
2. 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.
3. restore rman backup set with 64-bit Oracle 10.2.0.3
4. after restore, startup the database with upgrade option and invalidate PL/SQL objects in the database with utlirp.sql
5. run upgrade script (catupgrd.sql)
6. 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
#################