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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *