DBA Hub

📋Steps in this guide1/7

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, as well as the time zone data based on new time zone file.

oracle miscconfigurationintermediate
by OracleDba
17 views
1

The Problem

Countries occasionally 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 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.
2

Check Current Time Zone Version

The view displays the zone file version being used by the database. This can also be queried from the table. It's worth reading Doc ID 1255474.1 before using this though. It can also be queried from the view. The latest available version of the timezone file is displayed using the function in the package. We can see there is a difference between the current and latest version of the time zone file, so we should consider performing an upgrade.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SELECT * FROM v$timezone_file;

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

SQL>

SELECT tz_version FROM registry$database;

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

SQL>

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

SQL>

SELECT DBMS_DST.get_latest_timezone_version
FROM   dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         31

SQL>
3

Prepare for the Upgrade

The prepare phase is where you check the impact of a time zone file upgrade, including the tables that will be affected by the upgrade. This phase is optional, but it makes sense to at least check what is going to happen. Use the procedure, passing in the file version you want to upgrade to. In this case we are selecting the latest version. We can now check the upgrade we are going to attempt. Notice the column is now populated. Empty the default tables that hold the affected tables list and errors. If you are using custom tables, created with the , , procedures, then empty those instead. Find tables affected by the upgrade. Depending on your use of columns, you might not have any. Check the results of the call. When you've identified the affected tables and determined you are happy to continue, you can end the prepare phase.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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;
/

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       31
DST_UPGRADE_STATE              PREPARE

SQL>

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

EXEC DBMS_DST.find_affected_tables;

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

EXEC DBMS_DST.end_prepare;
4

Upgrade the Time Zone File (non-CDB)

Individual tables or schemas can be upgraded, but in this example we use procedure to upgrade all affected tables in the database. Put the database into upgrade mode. This is not necessary from 21c onward. See the note at the bottom of this article. Begin the upgrade to the latest version. Restart the database. This is not necessary from 21c onward. See the note at the bottom of this article. Do the upgrade of the database file zone file. The views display the tables that are processed by the time zone file upgrade, and their current upgrade status. The following examples show how they could be used for a CDB an non-CDB database. Once the upgrade is complete, check the time zone file version being used.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
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;
/

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

SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_31.dat              31          0

SQL>


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         31
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL>
5

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. This example performs all the steps in all containers, without doing any investigation, and assumes everything will work with no additional intervention. You may not want to make this assumption on a database you care about. You can perform the manual steps described above, including the investigation during the prepare phase and dealing with upgrade failures, for each container individually. Create a script called "/tmp/upgrade_tzf.sql" with the following contents. The shutdown and startup operations are not necessary from 21c onward. See the note at the bottom of this article. 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. An alternative is to do the root container on its own, then do all the other PDBs in parallel by excluding the root container. Check the "upgrade_tzf*.log" files for the result of the upgrade in each container.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- Pre 21c 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 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;
/

$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

# 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
6

Getting the Latest Time Zone File

You can find latest time zone file for your database in this MOS note. - Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1) The note is quite long and confusing, but the patches are listed in the section "C.1.d) DST patches list". Each link takes you to a further note describing the patch and providing the relevant download links. The patches are applied using OPatch in the normal way, but this only makes the new time zone file available. Once the patch has been applied you can go through the process described above to apply it to a database.
7

21c Update

From Oracle 21c onward we can issue to the following command to allow time zone files to be updated without switching the database to upgrade mode. This allows the time zone file to be upgraded without any downtime. With this in place, follow the procedure above, but remove all database shutdown and startup operations. For more information see: - Upgrading the Time Zone Data Using the DBMS_DST Package - DBMS_DST - TIMEZONE_VERSION_UPGRADE_ONLINE Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
alter system set timezone_version_upgrade_online=true;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!