1. Take a consistent backup of your database.
2. Install Oracle 10gR2
3. Check objects status in the db.
Compile invalid objects.
@?/rdbms/admin/ utlrp.sql
4. Create SYSAUX tablespace.
A manadatory requirement for Oracle 10G
5. Run utlu102i.sql script which checks the db readiness to upgrade to 10g.
This script will be available in oracle10g home. So specify complete path for oracle 10g rdbms/admin directory
6. Shutdown immediate the database.
7. Copy the parameter file & password file from existing home to new Oracle 10g Home.
8. Edit oratab and rerun oraenv to bring the changes in affect.
Oratab would be in /etc/oratab OR /var/opt/oracle/oratab
9. Startup the database in upgrade mode.
Command: startup upgrade
10. Now upgrade for which run catupgrd script. Spool the output.
This can take upto 40 mins to complete.
@?/rdbms/admin/catupgrd.sql
11. Now recompile invalid objects.
Run utlrp.sql
Remember to compare the status of objects with the one before upgrade. Current should be same or Less.
12. Check the status of upgrade
@?/rdbms/admin/utlu102s.sql
13. Alter / reset the parameter file and set the compatibility parameter.
The upgrade of Oracle 9i To 10g is completed successfully.
For further detailed reading refer Oracle documentation & metalink docs.
Useful read about Optimizer stats while upgrading:
http://optimizermagic.blogspot.com/2008/02/upgrading-from-oracle-database-9i-to.html
Metalink Doc: Complete Checklist for Manual Upgrades to 10gR2 [ID 316889.1]
