Upgrade the Database Time Zone File Using the DBMS

Upgrade the Database Time Zone File Using the DBMS_DST Package

The DBMS_DST package was introduced to simplify the process of upgrading the database time zone file.

Step 1: Check the Current Time Zone Version

You can verify the existing time zone version using different queries.

1.1 Query V$TIMEZONE_FILE

SELECT * FROM v$timezone_file;

Output:

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat              26          0

1.2 Query REGISTRY$DATABASE

SELECT tz_version FROM registry$database;

Output:

TZ_VERSION
----------
        26

1.3 Query DATABASE_PROPERTIES

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;

Output:

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

1.4 Check the Latest Available Time Zone Version

SELECT DBMS_DST.get_latest_timezone_version FROM dual;

Output:

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

Since our database is running version 26, and the latest available is 43, an upgrade is necessary.

Step 2: Prepare for the Upgrade

2.1 Begin the Preparation Phase

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

2.2 Verify the Preparation Phase

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;

You should now see DST_SECONDARY_TT_VERSION = 43 and DST_UPGRADE_STATE = PREPARE.

2.3 Find Affected Tables

EXEC DBMS_DST.find_affected_tables;

Check the results:

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

If there are no affected tables, you can proceed with the upgrade.

2.4 End the Preparation Phase

EXEC DBMS_DST.end_prepare;

Step 3: Upgrade the Time Zone File (Non-CDB)

3.1 Put the Database into Upgrade Mode (Pre-21c Only)

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

3.2 Begin the 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;
/

3.3 Restart the Database (Pre-21c Only)

SHUTDOWN IMMEDIATE;
STARTUP;

3.4 Perform the Upgrade

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

3.5 Verify the Upgrade

SELECT * FROM v$timezone_file;
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%';

You should now see DST_PRIMARY_TT_VERSION = 43 and DST_UPGRADE_STATE = NONE.

Step 4: Upgrade the Time Zone File (Multitenant)

In a CDB/PDB environment, you must perform the upgrade across all containers.

4.1 Create the Upgrade Script

Save the following script as /tmp/upgrade_tzf.sql:

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


SHUTDOWN IMMEDIATE;
STARTUP;

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

4.2 Run the Upgrade in All Containers

Execute the script using catcon.pl:

$ORACLE_HOME/perl/bin/perl \
    -I$ORACLE_HOME/perl/lib \
    -I$ORACLE_HOME/rdbms/admin \
    $ORACLE_HOME/rdbms/admin/catcon.pl \
    -n 1 \
    -l /tmp/ \
    -b upgrade_tzf \
    /tmp/upgrade_tzf.sql

4.3 Verify the Upgrade in All Containers

-- CDB

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;

-- NON CDB

COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT owner, table_name, upgrade_in_progress
FROM   dba_tstz_tables
ORDER BY 1,2;

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Upgrade the Database Time Zone File Using the DBMS_DST Package