The expdp equivalent of ‘exp consistent=y’…
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
Update: Legacy Mode
Since 11.2, Oracle introduced ‘legacy mode‘ options for expdp and impdp.
One now can use the old exp and imp parameters, including ‘CONSISTENT=Y’.
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!
December 19th, 2011 at 17:32
SELECT to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER,’9999999999999999999′) from dual;
49552038
expdp abansal6dba/****** DIRECTORY=’DP_EXP_DIR’ schemas= ‘OLTP_USER’ FLASHBACK_SCN=49552038 dumpfile=’oltp_user_1214time1427pm.dump’ logfile= ‘oltp_user_1214time1427pm.log’
after export
just user normal impdp
impdp abansal6dba/*** DIRECTORY=’DP_EXP_DIR’ schemas= ‘OLTP_USER’ dumpfile=’oltp_user_1214time1427pm.dump’ logfile= ‘imp_oltp_user_1214time1427pm.dump.log’
Thanks