Oracle DST Time Zone Upgrade (19c/21c): Key Considerations & Step-by-Step Guide

Upgrading the Oracle Database Time Zone (DST) version is a common task for DBAs, especially when dealing with TIMESTAMP WITH TIME ZONE (TSTZ) data across multi-region applications. However, it is not always required — and doing it incorrectly can cause Data Pump issues (ORA-39405) or unexpected behavior.

This guide covers the key considerations, affected data types, potential pitfalls, and a complete step-by-step upgrade process based on real-world experience (tested on 19c).

Important: Always refer to Oracle Doc ID 412160.1 (Primary Note: DST FAQ) and Doc ID 3002390.1 (Applying DSTv43) before starting.

Is a Time Zone Upgrade Really Required?

It depends. If your database does not use TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE data types, you may skip it. However, for global applications with clients in different time zones, keeping the DST version up-to-date is highly recommended.

Which Data Types Are Affected?

• TIMESTAMP WITH TIME ZONE (TSTZ) → Stores both timestamp and time zone (13 bytes). Ideal for multi-timezone apps.
• TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) → Normalizes to DBTIMEZONE (11 bytes). Not directly affected by DST upgrades.
• DATE and plain TIMESTAMP → Completely unaffected.

Best practice: Set DBTIMEZONE to +00:00 (UTC offset) and never use a named time zone that observes DST.

Find Columns with Time Zone Data

SQL> SELECT c.owner || '.' || c.table_name || '(' || c.column_name || ') - ' || c.data_type AS col
FROM dba_tab_cols c, dba_objects o
WHERE c.data_type LIKE '%WITH%TIME ZONE%'
AND c.owner = o.owner
AND c.table_name = o.object_name
AND o.object_type = 'TABLE'
ORDER BY col;

Key Considerations Before Upgrade

• ORA-39405 Error in Data Pump: Occurs when source TZ version is newer than target. Impdp fails even if no TSTZ data is present.
• Exadata Note: OS image updates do not update DB/JDK time zone files. Database automatically pushes newer TZ files to cells.
• OJVM and Java DST upgrades are separate and usually not required.
• Grid Infrastructure does not need DST update.

Fix for ORA-39405 (Data Pump Import Issue)

A quick workaround when importing from a newer TZ source to an older TZ target:

1. Use JOB_NAME in impdp:
   impdp ... JOB_NAME=IMPDP_TZCHANGE

2. Run this loop in another session (Linux example):

while true
do
  sqlplus / as sysdba <<EOF
    UPDATE sys.IMPDP_TZCHANGE SET property = 43 WHERE property = 32;
    COMMIT;
EOF
done

3. After successful import, kill the loop.

Best permanent fix: Upgrade the target database to the same (or newer) TZ version as the source.

Step-by-Step Time Zone Upgrade (19c Non-CDB Example)

Prepare Window

[oracle@ORACLEDBASECRETS01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.24/dbhome_1
[oracle@ORACLEDBASECRETS01 ~]$ export ORACLE_SID=bltdb1

SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

This script shows how much TSTZ data exists using optimizer statistics. If stats are stale, run utltz_countstar.sql instead.

Key Takeaways

• Check for TSTZ/TSLTZ columns first
• ORA-39405 is the most common migration blocker
• DST upgrade for Database, OJVM, and Java are independent
• Exadata handles cell TZ automatically
• Always test in non-production
• Latest TZ files are shipped with RU but must be manually applied

Conclusion

Upgrading the Oracle Database Time Zone (DST) version is straightforward when you understand the impact on TSTZ data and follow the correct preparation steps. Whether you are migrating across environments or simply staying current with the latest DST rules, this process ensures your timestamp data remains accurate across time zones.

Mastering DST upgrades eliminates Data Pump surprises and keeps your global applications running smoothly.

Please feel free to ask if you have any questions. Thank you 🙂

Toufique Khan