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

[Read the rest of this entry...]

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.

[Read the rest of this entry...]