RAC 下expdp impdp 并行 parallel FK

1. dump 文件非共享下的并行

Customer receives the following errors:

ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "<dumpfile name and path>" for write
ORA-19505: failed to identify file "<dumpfile name and path>"
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

Note:
It is possible for this to occur on other operating systems since it is a mount point. The OS specific errors may therefore be different.
 

CHANGES

CAUSE

The problem occurs when Datapump Export is being performed on a multi-node RAC where the dumpfile destination is not shared to all nodes for access.  Since multiple nodes will be running the Datapump job, ALL nodes must have access to the mount point where the dump file will be written.
 
The issue is addressed in the following bug report which was closed with status 'Not a Bug':
Bug 11677316 - DATA PUMP UNABLE TO OPEN DUMP FILE ORA-31617 ORA-19505 ORA-27037
 

SOLUTION

1. Share/mount the dumpfile destination with all RAC nodes performing the expdp

- OR -

2. Use CLUSTER=N during Datapump so it will only run on the node which has the mount point and permissions to write to it.

2. FK imp默认没有先后顺序，需要手动disable FK

Errors like the following are reported in the DataPump import log:

ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-2291: integrity constraint ("<SCHEMA_NAME>"."<FK_CONSTRAINT_NAME>") violated - parent key not found

The issue can be reproduced with the following test case:

-- create tables (schema <SCHEMA_NAME>)

CREATE TABLE DEPT
(
   DEPTNO    NUMBER(2)    CONSTRAINT PK_DEPT PRIMARY KEY,
   DNAME     VARCHAR2(14),
   LOC       VARCHAR2(13)
);

CREATE TABLE EMP
(
   EMPNO     NUMBER(4)    CONSTRAINT PK_EMP PRIMARY KEY,
   ENAME     VARCHAR2(10),
   JOB       VARCHAR2(9),
   MGR       NUMBER(4),
   HIREDATE  DATE,
   SAL       NUMBER(7,2),
   COMM      NUMBER(7,2),
   DEPTNO    NUMBER(2)    CONSTRAINT FK_DEPTNO REFERENCES fDEPT
);

-- run the import

#> impdp dumpfile=const.dmp logfile=constimp.log REMAP_SCHEMA=<SOURCE_SCHEMA>:<TARGET_SCHEMA> TABLE_EXISTS_ACTION= APPEND

You may receive errors like:

ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-2291: integrity constraint ("<SCHEMA_NAME>"."<FK_CONSTRAINT_NAME>") violated - parent key not found
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>"          5.656 KB   4   rows imported

CHANGES

CAUSE

This issue is documented in
Bug 6242277 - DATA PUMP IMPORTS FIRST CHILD ROWS AND THEN PARENT ROWS
closed with status 'Not a Bug'.
 

SOLUTION

This is an expected behavior already documented in Oracle utilities guide:
Data Pump Import

Please also refer to Oracle® Database Utilities 11g Release 2 (11.2)
Part Number E22490-04

To implement the solution, please use any of the following alternatives:

• If you have data that must be loaded but may cause constraint violations, consider disabling the constraints, loading the data, and then deleting the problem rows before reenabling the constraints
  
  - OR -
   
• Import the tables separately

3  PARTITION_OPTIONS=MERGE分区表变non 分区表

How to convert a partitioned table to a non-partitioned table using DataPump.

SOLUTION

A new import DataPump parameter PARTITION_OPTIONS has been introduced with 11g. The allowed values are:

NONE - Creates tables as they existed on the system from which the export operation was performed. This is the default value.

DEPARTITION - Promotes each partition or subpartition to a new individual table. The default name of the new table will be the concatenation of the table and partition name or the table and subpartition name, as appropriate.

MERGE - Combines all partitions and subpartitions into one table.

The parameter PARTITION_OPTIONS specifies how table partitions should be created during an import operation. To convert a partitioned table to a non-partitoned table we have to use PARTITION_OPTIONS=MERGE during the process of import.

The below example illustrates how to convert partitioned table to a non-partitioned table using expdp/impdp.

1. Create a partitioned table and insert values into the partitioned table

connect scott/<PASSWORD>
create table part_tab
(
   year    number(4),
   product varchar2(10),
   amt     number(10,2)
)
partition by range (year)
(
   partition p1 values less than (1992) tablespace u1,
   partition p2 values less than (1993) tablespace u2,
   partition p3 values less than (1994) tablespace u3,
   partition p4 values less than (1995) tablespace u4,
   partition p5 values less than (MAXVALUE) tablespace u5
);

select * from PART_TAB;

YEAR       PRODUCT    AMT
---------- ---------- ----------
      1992 p1                100
      1993 p2                200
      1994 p3                300
      1995 p4                400
      2010 p5                500

select OWNER, TABLE_NAME, PARTITIONED
from   dba_tables
where  table_name = 'PART_TAB' and owner = 'SCOTT';

OWNER                          TABLE_NAME PAR
------------------------------ ---------- ---
SCOTT                          PART_TAB   YES

select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME
from   dba_tab_partitions
where  TABLE_NAME = 'PART_TAB' and TABLE_OWNER = 'SCOTT';

TABLE_OWNER                    TABLE_NAME PARTITION_ TABLESPACE
------------------------------ ---------- ---------- ----------
SCOTT                          PART_TAB   P1         U1
SCOTT                          PART_TAB   P2         U2
SCOTT                          PART_TAB   P3         U3
SCOTT                          PART_TAB   P4         U4
SCOTT                          PART_TAB   P5         U5

2. Export the partitioned table:

#> expdp TABLES=scott.part_tab USERID="' / as sysdba'" DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=part_tab.log

Export: Release 11.2.0.2.0 - Production on Thu Dec 23 08:27:24 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": TABLES=scott.part_tab USERID="/******** AS SYSDBA" DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=part_tab.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."PART_TAB":"P2" 5.898 KB 1 rows
. . exported "SCOTT"."PART_TAB":"P3" 5.898 KB 1 rows
. . exported "SCOTT"."PART_TAB":"P4" 5.898 KB 1 rows
. . exported "SCOTT"."PART_TAB":"P5" 5.914 KB 2 rows
. . exported "SCOTT"."PART_TAB":"P1" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /tmp/part_tab.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:28:02

3. Import the table in user "USER2" to convert the partitioned table into a non-partitioned table:

#> impdp USERID="'/ as sysdba'" TABLES=scott.part_tab DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=imp_part_tab.log REMAP_SCHEMA=scott:user2 PARTITION_OPTIONS=merge

Import: Release 11.2.0.2.0 - Production on Thu Dec 23 08:39:08 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": USERID="/******** AS SYSDBA" TABLES=scott.part_tab DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=imp_part_tab.log REMAP_SCHEMA=scott:user2 PARTITION_OPTIONS=merge
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER2"."PART_TAB":"P2" 5.898 KB 1 rows
. . imported "USER2"."PART_TAB":"P3" 5.898 KB 1 rows
. . imported "USER2"."PART_TAB":"P4" 5.898 KB 1 rows
. . imported "USER2"."PART_TAB":"P5" 5.914 KB 2 rows
. . imported "USER2"."PART_TAB":"P1" 0 KB 0 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 08:39:17

select * from user2.part_tab;

YEAR       PRODUCT    AMT
---------- ---------- ----------
      1992 p1                100
      1993 p2                200
      1994 p3                300
      1995 p4                400
      2010 p5                500

select OWNER, TABLE_NAME, PARTITIONED
from   dba_tables
where  table_name = 'PART_TAB' and owner = 'USER2';

OWNER                          TABLE_NAME PAR
------------------------------ ---------- ---
USER2                          PART_TAB   NO

select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME
from   dba_tab_partitions
where  TABLE_NAME = 'PART_TAB' and TABLE_OWNER = 'USER2';

no rows selected


Note:
------
If there is a local or global prefixed index created on the partitioned table, import with PARTITION_OPTIONS=merge also converts the index to non-partitioned.

- local prefixed index:
CREATE INDEX part_tab_loc_idx ON part_tab(year) LOCAL;

After import with REMAP_SCHEMA=scott:user2 PARTITION_OPTIONS=merge, the local prefixed index is also converted to a non-partitioned index:

select OWNER, INDEX_NAME, PARTITIONED
from dba_indexes
where index_name='PART_TAB_GLOB_IDX';

OWNER INDEX_NAME PAR
---------- -------------------- ---
SCOTT PART_TAB_LOC_IDX YES
USER2 PART_TAB_LOC_IDX NO  

  -or-

- global prefixed index:  global index 但是分区
CREATE INDEX part_tab_glob_idx ON part_tab(year)
GLOBAL PARTITION BY RANGE (year)
(partition p1 values less than (1992),
partition p2 values less than (1993),
partition p3 values less than (1994),
partition p4 values less than (1995),
partition p5 values less than (MAXVALUE)
);

After import with REMAP_SCHEMA=scott:user2 PARTITION_OPTIONS=merge, the local prefixed index is also converted to a non-partitioned index:

select OWNER, INDEX_NAME, PARTITIONED
from dba_indexes
where index_name='PART_TAB_GLOB_IDX';

OWNER INDEX_NAME PAR
---------- -------------------- ---
SCOTT PART_TAB_GLOB_IDX YES
USER2 PART_TAB_GLOB_IDX NO

-----------------------------

Describes the tablespace for objects created using PARTITION_OPTIONS=MERGE with IMPDP.
 

SOLUTION

Using PARTITION_OPTIONS=MERGE, all partitions and subpartitions are merged into a single table.
The tables and indexes will be created using the default tablespace of the import target user.

example:

# Object configuration of source DB
SQL> select username, default_tablespace from dba_users where username = 'TEST';

USERNAME             DEFAULT_TABLESPACE
-------------------- --------------------
TEST                 USERS

SQL> select segment_name, partition_name, segment_type, tablespace_name from dba_segments where owner = 'TEST' order by 1,2;

SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE         TABLESPACE_NAME
-------------------- -------------------- -------------------- --------------------
T1                   P1                   TABLE PARTITION      TESTTS
T1                   P2                   TABLE PARTITION      TESTTS
T1_I_L               P1                   INDEX PARTITION      TESTTS
T1_I_L               P2                   INDEX PARTITION      TESTTS

# expdp command
expdp test/test directory=tmp_dir dumpfile=part.dmp

# impdp command
impdp test/test directory=tmp_dir dumpfile=part.dmp partition_options=merge

# Object configuration of target DB
SQL> select username, default_tablespace from dba_users where username = 'TEST';

USERNAME             DEFAULT_TABLESPACE
-------------------- --------------------
TEST                 USERS

SQL> select segment_name, partition_name, segment_type, tablespace_name from dba_segments where owner = 'TEST' order by 1,2;

SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE         TABLESPACE_NAME
-------------------- -------------------- -------------------- --------------------
T1                                        TABLE                USERS
T1_I_L                                    INDEX                USERS

If you want the tablespace use with objects to be other than the default tablespace for user, use one of the following options:

a)
a-1) Pre-create objects (tables and indexes) in a non-partitioned configuration
a-2) Run impdp

or

b)
b-1) Run impdp
b-2) Change the tablespace with "alter table ... move" and "alter index ... rebuild"

----------------------merge的坑

SYMPTOMS

If table gets loaded when target table pre-exists and PARTITION_OPTIONS=MERGE and TABLE_EXISTS_ACTION=SKIP is specified in par file or command line, duplicate rows are created.
Although IMPDP honors the skip request, the table data is imported again when PARTITION_OPTIONS=MERGE is specified.

Simple test case scenario :

+++ Create simple table, insert some data and export the table.
+++ Import the table using TABLE_EXISTS_ACTION=SKIP and PARTITION_OPTIONS=MERGE = One row is loaded
+++ Import the table again using TABLE_EXISTS_ACTION=SKIP and PARTITION_OPTIONS=MERGE = The rows is loaded again
+++ Duplicate rows are created if we import again from the same set of data.

Job IMPORT_1
============
impdp user/pwd@myinstance parfile=myfile.par

job_name=IMPORT_1
logfile=mylogfile.log
dumpfile=mydump.DMP
directory=data_pump_dir
TABLE_EXISTS_ACTION=SKIP       <<<<<<<<<<<<<<<<<<<<
PARTITION_OPTIONS=MERGE        <<<<<<<<<<<<<<<<<<<<
schemas=SCHEMA
include=TABLE:"='mytable'"

Import: Release 18.0.0.0.0 - Production on Tue Apr 2 17:38:21 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "USER"."IMPORT_1" successfully loaded/unloaded
Starting "USER"."IMPORT_1":  USER/********@myinstance parfile=myfile.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCHEMA"."mytable"                          5.062 KB       1 rows                  <<<<<<<<<<<<<<<<< 1 row uploaded
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "USER"."IMPORT_1" successfully completed at Tue Apr 2 14:39:09 2019 elapsed 0 00:00:33


SQL> select * from SCHEMA.mytable;

V1
----------
RECORD 01          <<<<<<<<<<<<<<<<< one row
 

Job IMPORT_2 duplicates the row - the issue does reproduce
==========================================================
impdp user/pwd@myinstance parfile=myfile.par

job_name=IMPORT_2
logfile=mylogfile.log
dumpfile=mydump.DMP
directory=data_pump_dir
TABLE_EXISTS_ACTION=SKIP   <<<<<<<<<<<<<<<<<<<<
PARTITION_OPTIONS=MERGE    <<<<<<<<<<<<<<<<<<<<
schemas=SCHEMA
include=TABLE:"='mytable'"

Import: Release 18.0.0.0.0 - Production on Tue Apr 2 17:41:07 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "USER"."IMPORT_2" successfully loaded/unloaded
Starting "USER"."IMPORT_2":  USER/********@myinstance parfile=myfile.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "SCHEMA"."mytable" exists. All dependent metadata and data will be skipped due to table_exists_action of skip  <<<<<<<<
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCHEMA"."mytable"                          5.062 KB       1 rows               <<<<<<<<<<<<<<<< again 1 row uploaded
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "USER"."IMPORT_2" successfully completed at Tue Apr 2 14:41:55 2019 elapsed 0 00:00:34


SQL>  select * from SCHEMA.mytable;

V1
----------
RECORD 01                 <<<<<<<<<<<<<<<<<<<<<<< duplicate record is inserted
RECORD 01   


Job IMPORT_3 doesn't introduce the problem if PARTITION_OPTIONS=MERGE is skipped:
=======================================================================================
impdp user/pwd@myinstance parfile=myfile.par

job_name=IMPORT_3
logfile=mylogfile.log
dumpfile=mydump.DMP
directory=data_pump_dir
TABLE_EXISTS_ACTION=SKIP                                <<<<<<<<<<
schemas=SCHEMA
include=TABLE:"='mytable'"

Import: Release 18.0.0.0.0 - Production on Tue Apr 2 17:55:54 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "USER"."IMPORT_3" successfully loaded/unloaded
Starting "USER"."IMPORT_3":  USER/********@myinstance parfile=myfile.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "SCHEMA"."mytable" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "USER"."IMPORT_3" successfully completed at Tue Apr 2 14:56:39 2019 elapsed 0 00:00:31


Only two rows reported from previous IMPORT_2 job
Job IMPORT_3 did not upload any row
===================================================
SQL> select * from SCHEMA.mytable;

V1
----------
RECORD 01       <<<<<<<<
RECORD 01       <<<<<<<<

 
Job IMPORT_4 doesn't introduce the problem if using PARTITION_OPTIONS=MERGE and TABLE_EXISTS_ACTION=TRUNCATE:
=============================================================================================================
impdp user/pwd@myinstance parfile=myfile.par

job_name=IMPORT_4
logfile=mylogfile.log
dumpfile=mydump.DMP
directory=data_pump_dir
TABLE_EXISTS_ACTION=TRUNCATE       <<<<<<<<<<<
PARTITION_OPTIONS=MERGE            <<<<<<<<<<<
schemas=SCHEMA
include=TABLE:"='mytable'"

Import: Release 18.0.0.0.0 - Production on Tue Apr 2 18:29:08 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "USER"."IMPORT_4" successfully loaded/unloaded
Starting "USER"."IMPORT_4":  USER/********@myinstance parfile=myfile.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "SCHEMA"."mytable" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCHEMA"."mytable"                          5.062 KB       1 rows          <<<<<<<<<<<
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "USER"."IMPORT_4" successfully completed at Tue Apr 2 15:30:01 2019 elapsed 0 00:00:38


Only two rows reported from previous IMPORT_2 job
Job IMPORT_4 did not upload any row
===================================================
SQL> select * from SCHEMA.mytable;

V1
----------
RECORD 01          <<<<<<<<<
RECORD 01          <<<<<<<<<
 

CHANGES

 N/a

CAUSE

This is due to unpublished BUG 27495407 - DP IMPORT LOADS INTO PRE-EXISTING TABLE WITH PARTITION_OPTIONS=MERGE.

SOLUTION

To solve this issue, use any of alternatives below:

1) Make sure the target table does not pre-exist before the import.

- or -

2) Remove PARTITION_OPTIONS=MERGE parameter.

- or -

3) Change table_exists_action to something other than SKIP(default). For example use TABLE_EXISTS_ACTION=TRUNCATE if feasible.
 
- or - 

4) Apply one off < Patch 27495407> if available for your platform and version.

- or -

5) Upgrade to 20.1 where the fix for unpublished Bug 27495407 is included.