Data Pump expdp/impdp数据泵版本兼容性

可以通过expdp/impdp指定versions参数(要导出的对象版本。有效的关键字值为: [COMPATIBLE], LATEST 或任何有效的数据库版本。)来保证版本兼容性,对于不兼容的对象会出现ORA-39139错误。

 

 

C:\Users\xiangbli>expdp dumpfile=DATA_PUMP_DIR:a1.dmp full=y version=10.2

Export: Release 12.1.0.1.0 - Production on 星期五 8月 9 16:25:05 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

用户名: / as sysdba

连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
启动 "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA dumpfile=DATA_PUMP_DIR:a1.dmp full=y version=10.2
正在使用 BLOCKS 方法进行估计...
处理对象类型 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39139: 在版本 10.2 中, 数据泵不支持 XMLType 对象。将跳过 TABLE_DATA:"OE"."WAREHOUSES"。
ORA-39139: 在版本 10.2 中, 数据泵不支持 XMLType 对象。将跳过 TABLE_DATA:"OE"."PURCHASEORDER"。

 

 

 

更多可以参考如下文档:

 

 

I had to do short test of “backward compatibility” for Oracle Data Pump export/import as potential rollback scenario for particular cross-platform/cross-version migration project. In this particular case, I was verifying 11.2.0.2.0->10.2.0.4.0 import.
It makes sense to start with checking [support] documentation first.. (or at least get back to this stage after first failure) :-)
Data pump compatibility matrix => [ID 553337.1] Export/Import DataPump Parameter VERSION – Compatibility of Data Pump Between Different Oracle Versions:
Data Pump file version.
=======================
     Version      Written by     Can be imported into Target: 
   Data Pump   database with      10gR1      10gR2      11gR1      11gR2 
Dumpfile Set   compatibility   10.1.0.x   10.2.0.x   11.1.0.x   11.2.0.x
------------ --------------- ---------- ---------- ---------- ----------
         0.1          10.1.x  supported  supported  supported  supported
         1.1          10.2.x         no  supported  supported  supported
         2.1          11.1.x         no         no  supported  supported
         3.1          11.2.x         no         no         no  supported
It all worked as expected after specifying VERSION=10.2 compatibility parameter (this data pump job combines both export and import in one step with transfer over network/db link):
impdp  schemas=<schema_name> network_link=<db_link_name> directory=<directory_name> logfile=<logfile_name> EXCLUDE=STATISTICS REMAP_TABLESPACE=<remap_data_tablespace_clause> REMAP_TABLESPACE=<remap_default_user_temp_tablespace_specification> VERSION=10.2
But it also made me wonder what is the impact of VERSION parameter on the format/content of the data pump dump file, and how to verify the “dumpfile versions” (0.1, 1.1, 2.1, 3.1)..
So I ran additional small test case based on Data Pump export (metadata only, running from the source 11g R2 server, with and without the VERSION parameter specified):

== test version of Data Pump dumpfile
== How to Gather the Header Information and the Content of an Export Dumpfile? [ID 462488.1]
== 11 g format ==
expdp  schemas=<schema_name> CONTENT=METADATA_ONLY directory=<directory_name> DUMPFILE=LT_11g.dmp logfile=exp_LT_11g.log
SQL> SET serveroutput on SIZE 1000000
SQL> BEGIN show_dumpfile_info(p_dir=> '<directory_name>', p_file=> 'LT_11g.dmp'); END;
2 /
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 19-MAR-2008
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.: LT_11g.dmp
Directory: DP_DIR
Disk Path: /db_dump
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...File Version....: 3.1 (Oracle11g Release 2: 11.2.0.x)
...Master Present..: 1 (Yes)
...GUID............: B422823D92DE048AE04048A5250F157A
...File Number.....: 1
...Characterset ID.: 873 (AL32UTF8)
...Creation Date...: Thu Dec 15 14:32:36 2011
...Flags...........: 2
...Job Name........: ""."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx
...Instance........: 
...Language........: AL32UTF8
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Column Encrypted: 0 (No)
...Encrypt.pwd. mod: 2 (None)
...Master Piece Cnt: 1
...Master Piece Num: 1
...Job Version.....: 11.02.00.00.00
...Max Items Code..: 22
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
== 10 g format exported from 11g R2 ==
expdp  schemas=<schema_name> CONTENT=METADATA_ONLY directory=<directory_name> DUMPFILE=LT_10g.dmp logfile=exp_LT_10g.log VERSION=10.2
SQL> SET serveroutput on SIZE 1000000
SQL> BEGIN show_dumpfile_info(p_dir=> '<directory_name>', p_file=> 'LT_10g.dmp'); END;
2 /
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 19-MAR-2008
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.: LT_10g.dmp
Directory: DP_DIR
Disk Path: /db_dump
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...File Version....: 1.1 (Oracle10g Release 2: 10.2.0.x)
...Master Present..: 1 (Yes)
...GUID............: B4228BA839A84A30E04048A5250F19AE
...File Number.....: 1
...Characterset ID.: 873 (AL32UTF8)
...Creation Date...: Thu Dec 15 14:35:12 2011
...Flags...........: 2
...Job Name........: ""."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx
...Language........: AL32UTF8
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Column Encrypted: 0 (No)
...Encrypt.pwd. mod: 2 (None)
...Job Version.....: 10.02.00.00.00
...Max Items Code..: 22

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *