Skip to Main Content
  • Questions

  • impdp with multiple REMAP_SCHEMA statements tries to load data twice in the same schema

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jorge.

Asked: January 30, 2018 - 9:56 am UTC

Last updated: February 01, 2018 - 4:37 pm UTC

Version: 11.2.0.3.0 - 64bit Production

Viewed 50K+ times! This question is

You Asked

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.




and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OK but I propose to update the documentation

Jorge Pacios, February 01, 2018 - 12:12 pm UTC

Thanks for your answer.

In our case separate imports would work, so I think there is no need for an ER.

However, I believe the documentation should be improved.

For instance, from:
https://docs.oracle.com/database/121/SUTIL/GUID-619809A6-1966-42D6-9ACC-A3E0ADC36523.htm#SUTIL927

REMAP_SCHEMA
Default: There is no default

Purpose

Loads all objects from the source schema into a target schema.

Syntax and Description

REMAP_SCHEMA=source_schema:target_schema
Multiple REMAP_SCHEMA lines can be specified, but the source schema must be different for each one. However, different source schemas can map to the same target schema. Note that the mapping may not be 100 percent complete; see the Restrictions section below.
If the schema you are remapping to does not already exist, then the import operation creates it, provided that the dump file set contains the necessary CREATE USER metadata for the source schema, and provided that you are importing with enough privileges.

Reading this, in my mind "source" meant "in the dump file" and "target" meant "in the database", especially if impdp will try to create the target in case it does not exist (rather than matching it with some source and then doing something else). Since it seems not to be the case, the logic should be clearly stated and justified (maybe there is an interesting use case for the implemented behaviour that I am missing), together with the restrictions (should all the intermediate schema names between the real source and target exist somewhere, or are they treated just as dummy labels?).

Best regards,
Jorge.

Chris Saxon
February 01, 2018 - 4:37 pm UTC

There's a feedback button on the docs. Post your comments there!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.