ORA-39083:
Object type REF_CONSTRAINT failed to create with error
ORA-39083 ORA-02298
during impdp
Environment:
Oracle database server 11.2.0.2.
Error
Message:
> impdp \'/as
sysdba\' dumpfile=TRANST0813_%U.dmp logfile=impTRANST0813.log
directory=DATA_PUMP_DIR REMAP_SCHEMA=TRANST:TRANST2, TRANSTADM:TRANSTADM2
Import: Release
11.2.0.2.0 - Production on Mon Aug 13 17:11:00 2012
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 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP, Data Mining
and Real Application
Testing options
Master table
"SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting
"SYS"."SYS_IMPORT_FULL_01":
"/******** AS SYSDBA" dumpfile=TRANST0813_%U.dmp logfile=impTRANST0813.log
directory=DATA_PUMP_DIR REMAP_SCHEMA=TRANST:TRANST2, TRANSTADM:TRANSTADM2
Processing object type
SCHEMA_EXPORT/USER
Processing object type
SCHEMA_EXPORT/ROLE_GRANT
Processing object type
SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type
SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type
SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type
SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type
SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type
SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type
SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type
SCHEMA_EXPORT/TABLE/TABLE
Processing object type
SCHEMA_EXPORT/TABLE/TABLE_DATA
.
.
.
.
Processing object type
SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type
SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type
SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (TRANSTADM2.FK_INV_ITM_ATTCHMNT_INV_ITM_ID)
- parent keys not found
Failing sql is:
ALTER TABLE "TRANSTADM2"."INVENTORY_ITEM_ATTACHMENT"
ADD CONSTRAINT "FK_INV_ITM_ATTCHMNT_INV_ITM_ID" FOREIGN KEY
("INVENTORY_ITEM_ID") REFERENCES "TRANSTADM2"."INVENTORY_ITEM"
("INVENTORY_ITEM_ID") ON DELETE SET NULL ENABLE
> oerr ora 39083
39083, 00000, "Object type %s failed to create with
error:\n%s\nFailing sql is:\n%s"
// *Cause: Examine
original error code to determine actual cause
// *Action: Original error code will contain more
information
cpidbplr21 | PREODS2 |
/u40_backup/MHPREPRD/PREPRDCH/rman/dbf
> oerr ora 02298
02298, 00000,"cannot validate (%s.%s) - parent keys
not found"
// *Cause: an alter table
validating constraint failed because the table has
// orphaned child records.
// *Action: Obvious
In the old exp/imp utility, we had parameter CONSISTENT for cross-table consistency and we
do not have it in impdp. So, how to get a consistent image of data using
expdp/impdp
Using FLASHBACK_TIME is an easier way of doing it. Pick a
timestamp and use it in during expdp to get a consistent image at that point of
time.
expdp \'/as sysdba\'
dumpfile=TRANST0813_%U.dmp logfile=TRANST0813.log directory=DATA_PUMP_DIR
SCHEMAS=TRANST,TRANSTADM PARALLEL=4 flashback_time=\"TO_TIMESTAMP
\(TO_CHAR \(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\'\), \'YYYY-MM-DD
HH24:MI:SS\'\)\"
Use the export dump generated from the above statement
and the import will run fine.
Also, try flashback_time=SYSTIMESTAMP
ReplyDelete