The expdp equivalent of ‘exp consistent=y’…

Update: Legacy Mode

In 11.2, Oracle introduced ‘legacy mode‘ options for expdp and impdp.

One now can use the old exp and imp parameters, including ‘CONSISTENT=Y’.

Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "consistent=TRUE" Location: Parameter File, Replaced with:
  "flashback_time=TO_TIMESTAMP('2013-05-07 07:18:29', 'YYYY-MM-DD HH24:MI:SS')"
Legacy Mode has set reuse_dumpfiles=true parameter.

For 11.1 and older, original post:

Last few weeks I have been exporting 10g databases for migrating them to 11g. I knew Export Data Pump was read consistent (I have been running into ORA-01555‘s), but I was surprised to see a ORA-02298 (parent keys not found) when importing:

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (SCOTT.FK_SCALE_ID) - parent keys not found
Failing sql is:
ALTER TABLE "SCOTT"."SCALE_ID" ADD CONSTRAINT "FK_SCALE_ID" FOREIGN KEY ("SCALE_ID") REFERENCES "SCOTT"."SCALE" ("ID") ENABLE

I thought the expdp had made an consistent database dump, but didn’t! The FK’s present in SALARY were not present as PK’s in the SCALE table, so this could only mean the dump was not database consistent at all!

Solution: Using the flashback_time option

I found a discussion about ‘exp consistent=y’ on AskTom. Check the followup on June 6, 2010:

Followup
“in all cases, a table is always exported “as of a single point in time”. However, many times you want ALL tables exported as of a single point in time. If you want the equivalent of the old export “consistent=y”, you would use flashback_time=systimestamp to get the data pump export to be “as of the point in time the export began, every table will be as of the same commit point in time” ”

According to the docs:

“The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent as of this SCN.”

Using the ‘systimestamp’ option will pass the current time value, as easy as that!

Example

expdp scott dumpfile=expdp.dmp logfile=expdp.log flashback_time=systimestamp

Summary

• By default expdp is consistent only for the table it is currently exporting.
• Use ‘flashback_time=systimestamp‘ to simulate the old export ‘consistent=y’.

Happy Data Pumping!