转自:http://www.oracleblog.org/working-case/12c-impdp-with-error-kup-11014/

将10.2.0.5的一个大表导入到12.1.0.2的时候,
导出参数是:
[oracle10g@testdb tmp]$ cat expdp.par
userid='/ as sysdba'
DIRECTORY=DUMPDIR
dumpfile=mytable_%U.dmp
tables=schema.mytable
logfile=mytable.log
job_name=mytable
parallel=8
filesize=100M

导入参数是:
userid='/ as sysdba'
DIRECTORY=DUMPDIR
dumpfile=mytable_%U.dmp
tables=schema.mytable
logfile=mytable.log
job_name=mytable
parallel=8
content=data_only

报错KUP-11014。
ORA-31693: Table data object "SCHEMA"."MYTABLE" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11011: the following file is not valid for this load operation
KUP-11014: internal metadata in file /home/oracle12c/mytable_02.dmp is not valid
Job "SYS"."MYTABLE" completed with 1 error(s) at Thu May 19 12:55:34 2016 elapsed 0 00:10:03

同样文件导入到11g中没有报错。这是因为12c中一个Bug 20690515引起的(可以详见Doc ID 20690515.8)。

下面我对这个bug稍微解释一下:

1. 触发条件:
在导入多个dump file set(即多个dump file文件)的时候,数据泵如果使用access_method=external_table的方式进行导入(默认情况下12c的access_method值是AUTOMATIC,即自动选择是extenal_table还是direct_path。至于什么时候选择前者什么时候选择后者,可参考Doc ID552424.1),用external_table方式导入期间,会校验每个dump file的xml内容,且与第一个dump file的xml内容做对比。但导入10.x的dump file set的时候,第一个(作为参考的那个)dump file的xml格式被转换成11.1的格式,那么与后面的dump file文件做对比校验的时候,就失败了。

2. 受影响版本:
12.1.0.2

3. 修复版本:
12.2

4. 是否有patch:
有,Patch 20690515 已经存在,有基于12.1.0.2版本的linux x86-64平台,aix平台和solaris SPARC平台。目前linux平台已经下载次数200多。

5. 是否有workaround:
有workaround,设置access_method=direct_path
如果还是报错,再加上table_exists_action=replace

就我来看,这个bug触发需要满足2个条件:
1. 多个dump文件
2. access method自动走了external table,或者强制手工指定了ACCESS_METHOD=EXTERNAL_TABLE。
(3. 可能还和表的大小有关,在某环境测试时300多M的一个表故障不重现,但是增加数据到3.6G,再次测试故障重现。但是目前在mos中没有说明表大小的影响因素。)

我在我的虚拟机测试环境中用来一个500多M的表,分别导成一个大文件和6个小文件:

创建测试表,create table test_dmp as select * from dba_objects;
多次insert into test_dmp as select * from test_dmp; 直到数据大约200多万行,500多M的segment size。

然后导出成一个大文件和多个小文件。
[oracle12c@testdb2 dump_12c]$ ls -l
total 889504
-rw-r—–. 1 oracle12c oinstall 455401472 May 19 16:01 bigdump.dmp <==单个大文件
-rw-r–r–. 1 oracle12c oinstall       173 May 19 16:56 impdp2.par
-rw-r–r–. 1 oracle12c oinstall       145 May 19 17:19 impdp3.par
-rw-r–r–. 1 oracle12c oinstall       171 May 19 17:08 impdp.par
-rw-r—–. 1 oracle12c oinstall 104857600 May 19 15:52 mydump_01.dmp <==多个小文件
-rw-r—–. 1 oracle12c oinstall 104857600 May 19 15:52 mydump_02.dmp
-rw-r—–. 1 oracle12c oinstall 104857600 May 19 15:52 mydump_03.dmp
-rw-r—–. 1 oracle12c oinstall 104857600 May 19 15:52 mydump_04.dmp
-rw-r—–. 1 oracle12c oinstall  34873344 May 19 15:52 mydump_05.dmp
-rw-r—–. 1 oracle12c oinstall   1118208 May 19 15:52 mydump_06.dmp
-rw-r—–. 1 oracle12c oinstall       677 May 19 17:20 mydump.log
[oracle12c@testdb2 dump_12c]$

1. 测试导入一个大文件,且强制使用ACCESS_METHOD=EXTERNAL_TABLE,不会报错:

[oracle12c@testdb2 dump_12c]$ cat impdp2.par
userid=’/ as sysdba’
DIRECTORY=MYDIR
dumpfile=bigdump.dmp
tables=test.test_dmp
logfile=mydump.log
job_name=mydump
parallel=2
content=data_only
ACCESS_METHOD=EXTERNAL_TABLE

[oracle12c@testdb2 dump_12c]$
[oracle12c@testdb2 dump_12c]$
[oracle12c@testdb2 dump_12c]$
[oracle12c@testdb2 dump_12c]$
[oracle12c@testdb2 dump_12c]$ impdp parfile=impdp2.par

Import: Release 12.1.0.2.0 – Production on Thu May 19 16:57:08 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table “SYS”.”MYDUMP” successfully loaded/unloaded
Starting “SYS”.”MYDUMP”:  /******** AS SYSDBA parfile=impdp2.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST”.”TEST_DMP”                           434.2 MB 5132800 rows
Job “SYS”.”MYDUMP” successfully completed at Thu May 19 16:57:26 2016 elapsed 0 00:00:17

[oracle12c@testdb2 dump_12c]$

2. 测试导入多个文件,且强制使用ACCESS_METHOD=EXTERNAL_TABLE,就报错了:

[oracle12c@testdb2 dump_12c]$ cat impdp.par
userid=’/ as sysdba’
DIRECTORY=MYDIR
dumpfile=mydump_%U.dmp
tables=test.test_dmp
logfile=mydump.log
job_name=mydump
parallel=2
content=data_only
ACCESS_METHOD=EXTERNAL_TABLE

[oracle12c@testdb2 dump_12c]$
[oracle12c@testdb2 dump_12c]$ impdp parfile=impdp.par

Import: Release 12.1.0.2.0 – Production on Thu May 19 16:55:30 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table “SYS”.”MYDUMP” successfully loaded/unloaded
Starting “SYS”.”MYDUMP”:  /******** AS SYSDBA parfile=impdp.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object “TEST”.”TEST_DMP” failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11011: the following file is not valid for this load operation
KUP-11014: internal metadata in file /tmp/dump_12c/mydump_02.dmp is not valid
Job “SYS”.”MYDUMP” completed with 1 error(s) at Thu May 19 16:55:34 2016 elapsed 0 00:00:03

[oracle12c@testdb2 dump_12c]$

3. 测试导入多个文件,且强制使用ACCESS_METHOD=DIRECT_PATH,也不会报错:

[oracle12c@testdb2 dump_12c]$ cat impdp.par
userid=’/ as sysdba’
DIRECTORY=MYDIR
dumpfile=mydump_%U.dmp
tables=test.test_dmp
logfile=mydump.log
job_name=mydump
parallel=2
content=data_only
ACCESS_METHOD=DIRECT_PATH

[oracle12c@testdb2 dump_12c]$ impdp parfile=impdp.par

Import: Release 12.1.0.2.0 – Production on Thu May 19 17:08:12 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table “SYS”.”MYDUMP” successfully loaded/unloaded
Starting “SYS”.”MYDUMP”:  /******** AS SYSDBA parfile=impdp.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST”.”TEST_DMP”                           434.2 MB 5132800 rows
Job “SYS”.”MYDUMP” successfully completed at Thu May 19 17:08:31 2016 elapsed 0 00:00:17

[oracle12c@testdb2 dump_12c]$

注:以上测试是在从10.2.0.5导出,导入到12.1.0.2;如果从11.2导入到12.1.0.2,也不会有这个问题。考虑到可能有不少用户会从10g升级到12c,建议打上这个bug的补丁。

,
Trackback

no comment untill now

Add your comment now

切换到手机版