Migrating a Database from 8i or 9i

You have several options to upgrade your existing databases in Oracle 8i or 9i to Oracle Database 10g. We will skim over the various steps used for the manual migration process as well as direct migration steps for the different versions. Before you plan for any upgrade process, make sure that you have the latest backup of your database on media and keep a database copy on another identical machine. This copy database will be there for you to point your applications to in the event something goes wrong during the upgrade process and you experience significant downtime.

More details of the upgrade processes are mentioned in Oracle Database Upgrade Guide 10g.

Is Export/Import a Good Option?

The traditional export/import process can be used for migrating data from any prior supported Oracle database versions if their data types are still supported in Oracle Database 10g. The export/import process is advisable only for smaller databases (maximum of a few tens of gigabytes in database size) and for databases with relatively few indexes or constraints. In the case of database upgrades with a large number of indexes, it is advisable to import the data with the indexes option turned off (INDEXES=N) and to run a separate script to re-create the indexes after data load. Make sure that the data in the migrating database is clean and that indexes are consistent before you start the export/import process.

If your old database is Oracle 7.3.4 or earlier, you have to upgrade it to Oracle 8.0.6 or 8.1.7. If the old database is version 8.0.5 or earlier, then upgrade it to 8.0.6. From 8.0.6, you can upgrade it to Oracle Database 10g. For 8.1.5 and 8.1.6 databases, upgrade to 8.1.7 and from there to 10g. For all Oracle 9i databases, direct upgrade is available.

For a direct upgrade from Oracle 8.0.6 to 10g, use the script u0800060.sql in the $ORACLE_HOME/rdbms/admin directory. From Oracle 8.1.7 to 10g, use the script u0801070.sql; from Oracle 9.0.1 to 10g, use the script u0900010.sql; and from Oracle 9.2 to 10g, use the script u0902000.sql. All these scripts are in the $ORACLE_HOME/rdbms/admin directory.

There are several steps to be done after the migration, such as collecting baseline statistics, tuning the database and SQL statements for Cost Based Optimizer (CBO), managing objects, adding new features, and so on, which are discussed in specific MetaLink documentation and in the upgrade guide. The steps would easily run over several pages if completely listed here, and as such have been omitted.