Another test case scenario (in addition to one described here) for Oracle Word size change, from 32-bit to 64-bit.

We want source database (32-bit Oracle 10.2.0.2, Windows 2003 SP1, 32-bit) to convert to target database
(64-bit Oracle 10.2.0.3 + Patch 12, Windows 2003 SP2, x64). Basically we’re changing the word size of the database and patching the database at the same time. This scenario was tested in test environment and will be used shortly on one of our production database.

We already installed 64-bit Oracle Software (10.2.0.3 + Patch 12) on our new machine and made necessary configuration of the storage, directories, spfile, password file etc. We also copied all database/ctl files from production server to the new, x64 machine (after shutdown normal).

You must be aware of the following:

• it’s important that target (64-bit) Oracle10g is of the proper release (minimum 10.2.0.3 + Patch 5, in our case is Patch 12, otherwise known as CPUOCT2007) or you might hit ORA-600 error that I wrote about.
• make sure SQLNET.ORA is present on target database and that contains NAMES.DIRECTORY_PATH= (TNSNAMES), or you might hit another bug.
• don’t even think of using Oracle on Windows 2003 with SP1 and large amount of memory! Read this before you decide what to do.
• make sure you read Metalink Note:62290.1 “Changing between 32-bit and 64-bit Word Sizes”
• read and understand patchset readme (10.2.0.3) as well as readme that comes with Patch 12.
• this test scenario is for single, non-RAC, Oracle instance

From here on the steps are:

1) create service on x64:

set ORACLE_SID=ORCL
D:\oracle\ORA10\bin\oradim.exe -new -sid ORCL -startmode manual -spfile 
D:\oracle\ORA10\bin\oradim.exe -edit -sid ORCL -startmode auto -srvcstart system 

2) start the instance in upgrade mode

I’m intentionally using pfile for the sake of simplicity of editing; after migration spfile is used)

startup upgrade pfile=D:\ORACLE\ORA10\database\initorcl.ora

3) invalidate PL/SQL packages

spool CATOUT_32_64.log
@D:\ORACLE\ORA10\RDBMS\ADMIN\UTLIRP.SQL
spool off

Check CATOUT_32_64.log.

Do not shutdown and restart the database!

4) Upgrade

SPOOL CATUPGRD.LOG
@D:\ORACLE\ORA10\RDBMS\ADMIN\CATUPGRD.SQL
SPOOL OFF

Check CATUPGRD.LOG for errors – some errors can be ignored, refer to readme that comes with Patchset 10.2.0.3.

5) Check status for the package DBMS_STANDARD

select status from dba_objects where object_name='DBMS_STANDARD'
and object_type='PACKAGE' and owner='SYS';

Recompile if package is invalid:
alter package dbms_standard compile;

6) Check the number of invalid objects for SYS and recompile if needed

select count(*) from dba_objects where owner='SYS' and status='INVALID';

@D:\ORACLE\ORA10\RDBMS\ADMIN\UTLRP.SQL

7) Check status of database components

column comp_name format a40
column version format a10
select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION    STATUS
---------------------------------------- ---------- -----------
Oracle interMedia                        10.2.0.3.0 VALID
Oracle Enterprise Manager                10.2.0.3.0 VALID
Oracle XML Database                      10.2.0.3.0 VALID
Oracle Text                              10.2.0.3.0 VALID
Oracle Expression Filter                 10.2.0.3.0 VALID
Oracle Rule Manager                      10.2.0.3.0 VALID
Oracle Workspace Manager                 10.2.0.1.0 VALID
Oracle Data Mining                       10.2.0.3.0 VALID
Oracle Database Catalog Views            10.2.0.3.0 VALID
Oracle Database Packages and Types       10.2.0.3.0 VALID
JServer JAVA Virtual Machine             10.2.0.3.0 VALID
Oracle XDK                               10.2.0.3.0 VALID
Oracle Database Java Packages            10.2.0.3.0 VALID
SQL>

8) Apply CPUOCT2007

In addition of running CATUPGRD.SQL we have to run CATCPU script as well, in our case for CPUOct2007.

cd %ORACLE_HOME%\CPU\CPUOct2007
sqlplus /nolog 
CONNECT / AS SYSDBA 
@catcpu.sql
QUIT 


The script will end with report similar as:

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CPU_END    2008-01-18 20:22:54

No. of Invalid Objects is :575
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current catcpu.sql session is : APPLY_ORAX_18Jan2008_20_18_52.log

Check log file mentioned in catcpu.sql report - some errors can be ignored (refer to CPU readme)!

At this stage you can check dba_registry again:

SQL> column comp_name format a40
SQL> column version format a10
SQL> select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION    STATUS
---------------------------------------- ---------- -----------
Oracle interMedia                        10.2.0.3.0 VALID
Oracle Enterprise Manager                10.2.0.3.0 VALID
Oracle XML Database                      10.2.0.3.0 VALID
Oracle Text                              10.2.0.3.0 VALID
Oracle Expression Filter                 10.2.0.3.0 VALID
Oracle Rule Manager                      10.2.0.3.0 VALID
Oracle Workspace Manager                 10.2.0.1.0 VALID
Oracle Data Mining                       10.2.0.3.0 VALID
Oracle Database Catalog Views            10.2.0.3.0 VALID
Oracle Database Packages and Types       10.2.0.3.0 UPGRADED
JServer JAVA Virtual Machine             10.2.0.3.0 VALID

COMP_NAME                                VERSION    STATUS
---------------------------------------- ---------- -----------
Oracle XDK                               10.2.0.3.0 VALID
Oracle Database Java Packages            10.2.0.3.0 VALID

13 rows selected.

9) Run UTLRP.SQL once again…

@D:\ORACLE\ORA10\RDBMS\ADMIN\UTLRP.SQL 

## WELCOME TO 64-bit World – END OF TEST CASE ##