Upgrade DST Time Zone Version in 19C using utltz_upg_apply.sql to avoid ORA-39405 during import

Difference In Dst TZ is  very common  issue  during  migration where we see  below message .  

Luckily  fix is  made very easy in 19c . 

Oracle Data Pump does not support importing from a source database with TSTZ version 35 or 36 into a target database with TSTZ version 34.

OR 

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

Upgrade Part : 

For releases (18c, 19c), the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory

In  case if  Pluggable database we  need to run  script in both cdb and pdb 

The following scripts get delivered with Oracle Database 18c onward

    $ORACLE_HOME/rdbms/admin/utltz_countstats.sql

    Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required. 

    

    $ORACLE_HOME/rdbms/admin/utltz_countstar.sql

    Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.

    

    $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

    Time zone upgrade check script

    

    $ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

    Time zone apply script. Warning: This script will restart the database and adjust time zone data.

Note: If you want to see what is happening when the scripts utltz_upg_check.sql and utltz_upg_apply.sql are being executed, run the following commands:

set PAGES 1000

-- query the V$SESSION_LONGOPS view

select TARGET, TO_CHAR(START_TIME,'HH24:MI:SS - DD-MM-YY'),

TIME_REMAINING, SOFAR, TOTALWORK, SID, SERIAL#, OPNAME

from V$SESSION_LONGOPS

where sid in

(select SID from V$SESSION where CLIENT_INFO = 'upg_tzv')

and

SOFAR < TOTALWORK

order by START_TIME;

-- query the V$SESSION and V$SQLAREA views

select S.SID, S.SERIAL#, S.SQL_ID, S.PREV_SQL_ID,

S.EVENT#, S.EVENT, S.P1TEXT, S.P1, S.P2TEXT,

S.P2, S.P3TEXT, S.P3, S.TIME_REMAINING_MICRO,

S.SEQ#, S.BLOCKING_SESSION, BS.PROGRAM "Blocking Program",

Q1.SQL_TEXT "Current SQL", Q2.SQL_TEXT "Previous SQL"

from V$SESSION S, V$SQLAREA Q1, V$SQLAREA Q2, V$SESSION BS

where S.SQL_ID = Q1.SQL_ID(+) and

S.PREV_SQL_ID = Q2.SQL_ID(+) and

S.BLOCKING_SESSION = BS.SID(+) and

S.CLIENT_INFO = 'upg_tzv';

 

Have  documented Old method in below Article : 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/12/upgrade-timezone-version-on-195-target.html

Views : 

SELECT version FROM v$timezone_file; 

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

SELECT tz_version FROM registry$database;

SELECT DBMS_DST.get_latest_timezone_version FROM   dual;

Reference : 

Multitenant - CDB/PDB - Upgrading DST using scripts - 12.2 and above - ( With Example Test Case - 19.11 ) (Doc ID 2794739.1)

https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/datetime-data-types-and-time-zone-support.html#GUID-7A1BA319-767A-43CC-A579-4DAC7063B243

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