8
Database Migration Using Export/Import

This chapter guides you through the process of upgrading and downgrading data in an Oracle database using the Export and Import utilities. This chapter covers the following topics:

• Export Dump File Compatibility
• Source Database and Target Database
• Upgrade the Source Database Using Export/Import

  See Also: Oracle9i Database Utilities for detailed information about the Export and Import utilities

Export Dump File Compatibility

Export dump files can be imported into all future releases of Oracle. For example, an Oracle7 export dump file can be imported by the release 8.1.7, release 9.0.1, and release 9.2 Import utilities.

Export dump files, however, are not downward compatible with the Import utilities of previous Oracle releases. That is, exported data cannot be imported by the Import utilities of previous Oracle releases. For example, a release 8.1.7 export dump file cannot be imported by the release 8.0.6 Import utility, and an Oracle9i export dump file cannot be imported by the Oracle7 Import utility.

The contents of a database can be imported into a previous Oracle release if you use the Export and Import utilities of the previous release to export and import the data. Table 8-1 details this support.

Table 8-1  Backward Compatibility Support for Export/Import

To Export Data From	Import Into	Use Export/Import Utilities For
Release 9.2 Release 9.0.1 Release 8.1.7 Release 8.0.6	Release 7.3.4	Release 7.3.4 Note: Run the catexp7.sql script before exporting.
Release 9.2 Release 9.0.1 Release 8.1.7	Release 8.0.6	Release 8.0.6
Release 9.2 Release 9.0.1	Release 8.1.7	Release 8.1.7
Release 9.2	Release 9.0.1	Release 9.0.1

As Table 8-1 indicates, to export data from a release 8.0 or higher database into an Oracle7 database, you must first run the catexp7.sql script on the release 8.0 or higher database before using the Oracle7 Export utility to export the data.

You do not need to run the catexp7.sql script if you are exporting data from a release 8.1 or higher database into a release 8.0 database.

Export/Import Usage on Data Incompatible with a Previous Release

When you export data to a previous release, data that is incompatible with the previous release either is not exported at all or is exported with the loss of some features.

For example, partitioned tables are not exported by the Oracle7 Export utility. If you need to move a partitioned table from a release 8.0 or higher database into an Oracle7 database, then first reorganize the table into a non-partitioned table. Another example involves procedures that use invoker-rights in release 8.1 and higher. If you use the release 8.0 Export utility, then these procedures are exported, but they do not function properly in release 8.0 because release 8.0 does not support invoker-rights. Therefore, in general, if you need to export data to a previous release, then first remove as many incompatibilities with the previous release as possible before you export the data.

Source Database and Target Database

The source database is the database containing the data to be exported. The target database is the database into which you are importing the exported data.

Export Utility Requirements

To upgrade a database, use the Export utility shipped with the release of the source database. After the export, the Import utility can copy the data from the export dump file into the target database, which is a new Oracle9i database. The new Oracle9i database must be created and operational before the Import utility can import the exported data.

For example, if you are upgrading to release 9.2 from release 7.3.4, then use the Export utility for release 7.3.4.

Import Utility Requirements

To upgrade a database, use the Import utility shipped with the release of the target database, which is a new Oracle9i database. For example, if you are upgrading to release 9.2 from release 7.3.4, then use the Import utility for release 9.2.

Upgrade the Source Database Using Export/Import

To upgrade a database using the Export/Import utilities, complete the following steps:

1. Export data from the source database using the Export utility shipped with the source database. See the source database's server utilities documents for information about using the Export utility on the source database.

   To ensure a consistent export, make sure the source database is not available for updates during and after the export. If the source database will be available to users for updates after the export, then, prior to making the source database available, put procedures in place to copy the changes made in the source database to the new Oracle9i target database after the import is complete.

2. Install the new Oracle9i software. Installation is operating system-specific. Installation steps for Oracle9i are covered in your operating system-specific Oracle documentation.
3. If the new Oracle9i database will have the same name as the existing source database, then shut down the existing database before creating the new Oracle9i database.
4. Create the new Oracle9i target database.

   See Also: Oracle9i Database Administrator's Guide for information about creating an Oracle9i database

5. Start SQL*Plus in the new Oracle9i environment.
6. Connect to the database instance as a user with SYSDBA privileges.
7. Start an Oracle9i database instance using STARTUP.
8. Pre-create tablespaces, users, and tables in the target database to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus, either run the database in the original database compatibility mode or make allowances for the specific data definition conversions that occur during import.

   Note: If the new Oracle9i database will be created on the same computer as the source database, and you do not want to overwrite the source database datafiles, then you must pre-create the tablespaces and specify IGNORE=Y and DESTROY=N when you import.

9. Use the Import utility of the new Oracle9i database to import the objects exported from the source database. Include the LOG parameter to save the informational and error messages from the import session to a file.

   See Also: Oracle9i Database Utilities for a complete description of the Import utility.

10. After the import, check the import log file for information about which imports of which objects completed successfully and, if there were failures, which failed.

    See Also: Oracle9i Database Utilities and the Oracle9i server README.doc file for error handling information.

11. Use further Import scenarios (see Oracle9i Database Utilities) or SQL scripts that create the source objects to clean up incomplete imports (or possibly to start an entirely new import).
12. If changes are made to the source database after the export, then make sure those changes are propagated to the new Oracle9i database prior to making it available to users. See Step 1 for more information.
13. Complete the procedures described in Chapter 4, "After Upgrading a Database".