To facilitate upgrading Oracle Database, you can use the Export and Import utilities in Oracle Data Pump to migrate data from one database to another. Oracle Data Pump provides high performance Export (expdp) and Import (impdp) utilities.
This chapter contains the following topics:
See Also:
Oracle Database Utilities for detailed information about Data Pump and the Export and Import utilitiesOracle provides Data Pump Export and Import to migrate, or move, data from one Oracle database to another. This is useful after upgrading Oracle Database.
Data Pump offers the following benefits:
Supports filtering the metadata that is exported and imported based upon objects and object types, using INCLUDE and EXCLUDE parameters.
Supports different modes for unloading/loading portions of the database including: full database mode, schema mode, table mode, tablespace mode, and transportable tablespace mode. (See the topics about Data Pump export modes and Data Pump import modes in Oracle Database Utilities.)
Enables you to specify how partitioned tables should be handled during import operations, using the PARTITION_OPTIONS parameter.
Provides support for the full range of data types.
See Also:
Oracle Database Utilities for an overview of Data Pump Export and Import
"The Export/Import Method for Migrating Data When Upgrading Oracle Database" for information on when to use this method
You can take advantage of Oracle Data Pump to export data from the source database before you install the new Oracle Database software, and then import the data into the target upgraded database.
To use Oracle Data Pump with the upgrade procedures:
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, the current database must not be available for updates during and after the export. If the current database is available to users for updates after the export, then, before 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.
Install the new Oracle Database software. Installation steps for Oracle Database are covered in your operating system-specific Oracle documentation. Refer to Oracle Database Installation Guide for your operating system.
If the new database has the same name as the current database, then shut down the current database before creating the new database.
Create the new database.
See Also:
Oracle Database Administrator's Guide for information about creating a databaseStart SQL*Plus in the new Oracle Database environment.
Connect to the database instance as a user with SYSDBA privileges.
Start an Oracle Database instance using STARTUP.
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 the TABLE_EXISTS_ACTION=APPEND option for Data Pump 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 an option 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.
Use the Import utility of the new database to import the objects exported from the current database. Include a parameter to save the informational messages and error messages from the import session to a file as follows:
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.After the import, check the import log file for information about the imports of specific objects that completed successfully. If there were failures, check for information about any objects that failed.
Use further Import scenarios as described in Oracle Database Utilities, or use 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 an unrecoverable error, then the job can be restarted after the condition inducing the failure is corrected. The job continues automatically from the point of failure.If changes are made to the current database after the export, then make sure those changes are propagated to the new database before making it available to users. Refer to step 1 in this procedure.
Complete the procedures described in Chapter 4, "Post-Upgrade Tasks for Oracle Database".
You can use the Data Pump Import utility with a database link to perform a full database import from a source database to a destination database without intermediate dump files. This method is an alternative to the procedure in "Migrating Data With Data Pump When Upgrading Oracle Database"
Note:
In order to avoid interoperability errors, ensure that you have applied the appropriate patchset to the database being upgraded. See My Oracle Support athttp://support.oracle.com to obtain the latest patchsets.
You may also refer to support note ID 4511371.8, which discusses ORA-6544 and ORA-4052 errors.
To use a network link with the Data Pump Import utility, impdp:
Ensure that the exporting user at the source database has the DATAPUMP_EXP_FULL_DATABASE role.
This user must be specified when you create the database link.
Ensure that the importing user at the destination database has the DATAPUMP_IMP_FULL_DATABASE role.
Create and test a database link between the source and destination databases.
Run the following command, where import_user is the username for the importing user, and db_link is the name of the database link owned by the exporting user:
IMPDP import_user NETWORK_LINK=db_link FULL=Y;
A log file for the import operation writes to the DATA_PUMP_DIR directory. You can discover the location of this directory by running the following command:
SQL> select * from dba_directories where DIRECTORY_NAME like 'DATA_PUMP_DIR';
Note:
XML objects are not exported from the source database.See Also:
Note 466181.1 on My Oracle Support athttp://support.oracle.com for more information on Data Pump Imports using a database linkNote:
The import operation re-creates users on the new destination server, and the creation date fordba_users shows the actual import date. The expiration date is updated to be creation_date + password_life_time. The parameters for dba_users on the new server are different than the dba_users parameters on the source server.When using Data Pump with the downgrade process, the Oracle Database release to which you downgrade can be one release earlier, at the most.
To obtain a downward compatible dump file with Data Pump Export:
Use the Data Pump Export utility in the current release, and set the VERSION parameter to the release of the earlier target to which you are downgrading.
Data Pump Import cannot read dump file sets created by a database release that is later than the current database release, unless those dump file sets were created with the VERSION parameter set to the release number of the target database. Therefore, the best way to perform a downgrade is to use Data Pump Export with the VERSION parameter set to the release number of the target database to which you are downgrading.