Oracle 12c data pump import error KUP-11014 error solution

When a large table of 10.2.0.5 is imported to 12.1.0.2,

The export parameters are:

[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 = 100 M

The import parameters are:

Userid = '/as sysdba'
DIRECTORY = DUMPDIR
Dumpfile = mytable _ % U. dmp
Tables = schema. mytable
Logfile = mytable. log
Job_name = mytable
Parallel = 8
Content = data_only

Error 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
The ORA-29400: data cartridge error
The 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

If the same file is imported to 11g, no error is reported. This is caused by a Bug 20690515 in 12c (for details, see Doc ID 20690515.8 ).

Below I will explain this bug a little:

1. Trigger conditions:

When importing multiple dump file sets (that is, multiple dump file files), if the data pump imports data using access_method = external_table (the value of access_method in 12c is AUTOMATIC by default, that is, whether it is extenal_table or direct_path is automatically selected. When to select the former and when to select the latter, refer to Doc ID552424.1). During the import using external_table, the xml content of each dump file is verified, and compare it with the xml content of the first dump file. But import 10. when the dump file set of x is used, the xml format of the first (for reference) dump file is converted to 11.1, the verification fails when compared with the subsequent dump file.

2. Affected versions:

12.1.0.2

3. Fixed version:
12.2

4. Whether patch exists:

Yes, Patch 20690515 already exists, there is a linux x86-64 platform based on version 12.1.0.2, aix platform and solaris iSCSI platform. At present, the linux platform has downloaded more than 200 times.

5. Check whether workaround exists:

With workaround, set access_method = direct_path
If an error is still reported, add table_exists_action = replace.

In my opinion, the trigger of this bug must meet two conditions:

1. Multiple dump files
2. The access method automatically follows the external table, or the ACCESS_METHOD = EXTERNAL_TABLE is manually specified.
(3. It may also be related to the table size. During an environment test, the fault of a table over 300 MB is not reproduced, but the data is increased to 3.6 GB, and the fault reproduction is tested again. However, the factors affecting the table size are not described in mos .)

In my virtual machine testing environment, I used a table of over 500 MB to export a large file and six small files:

Create a Test table, create table test_dmp as select * from dba_objects;
Insert into test_dmp as select * from test_dmp multiple times until there are about more than 2 million rows of data and the segment size of more than 500 MB.
 
Then export the data into a large file and multiple small files.

[Oracle12c @ testdb2 dump_12c] $ ls-l
Total 889504
-Rw-r -----. 1 oracle12c oinstall 455401472 May 19 bigdump. dmp <= a single large file
-Rw-r --. 1 oracle12c oinstall 173 May 19 impdp2.par
-Rw-r --. 1 oracle12c oinstall 145 May 19 impdp3.par
-Rw-r --. 1 oracle12c oinstall 171 May 19 impdp. par
-Rw-r -----. 1 oracle12c oinstall 104857600 May 19 mydump_01.dmp <= multiple small files
-Rw-r -----. 1 oracle12c oinstall 104857600 May 19 mydump_02.dmp
-Rw-r -----. 1 oracle12c oinstall 104857600 May 19 mydump_03.dmp
-Rw-r -----. 1 oracle12c oinstall 104857600 May 19 mydump_04.dmp
-Rw-r -----. 1 oracle12c oinstall 34873344 May 19 mydump_05.dmp
-Rw-r -----. 1 oracle12c oinstall 1118208 May 19 mydump_06.dmp
-Rw-r -----. 1 oracle12c oinstall 677 May 19 mydump. log
[Oracle12c @ testdb2 dump_12c] $

1. Test the import of a large file and use ACCESS_METHOD = EXTERNAL_TABLE forcibly. No error is returned:

[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,201 4, 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. Test the import of multiple files and forcibly use ACCESS_METHOD = EXTERNAL_TABLE, an error is returned:

[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,201 4, 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
The ORA-29400: data cartridge error
The 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. Test the import of multiple files and use ACCESS_METHOD = DIRECT_PATH forcibly. No error is returned:

[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,201 4, 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] $

Note: the above test is to export data from 10.2.0.5 to 12.1.0.2. If the data is imported from 11.2 to 12.1.0.2, this problem does not occur. Considering that many users may upgrade from 10 GB to 12c, we recommend that you install this bug.