Skip Headers
Oracle® Database Upgrade Guide
11g Release 1 (11.1)

Part Number B28300-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

8 Moving Data Using Data Pump and Export/Import

This chapter guides you through the process of using Data Pump Export and Import utilities, or the original Export and Import utilities to move data from one database to another.

This chapter covers the following topics:

See Also:

Oracle Database Utilities for detailed information about Data Pump and the Export and Import utilities

When to Use Data Pump Export/Import Versus Original Export/Import

The Data Pump Export (expdp) and Import (impdp) utilities have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate. The Data Pump Export and Import utilities, first released in Oracle Database 10g Release 1 (10.1), are high performance replacements for the original Export and Import utilities. When upgrading from Oracle Database 10g Release 1 (10.1) or higher, Oracle recommends the Data Pump Export and Import utilities in most cases because these utilities provide greatly enhanced performance compared to the original Export and Import utilities.

See Also:

  • "Export/Import" for information on when to use this upgrade method

  • Oracle Database Utilities for a detailed list of Data Pump features that contribute to increased performance, as well as to enhanced ease-of-use

Data Pump Export and Import offer the following advantages:

See Also:

Oracle Database Utilities for an overview of Data Pump Export and Import

The original Export/Import utilities are required for the following types of database upgrades and downgrades:

See Also:

Oracle Database Utilities. for information on moving a database from one platform to another

Export and Import Requirements

Dump files generated by the Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility, and vice versa.

Export and Import Requirements for Upgrades

Dump files created by one of the Export utilities can be imported into all future releases of Oracle Database. For example, an Oracle8 original Export dump file can be imported by the Oracle8i, Oracle9i, and Oracle Database 10g original Import utility.

Table 8-1 shows which releases to use when exporting data from releases earlier than Oracle Database 11g Release 1 (11.1) and then importing that data into Oracle Database 11g Release 1 (11.1).

Table 8-1 Exporting Data From Releases Earlier Than 11.1 and Importing Into Release 11.1

Export From Import To Export Version to Use Import Version to Use

Release 10.2

Release 11.1

Data Pump Export Release 10.2

Data Pump Import Release 11.1

Release 10.1

Release 11.1

Data Pump Export Release 10.1

Data Pump Import Release 11.1

Release 9.2

Release 11.1

Original Export Release 9.2

Original Import Release 11.1

Release 8.1.7

Release 11.1

Original Export Release 8.1.7

Original Import Release 11.1

Release 8.0.6

Release 11.1

Original Export Release 8.0.6

Original Import Release 11.1

Release 7.3.4

Release 11.1

Original Export Release 7.3.4

Original Import Release 11.1


Export and Import Requirements for Downgrades

The Oracle Database release to which you downgrade can be one release older, at the most. For example, if the current database is Oracle Database 11g Release 1 (11.1), then Oracle Database 10g Release 2 (10.2) is the only release supported for the downgrade.

To obtain a downward compatible dump file:

  • With Data Pump Export, use the current version's Data Pump Export utility, but set the VERSION parameter to the target older version.

    Data Pump Import cannot read dump file sets created by a database version that is newer than the current database version, unless those dump file sets were created with the VERSION parameter set to the version of the target database. Therefore, the best way to perform a downgrade is to perform your Data Pump export with the VERSION parameter set to the version of the target database.

    See Also:

    Oracle Database Utilities for more information about using the VERSION parameter
  • With original Export, run an older version of Export (exp) to produce a dump file that is compatible with the database version to which you want to downgrade. Then, run the target's original Import utility.

    Original Export dump files are not downward compatible with the Import utilities of previous Oracle Database releases. That is, exported data cannot be imported by the Import utilities of previous Oracle Database releases. For example, an Oracle9i Export dump file cannot be imported by an Oracle8i Import utility, and an Oracle Database 10g export dump file cannot be imported by an Oracle9i Import utility. The following tables provide specific examples.

See Also:

Oracle Database Utilities for more information on using different releases and versions of Export

Table 8-2 shows which releases to use when exporting data from Oracle Database 11g Release 1 (11.1) and then importing that data into earlier releases. Major release downgrades are supported to 10.2 and 10.1.

Table 8-2 Exporting Data From Release 11.1 and Importing Into Earlier Releases

Export From Import To Export Version to Use Import Version to Use

Release 11.1

Release 10.2

Data Pump Export Release 11.1 with VERSION=10.2

Data Pump Import Release 10.2

Release 10.2

Release 10.1

Data Pump Export Release 10.2 with VERSION=10.1

Data Pump Import Release 10.1


Note:

When using the original Export utility, if the source database is newer than the target database, then you must run the catexp.sql script supplied with the previous (older) release for the export to be successful. Once the export is done, then run the catexp.sql script from the newer release to restore the export views. Perform the steps as follows:
  1. Run the older CATEXP.SQL script on the database to be exported.

  2. Use the older Export utility to create the dump file.

  3. Use the older Import utility to import to the target database.

  4. Run the newer CATEXP.SQL script on the exported 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.

In general, if you must export data to a previous release, then first remove as many incompatibilities with the previous release as possible before you export the data.

Upgrade the Database Using Export/Import

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

  1. Export data from the current database using the Export utility shipped with the current database. See the current Oracle Database Utilities documentation for information about using the Export utility on the current database.

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

  2. Install the new Oracle Database software. Installation is operating system specific. Installation steps for Oracle Database are covered in your operating system-specific Oracle documentation.

  3. If the new database has the same name as the current database, then shut down the current database before creating the new database.

  4. Create the new database.

    See Also:

    Oracle Database Administrator's Guide for information about creating a database
  5. Start SQL*Plus in the new Oracle Database environment.

  6. Connect to the database instance as a user with SYSDBA privileges.

  7. Start an Oracle Database instance using STARTUP.

  8. Optionally, you can change the storage parameters from the source database.

    You can pre-create tablespaces, users, and tables in the new 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. When items have been pre-created, specify one of the following options:

    • TABLE_EXISTS_ACTION=APPEND for Data Pump Import

    • IGNORE=Y for original Import

    Note:

    If the new database is created on the same computer as the source database, and you do not want to overwrite the source database data files, then you must pre-create the tablespaces and specify one of the following options when you import:
    • REUSE_DATAFILES=N for Data Pump Import

      Optionally, consider using the REMAP_DATAFILE, REMAP_TABLESPACE and REMAP_TABLE options so that references to the old names in the dump file set are remapped to new, non-colliding names.

    • DESTROY=N for original Import.

  9. Use the Import utility of the new database to import the objects exported from the current database. Include one of the following parameters to save the informational and error messages from the import session to a file:

    • The LOGFILE parameter for Data Pump Import

    • The LOG parameter for original Import

    See Also:

    Oracle 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:

    Oracle Database Utilities and the Oracle Database README.doc file for error handling information.
  11. Use further Import scenarios (see Oracle Database Utilities) or SQL scripts that create the database's objects to clean up incomplete imports (or possibly to start an entirely new import).

    Note:

    If a Data Pump Export or Import job encounters a fatal error, then the job can be restarted after the condition inducing the failure is corrected. The job then continues automatically from the point of failure.
  12. If changes are made to the current database after the export, then make sure those changes are propagated to the new database prior to making it available to users. See Step 1 for more information.

  13. Complete the procedures described in Chapter 4, "After Upgrading to the New Release".