Upgrade Time Zone File And Timestamp With Time Zone Data

Upgrading the time zone file and timestamp with time zone (TSTZ) data in an Oracle database is indeed necessary for certain scenarios, such as when you upgrade to a new version of the database or when there are changes to the rules for Daylight Saving Time (DST).

Here’s a general process to perform this upgrade:

    1. Check the latest version of the time zone file available in the Oracle home:

[orauat@devdb oracore]$ cd $ORACLE_HOME/oracore/

[orauat@devdb oracore]$ ls -ltrh
total 6
drwxr-xr-x 4 orauat dba 34 May 7 2021 zoneinfo
drwxr-xr-x 2 orauat dba 24 May 7 2021 mesg

      2. Backup current zone info directory

[orauat@devdb oracore]$ cp -pr zoneinfo zoneinfo_old

     3. Check the status of the database:

[orauat@devdb oracore]$ more $ORACLE_HOME/oracore/zoneinfo/timezdif.csv

# File version 1.0
# Fields: VERSION#, TIMEZONE_NAME, FROM_YEAR, TO_YEAR
# Note: A NULL/blank for TO_YEAR means max year representable/allowed by db.
14, Africa/Casablanca, 2010,
14, Africa/Tunis, 2010,
14, America/Argentina/San_Luis, 2010,
14, America/Tijuana, 2010,
14, America/Santiago, 2010, 2010
14, America/Asuncion, 2010,
14, Antarctica/Casey, 2010,
14, Antarctica/Davis, 2010,

      4. Check also from sqlplus:

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

PROPERTY_NAME VALUE
—————————— ——————————
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

SQL> select * from v$timezone_file;

FILENAME VERSION
——————– ———-
timezlrg_14.dat 14

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
———-
14

            5. Set related parameter

Check The recycle bin, it should be purged, and some session parameters set as per the given MOS notes:

Time Zone upgrade performed through DBUA takes more time in databases that have large amount of data impacted by new TZ files (Doc ID 2259734.1)
Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

alter session set “_with_subquery”=materialize;
alter session set “_simple_view_merging”=TRUE;
purge dba_recyclebin;

      6. Start the upgrade process

SQL> set serveroutput on

SQL> exec DBMS_DST.BEGIN_PREPARE(31);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.

SQL> shutdown immediate;

SQL> startup upgrade;
ORACLE instance started.

SQL> exec DBMS_DST.BEGIN_UPGRADE(31);
PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3
  
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         31
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              UPGRADE SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

SQL> truncate table sys.dst$error_table;
Table truncated.

SQL> truncate table sys.dst$trigger_table;
Table truncated.

07. Check the Possible errors with the following query given below:

SQL> SELECT * FROM sys.dst$error_table;
no rows selected

SQL> alter session set “_with_subquery”=materialize;
Session altered.

SQL> alter session set “_simple_view_merging”=TRUE;
Session altered.

SQL> set serveroutput on
SQL> VAR numfail number

SQL> BEGIN
     DBMS_DST.UPGRADE_DATABASE(:numfail,
     parallel                  => TRUE,
     log_errors                => TRUE,
     log_errors_table          => 'SYS.DST$ERROR_TABLE',
     log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
     error_on_overlap_time     => TRUE,
     error_on_nonexisting_time => TRUE);
     DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
     END;
   /
 
PL/SQL procedure successfully completed. 

08. Now, Check error again 

SQL> SELECT * FROM sys.dst$error_table; 
no rows selected

09. Now Check Updated Timezone 

SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_31.dat              31 

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         31
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE