合 Oracle数据泵的兼容性 Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)
Tags: Oracle数据迁移数据泵expdpimpdp兼容性
In this Document
APPLIES TO:
Enterprise Manager for Oracle Database - Version 10.1.0.2 and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
PURPOSE
This article describes issues related to the compatibility of the different versions of the Oracle Export Data Pump and Import Data Pump utilities.
SCOPE
The article is intended for users of the Oracle10g and Oracle11g databases who wish to use Export Data Pump (expdp) and Import Data Pump (impdp) to export data from an Oracle database release x and import this data into an Oracle database release y. The article gives information how to create an Export Data Pump dumpfile that can be imported into a higher or lower release database, and how the compatibility applies when Data Pump is used over a database link.
For the compatibility of the original export and import clients (exp resp. imp), see:
Note:132904.1 "Compatibility Matrix for Export & Import Between Different Oracle Versions"
For the transportable tablespaces export/import, see:
Note:291024.1 "Compatibility and New Features when Transporting Tablespaces with Export and Import"
In the text below, the following terminology is used:
- Source database = the database where the data is exported from.
- Target database = the database where the data is imported into.
- Database compatibility level = the value of init.ora/spfile parameter COMPATIBLE.
- Data Pump client version = the release version of the Data Pump client (software version).
- Database version = the release version (the first four digits) of the ORACLE_HOME (= version of database dictionary).
DETAILS
1. Summary
The following are the most important guidelines regarding Data Pump compatibility:
The compatibility level of the Data Pump dumpfile set is determined by the compatibility level of the source database.
Use the Export Data Pump client (expdp) that matches the version of the source database (up to one major version lower expdp client can be used, but this is not recommended).
Use the Import Data Pump client (impdp) that matches the version of the target database (up to one major version lower impdp client can be used, but this is not recommended).
Use the Export Data Pump parameter VERSION in case the target database has a lower compatibility level than the source database.
Transferring data over a database link is supported even if the compatibility level of the (remote) source database differs from the (local) connected database (up to one major version difference is supported).
Import Data Pump can always read Export Data Pump dumpfile sets created by older versions of the database.
Export dumpfiles created with the Export Data Pump client (expdp) cannot be read by the original Import client (imp).
Export dumpfiles created with the original Export client (exp) cannot be read by the Import Data Pump client (impdp).
Overview of Data Pump dumpfile compatibility.
Export Use Export Data Pump parameter VERSION=...
From if dumpfile needs to be imported into a
Source Target Database with compatibility level
Database (value of init.ora/spfile parameter COMPATIBLE):
With
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Export Use Export Data Pump parameter VERSION=... From if dumpfile needs to be imported into a Source Target Database with compatibility level Database (value of init.ora/spfile parameter COMPATIBLE): With COMPATIBLE 10.1.0.x.y 10.2.0.x.y 11.1.0.x.y 11.2.0.x.y 12.1.0.x.y 12.2.0.x.y 18.x.y.z 19.x.y.z ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 10.1.0.x.y - - - - - - - - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 10.2.0.x.y VERSION=10.1 - - - - - - - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 11.1.0.x.y VERSION=10.1 VERSION=10.2 - - - - - - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 11.2.0.x.y VERSION=10.1 VERSION=10.2 VERSION=11.1 - - - - - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 12.1.0.x.y VERSION=10.1 VERSION=10.2 VERSION=11.1 VERSION=11.2 - - - - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 12.2.0.x.y VERSION=10.1 VERSION=10.2 VERSION=11.1 VERSION=11.2 VERSION=12.1 - - - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 18.x.y.z VERSION=10.1 VERSION=10.2 VERSION=11.1 VERSION=11.2 VERSION=12.1 VERSION=12.2 - - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ 19.x.y.z VERSION=10.1 VERSION=10.2 VERSION=11.1 VERSION=11.2 VERSION=12.1 VERSION=12.2 VERSION=18.x - ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------ |
Overview of Data Pump client/server compatibility.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Data Pump client compatibility. =============================== expdp and Connecting to Database version impdp client 10gR1 10gR2 11gR1 11gR2 12cR1 12cR2 18c 19c version 10.1.0.x 10.2.0.x 11.1.0.x 11.2.0.x 12.1.0.x 12.2.0.x 18.x.y.z 19.x.y.z ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 10.1.0.x supported supported supported supported no no no no 10.2.0.x no supported supported supported supported no no no 11.1.0.x no supported supported supported supported no no no 11.2.0.x no no no supported supported supported supported supported 12.1.0.x no no no no supported supported supported supported 12.2.0.x no no no no no supported supported supported 18.x.y.z no no no no no supported supported supported 19.x.y.z no no no no no no no supported |
For details about generic interoperability between Oracle client and server versions, see also:
Note:207303.1 "Client / Server / Interoperability Support Between Different Oracle Versions"
Overview of Data Pump dumpfile set file versions.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Data Pump file version. ======================= Version Written by Can be imported into Target: Data Pump database with 10gR1 10gR2 11gR1 11gR2 12cR1 12cR2 18c 19c Dumpfile Set compatibility 10.1.0.x 10.2.0.x 11.1.0.x 11.2.0.x 12.1.0.x 12.2.0.x 18.x.y.z 19.x.y.z ------------ --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0.1 10.1.x supported supported supported supported supported supported supported supported 1.1 10.2.x no supported supported supported supported supported supported supported 2.1 11.1.x no no supported supported supported supported supported supported 3.1 11.2.x no no no supported supported supported supported supported 4.1 12.1.x no no no no supported supported supported supported 5.1 12.2.x no no no no no supported supported supported 5.1 18.x.y no no no no no no supported supported 5.1 19.x.y no no no no no no no supported |
Overview of Data Pump client/feature compatibility.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | Data Pump New Features. ======================= Version New Feature Remark: --------- --------------------------------- ---------------------------------------------- 10.1.0.1 Data Pump Technology Introduction of high-speed data movement. --------- 10.2.0.1 COMPRESSION Compress metadata in export dumpfile. 10.2.0.1 ENCRYPTION_PASSWORD Allows encrypted column data in dumpfile. 10.2.0.1 SAMPLE Specify a percentage of data to be unloaded. 10.2.0.1 TRANSFORM Change DDL for OID's and space allocation. 10.2.0.1 VERSION Create file compatible with earlier release. --------- 11.1.0.1 COMPRESSION Both data and metadata can be compressed. 11.1.0.1 DATA_OPTIONS (expdp) Specify handling of XMLType columns. 11.1.0.1 DATA_OPTIONS (impdp) Specify handling of constraint violations. 11.1.0.1 ENCRYPTION Both data and metadata can be encrypted. 11.1.0.1 ENCRYPTION_ALGORITHM Specify a specific encryption algorithm. 11.1.0.1 ENCRYPTION_MODE Specify the type of security to be used. 11.1.0.1 PARTITION_OPTIONS Specify how to handle partitioned tables. 11.1.0.1 REMAP_DATA Change column values based on a function. 11.1.0.1 REMAP_TABLE Rename tables during an import operation. 11.1.0.1 REUSE_DUMPFILES Option to overwrite existing dumpfiles. 11.1.0.1 TRANSPORTABLE Transfer table data by copying datafiles. --------- 11.2.0.1 Legacy mode: accept exp and imp parameters. 11.2.0.1 ABORT_STEP Stop job after initializing to query master. 11.2.0.1 ACCESS_METHOD Specify a particular method to (un)load data. 11.2.0.1 CLUSTER Control whether workers use all RAC instances. 11.2.0.1 DATA_OPTIONS (impdp) Specify to disable the APPEND hint. 11.2.0.1 KEEP_MASTER Specify whether to retain master table. 11.2.0.1 MASTER_ONLY Only import the master table. 11.2.0.1 METRICS Report additional information in logfile. 11.2.0.1 SERVICE_NAME Use with CLUSTER to specify a service name. 11.2.0.1 SOURCE_EDITION (expdp) Specify the edition from which to exp objects. 11.2.0.1 TABLES Now specify tables in multiple schemas. 11.2.0.1 TABLES Now specify % for multiple tables and part. 11.2.0.1 TARGET_EDITION (impdp) Specify the edition from which to exp objects. 11.2.0.2 Default first segment now 8M for part. table. 11.2.0.2 TRANSFORM Specify how to handle SEGMENT CREATION. --------- 12.1.0.1 Support for CDB and Pluggable db's (PDB). 12.1.0.1 COMPRESSION_ALGORITHM Specify the algorithm when compressing data. 12.1.0.1 ENCRYPTION_PWD_PROMPT Specify whether Data to prompt for password. 12.1.0.1 FULL Can now be used together with TRANSPORTABLE. 12.1.0.1 LOGTIME Provide timestamp for messages in the logfile. 12.1.0.1 TRANSFORM Specify to disable logging during import. 12.1.0.1 TRANSFORM Specify to change the LOB storing on import. 12.1.0.1 TRANSFORM Specify to change table compression type. 12.1.0.1 VIEWS_AS_TABLES Export views as tables. --------- 12.2.0.1 12.2.0.1 REMAP_DIRECTORY Let you remap directories when you move databases between platforms. 12.2.0.1 TRUST_EXISTING_TABLE_PARTITIONS Enable data from multiple partitions to be loaded in parallel into a pre-existing table. This is a flag on Data Pump Import DATA_OPTIONS 12.2.0.1 VALIDATE_TABLE_DATA Verify the format number and date data types in table data columns. This is a flag on Data Pump Import DATA_OPTIONS 12.2.0.1 ENABLE_NETWORK_COMPRESSION Tell Data Pump to compress data before sending it over the network. This is a flag on DATA_OPTIONS parameter. 12.2.0.1 GROUP_PARTITION_TABLE_DATA Enable data for all partitions to be loaded at once and in parallel. This is a flag on the Data Pump Export DATA_OPTIONS parameter. 12.2.0.1 VERIFY_STREAM_FORMAT Validate the format of a data stream before it is written to the Data Pump dump file. This is a flag on the Data Pump Export DATA_OPTIONS parameter. --------- 18.1.X.X CONTINUE_LOAD_ON_FORMAT_ERROR This is a new value for the DATA_OPTIONS parameter for impdp. When it is set, Data Pump jumps ahead and continue loading from the next granule when an inconsistency is found. --------- 19c Use Document 2457955.1 19c DataPump New Features are described in this document. |
2. Introduction
2.1. Data Pump.
With Oracle10g Release 1 (10.1.0.x) we have introduced the new Export DataPump (expdp) and Import DataPump (impdp) utilities. These utilities have a better performance and increased flexibility when compared to the original export (exp) and import (imp) clients. All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string.
With the Oracle Export Data Pump and Import Data Pump utilities you can transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. With Export Data Pump the object definitions and table data can be extracted from an Oracle database and stored in an Oracle binary-format export Data Pump dumpfile located on the server.
The dumpfile set can then be transferred using FTP in binary mode or physically transported to a different site. With the Import Data Pump client the object definitions and table data can be read from the dumpfile set and then inserted into the Oracle database.
2.2. How to determine the database compatibility level ?
To determine the compatibility level of the database, run the following in SQL*Plus:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | col value for a20 col description for a49 select * from database_compatible_level; col value clear col description clear -- Sample output: VALUE DESCRIPTION -------------------- ------------------------------------------------- 12.1.0.0.0 Database will be completely compatible with this software version -- or: show parameter compatible NAME TYPE VALUE --------------------- ----------- ------------- compatible string 12.1.0.0.0 |
When checking for compatibility, Data Pump examins the major database release number and the database maintenance release number (i.e.: 11.1 in the example output above).
For details about version numbers, see also:
Note:39691.1 "VERSION NUMBER - Oracle version numbers explained"
2.3. How to determine the software version of the database and the Data Pump client ?
The software version of the Export Data Pump and Import Data Pump clients can be obtained by invoking the utilities with the parameter HELP=Y. The banner of the help page shows the version of the utility:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | SELECT * FROM v$version; BANNER CON_ID ----------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 -- And for the Data Pump clients: %expdp HELP=Y Export: Release 12.1.0.1.0 - Production on Wed May 28 09:58:07 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. ... (other output) and: %impdp HELP=Y Import: Release 12.1.0.1.0 - Production on Wed May 28 09:59:53 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. ... (other output) |
For details, see also:
Note:175627.1 "Export-Import iSR - How to Find the Database and Export/Import Version"
2.4. How to obtain the version of an export Data Pump dumpfile ?
Every export dumpfile starts with a header (usually 4 kb in size) which contains details about the dumpfile. Starting with Oracle10g Release 2 (10.2.0.1.0), this header information can be extracted from the dumpfile by calling the procedure DBMS_DATAPUMP.GET_DUMPFILE_INFO.
Possible output based on an example procedure SHOW_DUMPFILE_INFO:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | SET serveroutput on SIZE 1000000 exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_f.dmp') ---------------------------------------------------------------------------- Purpose..: Obtain details about export dumpfile. Version: 18-DEC-2013 Required.: RDBMS version: 10.2.0.1.0 or higher . Export dumpfile version: 7.3.4.0.0 or higher . Export Data Pump dumpfile version: 10.1.0.1.0 or higher Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE'); Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp') ---------------------------------------------------------------------------- Filename.: ftest.dmp Directory: dpdir Disk Path: <DIRECTORY_PATH>/expdp Filetype.: 1 (Export Data Pump dumpfile) ---------------------------------------------------------------------------- ...Database Job Version..........: 12.02.00.00.00 ...Internal Dump File Version....: 5.1 ...Creation Date.................: Tue Jun 06 11:04:57 2017 ...File Number (in dump file set): 1 ...Master Present in dump file...: 1 (Yes) ...Master in how many dump files.: 1 ...Master Piece Number in file...: 1 ...Operating System of source db.: x86_64/Linux 2.4.xx ...Instance Name of source db....: db4 ...Characterset ID of source db..: 873 (AL32UTF8) ...Language Name of characterset.: AL32UTF8 ...Job Name......................: "<SCHEMA_NAME>"."SYS_EXPORT_FULL_01" ...GUID (unique job identifier)..: 51470FA2BE241FE4E0539395400A4A02 ...Block size dump file (bytes)..: 4096 ...Metadata Compressed...........: 1 (Yes) ...Data Compressed...............: 0 (No) ...Compression Algorithm.........: 3 (Basic) ...Metadata Encrypted............: 0 (No) ...Table Data Encrypted..........: 0 (No) ...Column Data Encrypted.........: 0 (No) ...Encryption Mode...............: 2 (None) ...Internal Flag Values..........: 514 ...Max Items Code (Info Items)...: 23 ---------------------------------------------------------------------------- PL/SQL procedure successfully completed. |
In the example above, the dumpfile set has the compatibility level 12.2 (12.02.00.00.00) and is using a file version format 5.1. This dumpfile set can only be imported into a database with the same or a higher compatibility level.
For details, and the example procedure SHOW_DUMPFILE_INFO which can be called to obtain the export dumpfile header details, see:
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"
3. Basic Data Pump Compatibility
3.1. Data Pump dumpfile file version versus Data Pump job version.
When an Export Data Pump dumpfile set is created, we store information in the header block of each dumpfile. Besides the version of the Data Pump job, we also store the version of the dumpfile. The version of the dumpfile determines the internal structure of the dumpfile:
- In Oracle10g Release 1 we create an Export Data Pump dumpfile with version 0.1.
- In Oracle10g Release 2 we create an Export Data Pump dumpfile with version 1.1.
- In Oracle11g Release 1 we create an Export Data Pump dumpfile with version 2.1.
- In Oracle11g Release 2 we create an Export Data Pump dumpfile with version 3.1.
- In Oracle12c Release 1 we create an Export Data Pump dumpfile with version 4.1.
- In Oracle12c Release 2 we create an Export Data Pump dumpfile with version 5.1.
- In Oracle18c we create an Export Data Pump dumpfile with version 5.1.
- In Oracle19c we create an Export Data Pump dumpfile with version 5.1.
Note that the version of Data Pump dumpfile set is used internally to keep track of Data Pump specific features that result in a change of the internal structure of the dumpfile.
It is not possible to create a dumpfile with a higher version, e.g.: a 11.2.0.4.0 source database cannot create a version 4.1 dumpfile because that dumpfile version has a structure that was introduced in Oracle12c. It is possible though to create a dumpfile with a lower version, e.g.: a 11.2.0.4.0 source database can also create a version 1.1 dumpfile by specifying the Export Data Pump parameter VERSION (see section 4.2. "Export Data Pump parameter: VERSION" below). The reason why you would do this is to make the dumpfile set compatible with the lower release target database so it can be imported.
With the VERSION parameter you can specify the Data Pump job version and indirectly control the version of the dumpfile set. E.g.: if you specify VERSION=11.2 then Data Pump will create a dumpfile set that can be imported into an Oracle11g Release 2 database (dumpfile version: 3.1). When specified, you also determine which version of the objects will be exported. Database objects or attributes that are incompatible with the specified version will not be exported.