Doing it the hard way
Oracle's universal Installer (OUI) provides an upgrade option for DBA's to migrate the database from one version to another. However, many do not like this method, since it does not allow them to view what is actually going on during the migration.
To run an upgrade manually, perform the following steps:
1. Make sure that all database objects are in a valid state. Invalid objects need to be fixed or dropped. Run the script $BANNER_LINKS/guraltr.sql provided by SCT or $ORACLE_HOME/rdbms/admin/utlrp.sql provided by Oracle to re-compile all invalid objects.
2. Perform a full cold, offline back of the database to upgraded. In addition, back up the $ORACLE_HOME directory that is to be upgraded. After the back is done, shut down the oracle listener.
3. Install the new Oracle software. You can either install over the existing $ORACLE_HOME, or install into a completely different directory. Either will work.
4. Complete the following steps ONLY if the new version of Oracle is installed into a different directory. Otherwise proceed to step 5.
i. copy over from the old $ORACLE_HOME all relevant config files
(init.ora, listener.ora, tnsnames.ora, etc).
You must copy the appropriate init.ora's for ALL instances
that you are planning to upgrade.
ii. make the appropriate changes for the new Oracle home in the oratab file.
iii. Re-source the oraenv file to update the new value of $ORACLE_HOME
5. Startup the database in restricted mode using the new Oracle software. At this point you datafiles will be a different version than $ORACLE_HOME, this is fine for now. AT THIS POINT DO NOT SHUTDOWN THE INSTANCE UNTIL THE UPGRADE IS COMPLETE
Run the appropriate script located in $ORACLE_HOME/rdbms/admin as the user SYS to begin the upgrade process.
From To: Script
---- ---- -------------
8.0.6 9.0.1 u0800060.sql
8.0.6 9.2 u0800060.sql
8.1.5 9.0.1 u0801050.sql
8.1.5 9.2 Not Supported
8.1.6 9.0.1 u0801060.sql
8.1.6 9.2 Not Supported
8.1.7 9.0.1 u0801070.sql
8.1.7 9.2 u0801070.sql
9.0.1 9.2 u0900010.sql
Note that if there is no script, a direct path for the corresponding versions is not supported. You must then go to the next highest version and start the upgrade process again from that version. For example, if you are at 8.1.6, and want to go to 9.2.0, you must first upgrade to 8.1.7. After you upgrade to 8.1.7, you can upgrade to 9.2.
The upgrade steps can take a long time, especially on slow systems, so be patient.
6. When the script has finished, run $ORACLE_HOME/rdbms/admin/utlrp to re-compile all packages. Note that all objects are invalidated during the upgrade process, so this step may also take a while.
