ORA-39405 IMPDP Error

Problem

Following Error Occur when try to import oralce export backup using impdp utility

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 43 into a target database with TSTZ version 32

Cause

Identified that the DST (Daylight Saving Time) versions between the source and target databases are different

Source

SQL> SELECT version FROM v$timezone_file;
Version
----------
43

Target

SQL> SELECT version FROM v$timezone_file;
Version
----------
32

Solution

Method-1

Re-export from source with compatibility version parameter

expdp system/managerd directory=dump full=Y dumpfile=expdp_full_prod.dmp logfile=expdp_full_prod.log version=19.0.0

Method-2

Upgrade target DB to same TSTZ version

Applying the DSTv43 update for the Oracle Database (Doc ID 3002390.1)

Method-3

Workaround Steps to Overcome the Import Error

1. Prepare impdp command with job_name option

impdp system/manager directory=dump dumpfile=expdp_full_prod.dmp logfile=eimpdp_full_prod.log job_name=impdp_tzchange

2. Create and execute a script in another terminal that runs in an infinite loop as follows

Linux

 
$ cat tz.sh

export ORACLE_SID=TEST
while true
do
  sqlplus / as sysdba <<EOF
    update sys.IMPDP_TZCHANGE set property = 43 where property = 32;
    commit;
EOF
done

$ ./tz.sh 

Windows

 
D:\backup> type tz_update.sql

update sys.IMPDP_TZCHANGE
   set property = 43
 where property = 32;
commit;
exit;

 
D:\backup> type tz_loop.bat

@echo off
:loop
sqlplus / as sysdba @tz_update.sql
goto loop

D:\backup> tz_loop.bat

3. execute impdp command which prepared in step-1

4. Once the import job has been succesfully completed, Terminate the script which run as infinite to updating the master table