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

How To Avoid ORA-39405 During a Data Pump Import

If you have worked on Oracle database migrations using Data Pump, you may have come across this error during an import:

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version <source_version> into a target database with TSTZ version <target_version>

I recently came across this issue while looking at Data Pump migration scenarios, and the important thing to understand is that this error is related to the Time Zone File version of the source and target databases.

It can be easy to miss because we normally check the Oracle Database version first. But having the same database version on both sides doesn't necessarily mean the Time Zone File versions are the same.

First thing I check

Before starting any Data Pump migration, I recommend checking the Time Zone File version on both databases.

Run this on the source:

SELECT version FROM v$timezone_file;

And run the same command on the target.

For example:

Source Database
TSTZ Version: 43

Target Database
TSTZ Version: 32

Here we have a mismatch.

The source is using Time Zone File version 43, while the target is still using version 32. If the dump contains data that requires the newer Time Zone File information, the import can fail with ORA-39405.

Why does this happen?

Oracle uses Time Zone File information for data types such as:

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

During the Data Pump import, Oracle needs to make sure the target database can correctly understand the time zone information coming from the source.

If the target is running with an older Time Zone File version, Oracle can stop the import rather than risk incorrect time zone data.

That's why this check is important, especially for production migrations.

Don't confuse Data Pump VERSION with TSTZ version

This is another area where I have seen confusion.

You might be using:

expdp system/password \
directory=DP_DIR \
dumpfile=prod.dmp \
logfile=prod_exp.log \
schemas=APP \
version=19.0

The VERSION parameter in Data Pump is related to database and metadata compatibility.

It is not the same thing as the Time Zone File version.

So changing:

VERSION=19.0

doesn't automatically fix ORA-39405.

For this particular error, check:

SELECT version FROM v$timezone_file;

on both sides.

What should we do if the versions are different?

If the target database has an older Time Zone File version, the normal approach is to bring the target to the required Time Zone File version before performing the import.

Before making any change, I would strongly recommend checking the Oracle documentation and the supported procedure for the specific Oracle Database release and patch level.

Also check which Time Zone Files are available in the Oracle Home:

$ORACLE_HOME/oracore/zoneinfo/

You may find files such as:

timezlrg_32.dat
timezlrg_43.dat

The exact versions available will depend on your Oracle Home and patch level.

I would not recommend manually replacing Time Zone Files in a production Oracle Home just to get around the error. Treat the Time Zone File upgrade as a proper database maintenance activity and test it first.

My recommendation for migration projects

One small change in the migration checklist can save a lot of troubleshooting later.

Before starting expdp/impdp, I normally verify things like:

Oracle Database Version
Time Zone File Version
Character Set
NLS Settings
Tablespaces
Users and Roles
Storage Availability
Data Pump Version
Database Links

But the Time Zone File check is particularly easy to forget.

Just run:

SELECT version FROM v$timezone_file;

on both source and target.

If you find:

Source  : 43
Target  : 32

don't wait until the import fails.

Address it during the migration planning stage.

One more practical tip

If you are migrating a large production database, don't discover ORA-39405 after spending hours exporting the database, copying the dump files, and preparing the target.

Do the compatibility checks before the migration window.

I've always found that a few minutes spent on pre-checks can save hours during a migration.

So, whenever you're planning an Oracle Data Pump migration, add this simple command to your checklist:

SELECT version FROM v$timezone_file;

Check it on both sides.

It takes only a few seconds—and it can save you from a very frustrating Data Pump import failure.

#Oracle #OracleDatabase #OracleDBA #DataPump #EXPDP #IMPDP #ORA39405 #DatabaseMigration #Oracle19c #DBA #DatabaseAdministration