DBMS_DST Package updating Database Time Zone File

Users normally see below message in alert.log file when upgrading the Database. You can use DBMS_DST package to upgrade the time zone file.

Database is using a timezone file older than version xx

The timezone has 2 file i.e large file and small file and these are located under oracore/zoneinfo under ORACLE HOME directory.

• The large versions are designated as timezlrg_version_number.dat., this file has all the time zones defined in the database

• The small versions are designated as timezone_version_number.dat, this file has all most commonly used time zones

You can run query V$TIMEZONE_FILE to identify time zone file version used by the database.

SQL> select filename, version, from v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_26.dat 26

You can get the primary and secondary time zone versions details from DATABASE_PROPERTIES

SQL> column property_name format a40

SQL>column property_value format a30

SQL>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

After upgrade you can check upg_summary.log to identify Database time zone version and current release time zone version

Oracle Database Release 19 Post-Upgrade Status Tool 04-30-2021 11:07:0
Database Name: TDB1
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.10.0.0.0 00:09:54
JServer JAVA Virtual Machine VALID 19.10.0.0.0 00:01:12
Oracle XDK UPGRADED 19.10.0.0.0 00:00:38
Oracle Database Java Packages UPGRADED 19.10.0.0.0 00:00:05
Oracle Text UPGRADED 19.10.0.0.0 00:00:23
Oracle Workspace Manager UPGRADED 19.10.0.0.0 00:00:22
Oracle Real Application Clusters OPTION OFF 19.10.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.10.0.0.0 00:00:51
Oracle Multimedia UPGRADED 19.10.0.0.0 00:01:47
Datapatch 00:01:41
Final Actions 00:01:44
Post Upgrade 00:00:16

Total Upgrade Time: 00:17:31

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time: [0d:0h:20m:20s]

You can also get latest time zone version using below query

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
32

To upgrade time zone follow below steps

1. Shutdown the database and startup in upgrade mode

SQL> Shutdown immediate
SQL> Startup upgrade

2. Prepare the time zone version upgrade using BEGIN_PREPARE procedure

SQL> set serveroutput on

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

/

PL/SQL procedure successfully completed.

The below query will show the upgrade version we are attempting to

SQL> column property_name format a40

SQL>column property_value format a30

SQL>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 32

DST_UPGRADE_STATE PREPARE

3. You can find the affected tables by running below queries

SQL> exec DBMS_DST.find_affected_tables;
PL/SQL procedure successfully completed.

SQL> select count(*) from sys.dst$affected_tables;
COUNT(*)
----------
0
SQL> select * from sys.dst$error_table;
no rows selected

4. Use END_PREPARE procedure to end the prepare stage and begin upgrade using BEGIN_UPGRADE procedure

SQL> EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> 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_UPGRADE(l_tz_version);

end;

/

l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

5. Open in normal mode and upgrade database time zone file using DBMS_DST.UPGRADE_DATABASE

SQL> shutdown immediate
SQL> startup

sql> set serveroutput on

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

/

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

Verify the time zone upgrade by running below query

SQL> column property_name format a40

SQL>column property_value format a30

SQL>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 32

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

Thanks & Regards,

https://oracleracexpert.com, Oracle ACE