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