Upgrade TIMEZONE TSTZ Version

Recently,Tried importing a datapump export from 21c to 19c - came across below error
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 32.
 

Solution 

Upgrade the DST TZ version of the target database to higher or equal to source database DST TZ version.

Reference

Applying the DSTv34 update for the Oracle Database (Doc ID 2602555.1)

Applying the DSTv36 update for the Oracle Database (Doc ID 2767770.1)

Applying the DSTv35 update for the Oracle Database (Doc ID 2676922.1)

21c - SOURCE
SQL> SELECT * FROM v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              35          0

19c - TARGET
SQL> SELECT * FROM v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0

Apply Patch,     

            RDBMS - DSTV35 UPDATE - TZDATA2020A (Patch) : 31335037

Upgrade Opatch to the Latest Version 
E:\oracle\app\product\dbhome_19\OPatch>opatch version
OPatch Version: 12.2.0.1.39
OPatch succeeded.

Apply the TSTZ patch. 

    -- Shutdown All Services Running from Oracle home

    -- Apply the patch

opatch prereq CheckConflictAgainstOHWithDetail -ph C:\Softwares\TSTZ_Patches\31335037
	Oracle Interim Patch Installer version 12.2.0.1.39
	Copyright (c) 2023, Oracle Corporation.  All rights reserved.
	PREREQ session
	Oracle Home       : E:\oracle\app\product\dbhome_19
	Central Inventory : C:\Program Files\Oracle\Inventory
   	from           :
	OPatch version    : 12.2.0.1.39
	OUI version       : 12.2.0.7.0
	Log file location : E:\oracle\app\product\dbhome_19\cfgtoollogs\opatch\opatch2023-09-10_13-32-59PM_1.log
	Invoking prereq "checkconflictagainstohwithdetail"
	Prereq "checkConflictAgainstOHWithDetail" passed.
	OPatch succeeded.

opatch apply C:\Softwares\TSTZ_Patches\31335037 -oh E:\oracle\app\product\dbhome_19

Patch Applied successfully !

RESUME ALL Database SERVICES

SQL> SELECT * FROM v$timezone_file;

    FILENAME                VERSION     CON_ID
    -------------------- ---------- ----------
    timezlrg_32.dat              32          0   <--- Still 32 not upgraded

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 

    PROPERTY_NAME                VALUE
    ------------------------------------
    DST_PRIMARY_TT_VERSION       32
    DST_SECONDARY_TT_VERSION     0
    DST_UPGRADE_STATE            NONE

Verify latest TZ Version

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM   dual;
	GET_LATEST_TIMEZONE_VERSION
	---------------------------
               35  <-- Target version we need to reach upto

Upgrade TZTS

shut immediate
startup upgrade
select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
adfstg           OPEN MIGRATE

Prepare Pre-Upgrade Stage

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

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;



SQL> EXEC DBMS_DST.find_affected_tables;
    PL/SQL procedure successfully completed.

SQL> select * from sys.dst$error_table;
    no rows selected

SQL> EXEC DBMS_DST.end_prepare;
    PL/SQL procedure successfully completed.

End the Pre-Upgrade stage

shut immediate
startup upgrade

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

End TZ Upgrade

shut immediate
startup


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

Verify TZ Upgrade

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_35.dat              35          0



COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;