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

I got ” ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 32 into a target database with TSTZ version 18. ” error in the Oracle database.

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

Details of error are as follows.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 32 into a target database with TSTZ version 18.

Oracle Data Pump does not support importing from a source database with TSTZ version 32 into a target database with TSTZ version 18.

This ORA-39405 errors are related with the TSTZ version.

You can try to use the database link for this error if you don’t want to upgrade the timezone of database.

OR

To solve this error, Upgrade the TIMEZONE version on the 19c target DB from 18 (the default) to 32:

Download the related patch

Download Patch 28852325: RDBMS – DSTV33 UPDATE – TZDATA2018G:
https://updates.oracle.com/Orion/Services/download/p28852325_190000_Linux-x86-64.zip?aru=23061696&patch_file=p28852325_190000_Linux-x86-64.zip

Extract TIMEZONE V33 files under ORACLE_HOME by applying the patch:
[Can be applied while the DB is up and running, it will place the new TIMEZONE files under ORACLE_HOME]

Unzip and apply the related patch as follows.

# unzip p28852325_190000_Linux-x86-64.zip
# cd 28852325
# opatch apply
Do you want to proceed? [y|n]
y

Check the current timezone version:

SQL> select tz_version from registry$database;

TZ_VERSION
----------
        32

Before upgrade the Timezone of Oracle database, perform the pre-upgrade stage steps as follows.

SQL> DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;
  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_prepare(l_tz_version);
END;
/

Truncate the affected tables as follows.

SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;

SQL> EXEC DBMS_DST.find_affected_tables;

SQL> select * from sys.dst$affected_tables;
SQL> select * from sys.dst$error_table;

Finish the pre-upgrade stage as follows.

SQL> EXEC DBMS_DST.end_prepare;

Now, You can perform the actual TIMEZONE upgrade: [Downtime required]

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE

Upgrade the TIMEZONE of database as follows.

SQL> SET SERVEROUTPUT ON
DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  select DBMS_DST.get_latest_timezone_version into l_tz_version from dual;
  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/

Restart the DB in Normal mode:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

You should Upgrade ZONE files: [Ideally will impact all tables having “TIMESTAMP WITH TIME ZONE” data type]

SQL> SET SERVEROUTPUT ON
DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_failures PLS_INTEGER;
3 BEGIN
4 DBMS_DST.upgrade_database(l_failures);
5 DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
6 DBMS_DST.end_upgrade(l_failures);
7 DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
8 END;
9 /
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "GGS_ADMIN19C"."AQ$_QT$_OGG$RKP0205_39_S"
Number of failures: 0
Table list: "DB_MAINTAIN"."DBA_USERS_YEDEK"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GGS_ADMIN19C"."AQ$_QT$_OGG$RKP0703_25_L"
Number of failures: 0
Table list: "GGS_ADMIN19C"."AQ$_QT$_OGG$RCP0115_106_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

SQL>

Now try to import job again, it will be solved.

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/