Upgrade the Oracle Database Time Zone File Using the DBMS_DST Package

The current web page context is about how to upgrade the database time zone file using the DBMS_DST package. Here are some key takeaways:

• The DBMS_DST package is a PL/SQL package that simplifies the process of upgrading the database time zone file and the time zone data based on the new file.
• Upgrading the time zone file is necessary when countries change their time zones or daylight saving time policies, which may affect the data stored in TIMESTAMP WITH TIME ZONE columns.
• The DBMS_DST package provides procedures to check the current and latest versions of the time zone file, prepare for the upgrade, perform the upgrade, and end the upgrade.
• The upgrade process involves three phases: prepare, upgrade, and end. The prepare phase is optional but recommended to check the impact of the upgrade. The upgrade phase can be done in online or offline mode. The end phase finalizes the upgrade and cleans up temporary data.

1.0. Check Current Time Zone Version

SELECT * FROM v$timezone_file;

or

SELECT tz_version FROM registry$database;

or

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;

or

alter system set "_exclude_seed_cdb_view"=false scope=both;

select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;

1.1. Latest timezone file

SELECT DBMS_DST.get_latest_timezone_version FROM dual;

2.0. Prepare for the Time Zone 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;

/

2.1. Check the DST_UPGRADE_STATE

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;

Now DST_UPGRADE_STATE is in PREPARE mode.

3.0. To clear the data from the tables that store the information about the affected tables and the errors, you need TRUNCATE sys.dst$affected_tables and sys.dst$error_table. If you have created your own custom tables using the CREATE_AFFECTED_TABLE, CREATE_ERROR_TABLE, or CREATE_TRIGGER_TABLE, then you need to TRUNCATE those tables instead of the default ones.

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

3.1. To identify the tables that affected by the upgrade. This query will return the names of the tables and columns that have the TIMESTAMP WITH TIME ZONE data type. If you do not use this data type in your database, the query will return an empty result set.

EXEC DBMS_DST.find_affected_tables;

3.2. Check the results of the executing the DBMS_DST.find_affected_tables procedure.

SELECT * FROM sys.dst$affected_tables;

SELECT * FROM sys.dst$error_table;

3.3. After reviewing the tables that will be impacted by the operation and confirming that you are ready to proceed, you can complete the prepare stage.

EXEC DBMS_DST.end_prepare;

4.0. Upgrade the Time Zone File (Multitenant)

The time zone file is a critical component of a multitenant database, as it affects the date and time calculations for different regions. When upgrading the time zone file, you need to ensure that all containers are updated, not just the root container. Otherwise, you will have inconsistency and errors in your data. The "catcon.pl" Perl script is a tool that allows you to run commands or scripts in multiple containers at once. By using this script, you can upgrade the time zone file in the root container and all PDBs in one step. This will also ensure that any new PDBs created from the seed will have the latest time zone file.

4.1. Create a script with the following contents. The shutdown and startup operations are obsolete from 21c onward.

tee /oracle/scripts/ug/tz/upgrade_tzf.sql > /dev/null << EOF

-- Pre-21c Version only.

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;

/

-- Pre-21c Version only.

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;

/

EOF

4.2. Run the above script to all of the containers.

To execute the script on all containers, we use the "catcon.pl" Perl script. The -n parameter is set to 1, so the script runs each PDB recompilation in sequence.. The script performs a shutdown and startup. If the script is executed in parallel, the root container will interfere with some of the PDBs' upgrade when it shuts down or starts up. Using a single worker ensures a sequential order. You may run it in cron job.

touch /oracle/scripts/ug/tz/upgrade_tzf.log

touch /oracle/scripts/ug/tz/upgrade_tzf.lockfile

touch /oracle/scripts/ug/tz/upgrade_tzf.sh

chmod a+x /oracle/scripts/ug/tz/upgrade_tzf.sh

watch -n 1 'ps -axuwwf | grep upgrade_tzf'

watch -n 1 'sudo tail -n 5 /var/log/cron'

tail -f -n 5 /oracle/scripts/ug/tz/upgrade_tzf.lockfile

tail -f -n 5 /oracle/scripts/ug/tz/upgrade_tzf.log

tee /oracle/scripts/ug/tz/upgrade_tzf.sh > /dev/null << EOF

#!/bin/bash

echo 'Generating Config File Start............................................'$(date) 2>&1 | tee /oracle/scripts/ug/tz/upgrade_tzf.log

source /oracle/scripts/Oracle19cEnvVar.sh

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /oracle/scripts/ug/tz/ -b upgrade_tzf -d /oracle/scripts/ug/tz/ /oracle/scripts/ug/tz/upgrade_tzf.sql 2>&1 | tee -a /oracle/scripts/ug/tz/upgrade_tzf.log

echo 'Generating Config File End............................................'$(date) 2>&1 | tee -a /oracle/scripts/ug/tz/upgrade_tzf.log

EOF

crontab -e

#50 18 * * 4 /usr/bin/flock -n /oracle/scripts/ug/tz/upgrade_tzf.lockfile /oracle/scripts/ug/tz/upgrade_tzf.sh > /dev/null 2>&1

5.0. 21c and onward Update

One of the new features of Oracle 21c is the ability to update the time zone file without putting the database in upgrade mode. This means that the database can remain online and available while the time zone file is upgraded to the latest version. To perform this operation, you can use the following command:

alter system set timezone_version_upgrade_online=true;

Now follow the setp 4 without database shutdown and startup command.

6.0. Conclusion

In conclusion, upgrading the Oracle Database time zone file using the DBMS_DST package is a necessary and beneficial process that allows the database to handle changes in daylight saving time rules and ensure data accuracy and consistency. The DBMS_DST package provides various procedures and functions to perform the upgrade in a safe and efficient manner, such as validating the data, preparing the upgrade scripts, applying the patches, and verifying the results. The upgrade process requires careful planning and testing before applying it to the production environment, as well as proper backup and recovery strategies in case of any issues. By following the steps and best practices outlined in this document, you can successfully upgrade the Oracle Database time zone file using the DBMS_DST package and enjoy the benefits of having an up-to-date and reliable database.