Note: If you want to upgrade 9.2.0.1.0, you must apply patch set 7 R2 (9.2.0.8) (Down load from Metal ink) or you can upgrade directly from 9.2.0.8.0

We can use three methods to upgrade our oracle database.
Export / Import
Database Upgrade Assistant
Manually by using Scripts (We prefer manually method to upgrade our database)

Step 1 (Pre upgrade Task)

a) Install the oracle 10g database software in new oracle home.
b) Connect to the oracle database form 9i and run pre-upgrade scripts (utlu102i), which is store in Oracle 10 g Home/rdbms/admin.
c) Follow the steps suggested from output of above steps.

Step 2 (As per instruction of above output, you must change database and initSID file.)

1. Shutdown the 9i instance

SQL>SHUTDOWN IMMEDIATE

2. Stop the OracleServiceSID Oracle service of the oracle 9i database

C:\> NET STOP OracleService

3. Delete the OracleServiceSID at command line of 9i Home

C:\>ORADIM –DELETE –SID

Step 3

1.Create the new oracle database 10g service at command prompt using the following command.

C:\>ORADIM –NEW –SID –INTPWD –STARTMODE A

2. Put your init file in database folder at new oracle 10g home from 9i.

Step 4

1. Connect to the new oracle 10g instance as a user sysdba privilege and issue following command:

SQL>STARTUP UPGRADE

You don’t need to use the PFILE option to specify the location of your initialization parameter file in our case because we are using INIT file in default location (which is reside in Oracle10gHome/database). We have just put init file at new oracle 10g home from 9i.

The error may be occur, when you attempting to start the new oracle Database 10g release. If you receive, issue the SHUTDOWN ABORT commands to shut down the database and correct the problem.

Step 5

Create SYSAUX table space for the database.
CREATE TABLESPACE sysaux DATAFILE ‘sysaux01.dbf’
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

NOTE:

If you are upgrading from 10.1, then skip step5 otherwise create SYSAUX tablespace. In oracle 10g , the SYSAUX tablespace is used to consolidate data from a number of tablespace that where separated in previous release.

Step 6

1. Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

2. Run the upgrade scripts.

SQL>@catupgrd.sql

The catupgrd.sql script determines which scripts need to be run and then runs each necessary scripts.

3. Run the result of the upgrade display report.

SQL>@utlu102s.sql

The Post-upgrade status Tool display the status of the database components in the upgrade database and time required to complete each component upgrade.

4. Turn off the spooling of script result to the log file

SQL>spool off;

5. Shutdown the instance and restart.

SQL> STHTDOWN IMMEDIATE

SQL> STARTUP

Step 7 (Post migration Task)

1. Remove the obsolete initialization parameter from parameter file.

2. Check Invalid objects in database by using following Query:

SQL> select count(*) from dba_objects where status=’INVALID’;

3. Run utlrp.sql scripts to recompile any remaining stored Pl/SQL and java codes.

SQL > @utlro.sql

4. Exit SQL* PLUS