Database upgraded successfully!

I have been trying to migrate an Oracle9i database to an Oracle10g Rel2 version. Today I finally got time to read the upgrade guide and do some testing.

Last time while trying to migrate manually I got stuck as I could not find the upgrade scripts like u080 or u090 in ORACLE_HOME/rdbms/admin directory. Today when I read the upgradation manual, I came to know that we need to execute the file named catupgrd.sql for manual upgradation, and I had been searching for the wrong upgradation sql files all the time.

A few things to remember during the upgradation:

  • Increase the size of shared_pool_size to minimum of 178499994 and java_pool_size to a minimum of 67108864. This will prevent you from encountering the following error.
  • Create SYSAUX tablespace if not present before invoking the upgrade scripts. Make sure that the SYSAUX tablespace is created as shown in the upgrade guide or as shown in the overview below with the mandatory attributes. The SYSAUX tablespace is used to consolidate data from a number of tablespaces that were separate in previous releases.
  • Execute the Pre-Upgrade Information Tool (utlu102i.sql) before invoking the upgrade scripts. It will analyze your database to check if the necessary pre-requisites for upgrade are complete.
  • Go through the deprecated parameters list to remove the parameters no longer required from init file.

Here is a brief overview of the migration process. I have already copied the consistent set of datafiles from the source database (after shutdown immediate) to target database and recreated the control files.

  • Operating System: Windows 2000 Professional
  • Source Database Version: Oracle9i Release2 9.2.0.6.0
  • Target Database Version: Oracle10g Release2 10.2.0.1.0
  • CPU: Pentium IV 2.66 GHz
  • RAM: 512MB
set ORACLE_SID=ACME

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 11:45:19 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1247684 bytes
Variable Size              92276284 bytes
Database Buffers           33554432 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle XML Database            9.2.0.6.0                      VALID
Oracle Data Mining             9.2.0.6.0                      VALID
Spatial                        9.2.0.6.0                      VALID
Oracle Ultra Search            9.2.0.6.0                      VALID
Oracle Workspace Manager       9.2.0.1.0                      VALID
Oracle9i Catalog Views         9.2.0.6.0                      VALID
Oracle9i Packages and Types    9.2.0.6.0                      VALID
JServer JAVA Virtual Machine   9.2.0.6.0                      VALID
Oracle XDK for Java            9.2.0.8.0                      VALID
Oracle9i Java Packages         9.2.0.6.0                      VALID
Oracle interMedia              9.2.0.6.0                      VALID
Oracle Text                    9.2.0.6.0                      VALID

12 rows selected.

SQL> create tablespace SYSAUX 
  2  DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ACME\sysaux01.dbf' 
  3  size 500M
  4  extent management local
  5  segment space management auto;

Tablespace created.

SQL> alter system set sga_max_size=300M scope=spfile;

System altered.

SQL> alter system set shared_pool_size=178499994 scope=spfile;

System altered.

SQL> alter system set java_pool_size=67108864 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1248768 bytes
Variable Size             276824576 bytes
Database Buffers           33554432 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.

SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
.
.
.
.
.
.
Oracle Database 10.2 Upgrade Status Utility           09-23-2005 13:50:57
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                  INVALID      10.2.0.1.0  00:21:38
JServer JAVA Virtual Machine              VALID      10.2.0.1.0  00:22:55
Oracle XDK                                VALID      10.2.0.1.0  00:03:35
Oracle Database Java Packages             VALID      10.2.0.1.0  00:01:19
Oracle Text                               VALID      10.2.0.1.0  00:02:48
Oracle XML Database                       VALID      10.2.0.1.0  00:04:46
Oracle Workspace Manager                  VALID      10.2.0.1.0  00:01:27
Oracle Data Mining                        VALID      10.2.0.1.0  00:00:30
Oracle interMedia                         VALID      10.2.0.1.0  00:14:24
Spatial                                   VALID      10.2.0.1.0  00:08:14
Oracle Ultra Search                   NO SCRIPT       9.2.0.6.0  00:00:00
.
Total Upgrade Time: 01:27:45

PL/SQL procedure successfully completed.

SQL> spool off
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1248768 bytes
Variable Size             276824576 bytes
Database Buffers           33554432 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql

OBJECTS WITH ERRORS
-------------------
                  0

1 row selected.

ERRORS DURING RECOMPILATION
---------------------------
                          0

1 row selected.


PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/utlu102s.sql
.
Oracle Database 10.2 Upgrade Status Utility           09-23-2005 14:50:42
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.1.0  00:21:38
JServer JAVA Virtual Machine              VALID      10.2.0.1.0  00:22:55
Oracle XDK                                VALID      10.2.0.1.0  00:03:35
Oracle Database Java Packages             VALID      10.2.0.1.0  00:01:19
Oracle Text                               VALID      10.2.0.1.0  00:02:48
Oracle XML Database                       VALID      10.2.0.1.0  00:04:46
Oracle Workspace Manager                  VALID      10.2.0.1.0  00:01:27
Oracle Data Mining                        VALID      10.2.0.1.0  00:00:30
Oracle interMedia                         VALID      10.2.0.1.0  00:14:24
Spatial                                   VALID      10.2.0.1.0  00:08:14
Oracle Ultra Search                   NO SCRIPT       9.2.0.6.0  00:00:00
.
Total Upgrade Time: 01:27:45

PL/SQL procedure successfully completed.