不同版本之间的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的数据库。

EXPDP Streams AQ: enqueue blocked on low memory

有一台11.2.0.4的库最近expdp导出非常慢,总是停留在99%以后还要持续2个多小时,还有另外一台同版本的数据库也碰到了类似的情况。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Job: SYS_EXPORT_SCHEMA_01
Owner: XXX
Operation: EXPORT
Creator Privs: TRUE
GUID: 8A27A5FA09F142F9E055000000000001
Start Time:
Mode: SCHEMA
Instance:
Max Parallelism: 4
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND backup_user/******** dumpfile=
COMPRESSION ALL
State: EXECUTING
Bytes Processed: 45,234,549,312
Percent Done: 99
Current Parallelism: 4
Job Error Count: 0

当前导出的对象都是一些空的分区,查询导出任务是否在等待

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
SQL> select dw.waiting_session,
dw.holding_session,
b.serial#,
w.event,
w.program wprogram,
b.program bprogram,
w.module wmod,
b.module bmod,
LOCK_ID1
from sys.dba_waiters dw, v$session w, v$session b
where dw.waiting_session = w.sid
and dw.holding_session = b.sid
and (w.module like 'Data Pump%' or w.program like '%EXPDP%' or
w.program like '%IMPDP%')
order by dw.holding_session;

no rows selected


SQL> select sw.SID,
sw.SEQ#,
sw.EVENT,
sw.WAIT_TIME,
sw.SECONDS_IN_WAIT,
sw.STATE,
sw.P1TEXT,
sw.P1,
sw.P2TEXT,
sw.P2,
sw.P3TEXT,
sw.P3
from V$SESSION_WAIT sw, v$session s
where sw.wait_class <> 'Idle'
and sw.sid = s.sid
and (s.module like 'Data Pump%' or s.program like '%EXPDP%' or
s.program like '%IMPDP%');

SID SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE P1TEXT P1 P2TEXT P2 P3TEXT P3
---------- ---------- ---------------------------------------------------------------- ---------- --------------- ------------------- ---------- ---------- ---------- ---------- ---------- ----------
1801 31734 Streams AQ: enqueue blocked on low memory 0 1 WAITING 0 0 0
2540 3931 Streams AQ: enqueue blocked on low memory 0 1 WAITING 0 0 0

等待显示导出队列在等待与streams相关的内存,在sga中就是streams pool,查看当前streams pool的使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select component
,current_size/1024/1024 "Current MB"
,min_size/1024/1024 "Min MB"
from v$sga_dynamic_components;

COMPONENT Current MB Min MB
---------------------------------------------------------------- ---------- ----------
shared pool 3232 1472
large pool 64 64
java pool 32 32
streams pool 0 0
DEFAULT buffer cache 6624 6432
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
DEFAULT 32K buffer cache 0 0
Shared IO Pool 0 0
ASM Buffer Cache 0 0

因为这个库设置了sga_target,相关组件的内存大小分配是由oracle自动来控制的,所以可能碰上了bug。

Workaround:

  1. 重启数据库,通常能解决这个问题
  2. 修改参数,设定streams_pool_size的最小值
1
2
CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=both;
  1. 如果无法动态调整,则需要重启
1
2
3
4
CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=spfile;
SHUTDOWN IMMEDIATE
STARTUP
  1. 打补丁Patch 17365043
    或者修改隐含参数
1
alter system set "_disable_streams_pool_auto_tuning"=TRUE;

做完以后重新执行datapump任务。