ORA-02298 During Data Pump Import — Why It Happens and How to Handle It

 

ORA-02298 During Data Pump Import — Why It Happens and How to Handle It

Recently, I was working on a database migration where Data Pump failed to create a foreign key constraint during import.

The error looked like this:

01-JUN-26 03:12:18.148: W-8 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
01-JUN-26 03:18:58.517: ORA-39083: Object type REF_CONSTRAINT:"APPUSER"."FK_CHILDTABLE_C001" failed to create with error:
ORA-02298: cannot validate (APPUSER.FK_CHILDTABLE_C001) - parent keys not found

ALTER TABLE "APPUSER"."CHILDTABLE" ADD CONSTRAINT "FK_CHILDTABLE_C001"
  FOREIGN KEY ("C001") REFERENCES "APPUSER"."PARENTTABLE" ("C001") ENABLE

At first glance, it looks like some data was lost during the migration.

But that wasn't actually the case.

The interesting part was that the foreign key was VALIDATED in the source database.

So the obvious question was:

If the constraint was valid on the source, how can the parent rows be missing on the target?

The reason is how Data Pump export works

A normal Data Pump export is not necessarily a single point-in-time export of the entire database.

Each table can be exported at a different SCN.

For example:

Object	Export SCN
Export starts	100
T1	110
PARENT1	120
CHILD1	130
Export finishes	140

Now imagine PARENT1 and CHILD1 have a foreign key relationship.

A user inserts a new parent row at SCN 125, and the corresponding child row at SCN 130.

The important thing is that:

• PARENT1 was exported at SCN 120, so the new parent row isn't included.
• CHILD1 was exported at SCN 130, so the child row is included.

When Data Pump imports the data, it now has a child row without its corresponding parent row.

And when Oracle tries to enable the foreign key, it quite correctly says:

ORA-02298: cannot validate - parent keys not found

So this doesn't necessarily mean that Data Pump lost data.

It's a consequence of exporting related tables at different points in time.

What about GoldenGate?

This is where things become interesting.

In our migration, we were using Data Pump + GoldenGate together, so this wasn't actually a data-loss problem.

Data Pump records the SCN associated with the exported objects, and GoldenGate can use those SCNs with Automatic Per Table Instantiation.

For example:

Table	Replication starts from
T1	SCN 110
PARENT1	SCN 120
CHILD1	SCN 130

GoldenGate then starts replicating changes from the appropriate point.

So the parent row created after SCN 120 will eventually be replicated to the target.

Once GoldenGate has caught up beyond that point, the parent row exists on the target and the foreign key can be created and validated successfully.

This is why, in an initial-load migration using Data Pump and GoldenGate, ORA-02298 doesn't automatically mean that something went wrong with the migration.

What about ZDM?

In our case, the migration was being performed using Zero Downtime Migration (ZDM).

We had to tell ZDM to ignore this particular Data Pump error:

IGNOREIMPORTERRORS=ORA-02298,...

The important part here is not simply to ignore the error and forget about it.

You need to understand why the error happened and make sure the missing parent rows will arrive through the replication process.

Once GoldenGate catches up, the constraint should be validated.

Another option — Fully consistent Data Pump export

If you don't want tables to be exported at different SCNs, Data Pump can perform a consistent export using:

FLASHBACK_TIME=SYSTIMESTAMP

For example:

expdp ... FLASHBACK_TIME=SYSTIMESTAMP

Now all the tables are exported as of the same point in time.

Using the previous example, it would look more like:

Object	Export SCN
T1	100
PARENT1	100
CHILD1	100
Export finishes	140

This avoids the parent/child mismatch caused by different export SCNs.

For ZDM, the equivalent response-file parameter is:

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_FLASHBACKTIME=SYSTIMESTAMP

But there is a catch...

A consistent export means Oracle may need to maintain the required older read-consistent data for the entire duration of the export.

If your export takes four hours, you need enough UNDO to support that.

Otherwise, you may run into:

ORA-31693: Table data object failed to load/unload
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old

This is something I would definitely consider before blindly using FLASHBACK_TIME on a large and busy production database.

What about exporting from a standby?

Another option is to perform the Data Pump export from a standby database, especially when the primary is heavily loaded.

For suitable environments, a snapshot standby can be useful for this type of migration activity.

It can also help reduce the impact of a long-running Data Pump export on the primary database.

My takeaway

When I first saw ORA-02298 during the import, it looked like a serious data consistency problem.

And normally, it is something you should investigate carefully.

But in a migration where Data Pump is being used for the initial load and GoldenGate is handling ongoing replication, the situation can be different.

The key is understanding the SCNs involved.

Data Pump loads the initial data. GoldenGate catches up the changes. Once the missing parent rows arrive, the foreign key can be validated.

So before treating ORA-02298 as data loss, check:

• Was the source constraint valid?
• Were parent and child tables exported at different SCNs?
• Is GoldenGate configured for automatic table instantiation?
• Has GoldenGate caught up beyond the relevant SCN?
• Can the constraint be validated after replication catches up?

That little bit of SCN awareness can save a lot of unnecessary panic during a migration. 🙂

#Oracle #OracleDatabase #DataPump #GoldenGate #ZDM #DatabaseMigration #OracleDBA #ZeroDowntimeMigration #DataGuard #DBA