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

Continue reading

Oracle Database Undo space explained

In this blog post I will talk about the basic workings of Automatic Undo Management, which can cause ORA-01555 and ORA-30036 issues.

The scope is Automatic Undo Management used in 10g and 11g, but has to be explicitly set for 9i (UNDO_MANAGEMENT = AUTO). Manual Undo Management is out of scope for this blog.

The Undo tablespace is a normal tablespace like any other, but only Oracle is controlling what is happening inside it.

Undo something

The Undo tablespace is used for several features: ROLLBACK, READ CONSISTENCY and FLASHBACK technology. Continue reading