Dear TOM,
We are using impdp Release 11.2.0.3.0 - Production to exchange data between two databases, both in version 11.2.0.3.0 - 64bit Production.
We use the following import.par parfile with several REMAP_SCHEMA statements:
DUMPFILE=usertest.dmp
LOGFILE=usertest.dmp.import_20180130_095418.log
PARALLEL=6
SCHEMAS=SRDB_MTGCEN_E01001_W,SRDB_MTGCEN_E01002_W
REMAP_SCHEMA=SRDB_MTGCEN_E01001_W:SRDB_MTGCEN_E01002_W,SRDB_MTGCEN_E01002_W:SRDB_MTGCEN_E01004_W
CONTENT=DATA_ONLY
TABLE_EXISTS_ACTION=TRUNCATE
EXCLUDE=TABLE:"IN('CI_OCC','MD','AREA_SCHEMA','CMPRIVATETABLE','LINKTABLE','DATATABLE','CONN_ROLE_OBJ_SCOPE')"
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
What we try to do is:
- to import data from schema SRDB_MTGCEN_E01001_W in the dump file to schema SRDB_MTGCEN_E01002_W in the database
- to import data from schema SRDB_MTGCEN_E01002_W in the dump file to schema SRDB_MTGCEN_E01004_W in the database
When we run impdp we are getting data errors in the log (sorry I have removed some lines to comply with the 20000 char limit in the post):
C:\importer>impdp 'sys/oracle@srdbsw as sysdba' PARFILE=import.par
Import: Release 11.2.0.3.0 - Production on Tue Jan 30 09:59:46 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "sys/********@srdbsw AS SYSDBA" PARFILE=import.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SRDB_MTGCEN_E01004_W"."OBJECT" 6.562 KB 1 rows
. . imported "SRDB_MTGCEN_E01004_W"."OBJ_VIEWS" 5.156 KB 1 rows
. . imported "SRDB_MTGCEN_E01004_W"."APID" 0 KB 0 rows
. . imported "SRDB_MTGCEN_E01004_W"."APID_INST" 0 KB 0 rows
<snap snap snap>
. . imported "SRDB_MTGCEN_E01004_W"."APID" 0 KB 0 rows
. . imported "SRDB_MTGCEN_E01004_W"."APID_INST" 0 KB 0 rows
. . imported "SRDB_MTGCEN_E01004_W"."APID_VIEWS" 0 KB 0 rows
. . imported "SRDB_MTGCEN_E01004_W"."VERIF_PAR" 0 KB 0 rows
. . imported "SRDB_MTGCEN_E01004_W"."VERIF_PHASE" 0 KB 0 rows
. . imported "SRDB_MTGCEN_E01004_W"."OBJECT" 6.562 KB 0 out of 1 rows
1 row(s) were rejected with the following error:
ORA-00001: unique constraint (SRDB_MTGCEN_E01004_W.OBJECT_AK1) violated
. . imported "SRDB_MTGCEN_E01004_W"."OBJ_VIEWS" 5.187 KB 1 rows
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 10:00:23
Tables are truncated and constraints disabled prior to the import. The fact that SRDB_MTGCEN_E01004_W.OBJECT_AK1 is not disabled is not the point: when we inspect the data there are many duplicates, that will prevent re-enabling the constraints again.
As you can see in the log, the problem comes from impdp trying to import data in schema SRDB_MTGCEN_E01004_W twice. No import in database schema SRDB_MTGCEN_E01002_W is done.
Maybe the problem is in the REMAP_SCHEMA statement. impdp could be confused by the fact that there are schemas with the same name both in the dump file and in the database (SRDB_MTGCEN_E01002_W). Apparently, impdp might be applying some transitive calculation on the REMAP_SCHEMA statements so it imports:
- data from schema SRDB_MTGCEN_E01001_W in the dump file to schema SRDB_MTGCEN_E01004_W in the database (because of REMAP_SCHEMA = SRDB_MTGCEN_E01001_W:SRDB_MTGCEN_E01002_W + REMAP_SCHEMA = SRDB_MTGCEN_E01002_W:SRDB_MTGCEN_E01004_W)
- data from schema SRDB_MTGCEN_E01002_W in the dump file to schema SRDB_MTGCEN_E01004_W in the database (because of REMAP_SCHEMA=SRDB_MTGCEN_E01002_W:SRDB_MTGCEN_E01004_W)
Which is, of course, not what we want to do.
We have tried to use both the one-line syntax for REMAP_SCHEMA as shown above, and the multiple-line syntax (multiple REMAP_SCHEMA statements, one for each mapping) but the result is the same.
If we split in two imports, one for each mapping, it works but the process is slower. We would like to do it in one shot, if possible. Maybe we are missing some syntax that could help us?
Thanks in advance,
Jorge.
I think you have to go with separate imports. Given that you're mapping S1 -> S2, then S2 -> S4 it makes sense to me that S1 maps through to S4.
Certainly, I don't know of any parameters which will change this behaviour. If this is a problem for you, you could try raising this as an ER.