Monday, February 21, 2022

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

3 comments:

  1. I'm what I call an "easy chair metropolitan and regional organizer." Some of you perusing this article might know me just from seeing me cruise all over in my separated Toyota. Mobile Mechanic Columbus Ohio

    ReplyDelete
  2. Palm Ocean side Region is its own help region yet takes care of into the bigger Tri-Rail adjusted Tri-District Region.) Mobile Truck Repair Cypress

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete