Manual upgrade from 9i to 10g Release 2

 This procedure describes the steps necessary to manually upgrade a database from 9.2.x to 10.2.x. It assumes that you have already installed 10g onto the server. Of course, it is essential that you take a backup of your database before attempting any upgrade.

1. Compile any invalid objects

@?/rdbms/admin/utlrp.sql

2. Create a sysaux tablespace

Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.

create tablespace sysaux
datafile '<file_name>' size 512M
extent management local
segment space management auto
/
3. Run utlu102i.sql

This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql
Review the output and make any necessary alterations. Make a note of how many invalid objects there are.

4. Shut the database down with either normal or immediate

shutdown immediate

5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.

cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* <new_home>/dbs/

6. Edit oratab

Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home. Once done, rerun oraenv to bring the alteration into effect.

7. Upgrade the database
sqlplus "/ as sysdba"

startup upgrade
This next bit is the upgrade itself. It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.

@?/rdbms/admin/catupgrd.sql
8. Recompile any invalid objects

@?/rdbms/admin/utlrp.sql
Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.

9. Then check the status of the upgrade

@?/rdbms/admin/utlu102s.sql

10. Alter or remove initialisation parameters

Temporarily creating a pfile is the easiest way.

create pfile from spfile;

shutdown immediate

vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
startup

create spfile from pfile;

shutdown immediate

startup
That's it!