Issue impdp giving error ORA-39002: invalid operation , ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 42 into a target database with TSTZ version 32

While trying to restore a schema on UAT from Production, getting above error. Both Production and UAT are on DBCS instance. UAT is with latest 19c patch on DBCS.

Cause: Even though the timezone file (TSTZ version 43) is updated in $ORACLE_HOME, the database registry (registry$database) and database properties (DST_PRIMARY_TT_VERSION) have not been updated to reflect the latest version.

Showing Target is TSTZ 32 (UAT latest patched from OCI console) and Production is 42. Even though we have patched UAfrom OCI DBCS recommended way noticed the TSTZ is not getting updated on the UAT causing this error. The timezone file of UAT showing TSTZ 43 (ls $ORACLE_HOME/oracore/zoneinfo/)

Solution :

You need to update the database registry and synchronize it with the latest timezone file version.

Check timezone file , UAT showing TSTZ 43 (ls $ORACLE_HOME/oracore/zoneinfo/)

Since its a container with pluggable we will do this first on container then on pluggable this change

Check Current Time Zone Version

SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          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;

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

check the version from Timezone file

SELECT DBMS_DST.get_latest_timezone_version
FROM   dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         43

Prepare for the Upgrade
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;
/

Empty the default tables that hold the affected tables list and errors. If you are using custom tables, created with the CREATE_AFFECTED_TABLE, CREATE_ERROR_TABLE, CREATE_TRIGGER_TABLE procedures, then empty those instead.

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

Find tables affected by the upgrade. Depending on your use of TIMESTAMP WITH TIME ZONE columns, you might not have any.

EXEC DBMS_DST.find_affected_tables;

SELECT * FROM sys.dst$affected_tables;
SELECT * FROM sys.dst$error_table;

When you’ve identified the affected tables and determined you are happy to continue, you can end the prepare phase.

EXEC DBMS_DST.end_prepare;

Now will do the Upgrade the Time Zone File

–> First connect to the container and do the

Put the database into upgrade mode. This is not necessary from 21c onward. See the note at the bottom of this article.

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

Begin the upgrade to the latest version.

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

SHUTDOWN IMMEDIATE;
STARTUP;

Do the upgrade of the database file zone file.

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

Now check 
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30

SELECT con_id,
       owner,
       table_name,
       upgrade_in_progress
FROM   cdb_tstz_tables
ORDER BY 1,2,3;

Once the upgrade is complete, check the time zone file version being used.

SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_31.dat              43          0

SQL>


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;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         43
DST_SECONDARY_TT_VERSION       0

–> Now we will connect to the pluggable and do

ALTER SESSION SET CONTAINER = orcl;

ALTER PLUGGABLE DATABASE ORCL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ORCL OPEN UPGRADE;

purge dba_recyclebin;

Now connecting to plugabble database in upgrade mode we will upgrade the timezone

Update the database registry on both container and pluggable

after the upgrade still the registry tz_version will be old TZ, to update do the following

update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

Now the issue is solved and IMPDP works….

After completing above steps

1. The container and pluggable databases will have the latest timezone file version.
2. The mismatch between TSTZ versions will be resolved.
3. The IMPDP process should now work without errors.

Docs followed

https://oracle-base.com/articles/misc/update-database-time-zone-file#google_vignette

Different Time Zone Version In Registry$Database And V$Timezone_file (Doc ID 1255474.1)