Problem:

While importing data on a 19.5 database, Data pump terminated with this error:

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

 

Analysis:

This happened because the target database (where the data is getting imported to) is having a lower TIMEZONE version than the Source DB.

Solution:

Upgrade the TIMEZONE version on the 19.5 target DB from 32 (the default) to 33: [Follow below steps]

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 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

Check the availability of the new (installed) timezone version on ORACLE_HOME:

SQL> select DBMS_DST.get_latest_timezone_version from dual;

GET_LATEST_TIMEZONE_VERSION
—————————
33

Before upgrade, perform the pre-upgrade stage steps which will let you know which tables will be impacted by this new TIMEZONE upgrade:

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;
/

SQL> col property_name for a30
col property_value for a20
select property_name, property_value from database_properties where property_name LIKE ‘DST_%’ order by property_name;

PROPERTY_NAME                               PROPERTY_VALUE
——————————                         ——————–
DST_PRIMARY_TT_VERSION            32
DST_SECONDARY_TT_VERSION      33
DST_UPGRADE_STATE                        PREPARE

View the impacted tables due to this TIMEZONE upgrade: [Which

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;

End the pre-upgrade stage:
SQL> EXEC DBMS_DST.end_prepare;

Now, the actual TIMEZONE upgrade: [Downtime required]

— Restart the DB in UPGRADE mode:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE

— Upgrade the TIMEZONE:
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

— 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;
/

Once it’s done, the TIMEZONE upgrade will be complete.

Post checks: Check the current TIMEZONE version:

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
——————–          ———-        ———-
timezlrg_33.dat            33                 0