不同版本之间的EXPDP/IMPDP

故障现象

将一个18c版本的dmp导入到11.2.0.4当中时出现报错

1
2
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/u01/schema_dump.dmp"

这是由于高版本导出的dmp文件在低版本数据库当中无法识别。

解决办法：

在导出语句里加上version=11.2即可

1
2
Example
expdp scott/tiger@orcl directory=EXPIMP schemas=scott Version=11.2 dumpfile=Exp_Scott.dmp logfile=Exp_Scott.log

跨版本DATAPUMP

兼容性

依据数据泵的兼容性有一些准则：

  1. 数据泵dmp文件的兼容级别是由源数据库的兼容级别决定的
  2. 使用与源数据库同版本的expdp客户端
  3. 使用与目标数据库同版本的impdp客户端
  4. 当目标库的compatibility级别比源库低时，使用expdp的VERSION参数
  5. 即使两边兼容级别不一致，也可以基于database link的数据传输
  6. impdp总是可以读取从更低版本库中导出的dmp文件
  7. imp只能读取exp的dmp，impdp只能读取expdp的dmp

Version

数据泵可以用来不同版本数据库之间的数据迁移，通常通过expdp当中的version参数来完成，带上这个参数后就会生成与指定版本兼容的数据dmp集。

1
VERSION=[COMPATIBLE | LATEST | version_string]

version总共有三种值

  • COMPATIBLE - 这是默认值，元数据的版本与数据库初始化参数COMPATIBLE相对应，这个参数必须被设成9.2或更新
  • LATEST - 元数据的版本和生成的SQL DDL对于数据库的发行版本，不用管兼容参数如何
  • version_string - 指定的数据库版本

如果数据库里的对象或者某些特性与指定的version参数有冲突，则冲突的对象不会被导出。比如12.2中表名的长度限制要比11.2中大，所以对于version=11.2的情况，12.2中对象名称过长的不会被导出。

数据泵的dmp兼容性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
源数据库兼容参数		目标库兼容参数

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

数据泵client/server兼容性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Data Pump client compatibility.
===============================

     			连接数据库版本
数据泵客户端版本     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

数据泵dmp文件版本

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

数据泵特性

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.

介绍

确定数据库的兼容版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
col value for a20
col description for a49
select * from database_compatible_level;

VALUE		     DESCRIPTION
-------------------- -------------------------------------------------
18.0.0		     Database will be completely compatible with this
		     software version
		     
-- 或者：
sys@> show parameter compatible

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
compatible			     string	 18.0.0
noncdb_compatible		     boolean	 FALSE

检查数据库和数据泵的版本

1
2
3
4
5
6
7
8
9
10
11
12
sys@> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

-- client版本
[oracle@]$ expdp HELP=Y

Export: Release 18.0.0.0.0 
Version 18.5.0.0.0

获取dmp文件的兼容版本

每个生成的dmp文件都会有一个头部信息（通常是4kb大小）包含了这个dmp文件的详细信息，从10.2版本开始，可以通过DBMS_DATAPUMP.GET_DUMPFILE_INFO过程来获取这些详细信息，但是这个过程生成的信息太过繁杂，为了便于阅读，通过一个自定义的存储过程SHOW_DUMPFILE_INFO.sql来调用然后输出

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
SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'xx', p_file=> 'xx.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.: xx.dmp
Directory: xx
Disk Path: /backup/xxxx
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 18.00.00.00.00
...Internal Dump File Version....: 5.1
...Creation Date.................: Sun ---
...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....: ------
...Characterset ID of source db..: 852 (ZHS16GBK)
...Language Name of characterset.: ZHS16GBK
...Job Name......................: "SYSTEM"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: A28977F60899EE30E053D326680AD984
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 1 (Yes)
...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..........: 518
...Max Items Code (Info Items)...: 24
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

根据上面得到的结果可以看出这个生成的文件兼容参数为18.0（18.00.00.00.00），内部文件版本为5.1，这个文件只能导入到兼容版本高于或等于18.0的数据库。

Oracle
#datapump #ora-
不同版本之间的EXPDP/IMPDP
https://www.xbdba.com/2020/04/09/datapump-compatibility/
作者
xbdba
发布于
2020年4月9日
许可协议