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
* 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
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.
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
* 5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
* 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"
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.
* 8. Recompile any invalid objects
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
* 10. Alter or remove initialisation parameters
Temporarily creating a pfile is the easiest way.
create pfile from spfile;
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
create spfile from pfile;