Upgrade the Database Time Zone File Using the DBMS_DST Package | ORA-56920

The DBMS_DST package was proposed to streamline the process of upgrading the database time zone file, along with the time zone data based on new time zone file.

Countries sometimes change their time zones, or alter the way they handle daylight saving time (DST). From Oracle 11gR2 onward, new time zone files are shipped with upgrades and patches, but they are not automatically applied to the database.

Applying a change to the database time zone file not only affects the way new data is handled, but potentially alters data stored in TIMESTAMP WITH TIME ZONE columns, so you need to consider the impact of this before upgrading the time zone file.

Remember, if you only deal with dates in your country, and your country has not altered its time zone or daylight saving time policy, this upgrade may not be necessary.


Find Present Time Zone Version

The V$TIMEZONE_FILE view displays the zone file version –

SELECT * FROM v$timezone_file;

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

You can get the same information from other available options like from registry$database and database_properties

B8qKMQJzh+ePAAAAAElFTkSuQmCC

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;

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

 

You can get the latest available version of the timezone file is displayed using the GET_LATEST_TIMEZONE_VERSION function in the DBMS_DST package.

SELECT DBMS_DST.get_latest_timezone_version
FROM   dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

Prepare for the Upgrade

This step is where you check the impact of a time zone file upgrade, including the tables that will be affected by the upgrade. This is optional, but good to check. 

BfHBpy29n1GKkAAAAASUVORK5CYII=

Empty the default tables that hold the affected tables list and errors. If you are using custom tables, created with the CREATE_AFFECTED_TABLE, CREATE_ERROR_TABLE, CREATE_TRIGGER_TABLE procedures, then empty those instead. 

TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

DLUBkokEolEIpFIJBLJYdQGSiQSiUQikUgkEslh1AZKJBKJRCKRSCQSyWHUBkokEolEIpFIJBLJYdQGSiQSiUQikUgkEslh1AZKJBKJRCKRSCQSyWHUBkokEolEIpFIJBLJYdQGSiQSiUQikUgkEslh1AZKJBKJRCKRSCQSyWHUBkokEolEIpFIJBLJYdQGSiQSiUQikUgkEslh1AZKJBKJRCKRSCQSyWHUBkokEolEIpFIJBLJYdQGSiQSiUQikUgkEslh1AZKJBKJRCKRSCQSyWHUBkokEolEIpFIJBLJYdQGSiQSiUQikUgkEslh1AZKJBKJRCKRSCQSyWHUBkokEolEIpFIJBLJYdQGSiQSiUQikUgkEslh1AZKJBKJRCKRSCQSyT40+P8BQBvZtAwdpQ4AAAAASUVORK5CYII=

Find tables affected by the upgrade.

EXEC DBMS_DST.find_affected_tables;

BYj+3THoHKXuAAAAAElFTkSuQmCC

Check the results of the call.

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

When you’ve identified the affected tables and determined you are happy to continue, you can end the prepare phase.

EXEC DBMS_DST.end_prepare;

B0Yo9Y3RQfl3AAAAAElFTkSuQmCC

Upgrade the Time Zone File (non-CDB)

Start the database into upgrade mode.

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

Begin the upgrade to the latest version.

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

bP8w6qOLqUgAAAABJRU5ErkJggg==

Restart the database.

SHUTDOWN IMMEDIATE;
STARTUP;

Do the upgrade of the database file zone file.

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

 

Issues

For issues like below you need to follow the whole process to rectify the same – 

weHOid9Lp6OtwAAAABJRU5ErkJggg==

Reason for getting this error – 

T67r2DrStLwAAAAASUVORK5CYII=

If you check it shows the TZ version is  –

CcPHeXlKYzgAAAABJRU5ErkJggg==

Upgrade the Time Zone File (Multitenant)

In a multitenant environment, the time zone file upgrade must be performed in all containers. If you follow the example of the non-CDB instance, it will only be upgraded in the root container. What’s more, any new PDBs created from the seed will also use the old time zone file. To solve this we run the upgrade in the root container and all PDBs using the “catcon.pl” Perl script.

Create a script called “/tmp/upgrade_tzf.sql” with the following contents. 

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

Now we run the script in all containers using the “catcon.pl” Perl script. The “-n 1” flag is important. The script does a shutdown and startup. If the script is run in parallel, some of the PDBs will be processed at the same time as the root container, so their upgrade will be interrupted by the root container shutdown/startup. Running with a single worker keeps things sequential.

$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

Alternative options –

# Root only.
$ORACLE_HOME/perl/bin/perl \
    -I$ORACLE_HOME/perl/lib \
    -I$ORACLE_HOME/rdbms/admin \
    $ORACLE_HOME/rdbms/admin/catcon.pl \
    -l /tmp/ \
    -b upgrade_tzf \
    -c 'CDB$ROOT' \
    /tmp/upgrade_tzf.sql

# Root excluded.
$ORACLE_HOME/perl/bin/perl \
    -I$ORACLE_HOME/perl/lib \
    -I$ORACLE_HOME/rdbms/admin \
    $ORACLE_HOME/rdbms/admin/catcon.pl \
    -l /tmp/ \
    -b upgrade_tzf \
    -C 'CDB$ROOT' \
    /tmp/upgrade_tzf.sql

Check the “upgrade_tzf*.log” files for the result of the upgrade in each container.

Hope this document will help you to resolve the issues during Time zone upgrade.

Leave a Comment

Your email address will not be published. Required fields are marked *