Upgrading the time zone file and timestamp with time zone data in a 12.2 Oracle database

Share on:

It is sometimes necessary to upgrade the time zone file and timestamp with time zone (TSTZ) data in an Oracle database, for instance when you upgrade to a new version of the database or when the rules for Daylight Saving Time (DST) change.

Since version 11.2, the update process was straightforward: one would simply download the proper scripts from this My Oracle Support (MOS) note and run them:

  • Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)

With version 12.2.0.1, the update procedure has changed drastically as the DST files are now included in the RDBMS home.  The procedure is also part of the documentation:

  • https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-12830F09-735F-4E25-B141-42B4776A110A

In the following article, I will show the steps to upgrade the time zone file and TSTZ data on a database that has just been upgraded from 11.2.0.4 to 12.2.0.1.

 

1. Check the latest version of the time zone file available in the Oracle home:

 

echo $ORACLE_HOME
 /appl/oracle/12201
 cd $ORACLE_HOME/oracore/zoneinfo
 head -2 readme.txt
 Current Structure version: 3
 Current Content Version  :26

According to the output the latest version available is 26.  This version number will be used in the next steps.

 

2. Check the status of the database:

SQL> conn / as sysdba
 Connected.
 SQL> set pagesize 9999 linesize 132
 SQL> col PROPERTY_NAME format a30
 SQL> col value format a20
 SQL>
 SQL> select banner from v$version;

BANNER
 --------------------------------------------------------------------------------
 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for Solaris: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_14.dat              14          0
SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
14

 

We see above that the version of the database is 12.2.0.1.0 and that the version of the time zone file is 14.

SQL> set pagesize 9999 linesize 132

SQL> col PROPERTY_NAME format a30

SQL> col value format a20

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES

  3  WHERE PROPERTY_NAME LIKE 'DST_%'

  4  ORDER BY PROPERTY_NAME;


PROPERTY_NAME                  VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

The previous query is important, and one should expect the following output:

DST_PRIMARY_TT_VERSION                   Same as “select VERSION from v$timezone_file”
DST_SECONDARY_TT_VERSION              0
DST_UPGRADE_STATE                                NONE

 

Do not continue if the output is different.

 

3. Understanding the upgrade process:

 

Step 1 (optional): start a prepare window

This step will show how much data must be updated in the database, give an estimate of how much time the upgrade will take, and show potential semantic errors.  All the errors should be fixed before running the upgrade.

 

Step 2: start an upgrade window

This step is the time when changes to the data will occur.

 

4. Avoid performance issues:

 

The recycle bin should be purged, and some session parameters set as per the following MOS notes:

  • Time Zone upgrade performed through DBUA takes more time in databases that have large amount of data impacted by new TZ files (Doc ID 2259734.1)
  • Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

 

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
purge dba_recyclebin;

 

5. Start a prepare window:

Use the version number (26) found in step 1 in the following statement:

SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_PREPARE(26);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.


SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

 

PROPERTY_NAME                  VALUE
------------------------------ --------------------

DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       26
DST_UPGRADE_STATE              PREPARE


We see that the time zone file will be upgraded from version 14 to 26 and that the upgrade is in the prepare state.

Next, the

DBMS_DST.FIND_AFFECTED_TABLES

procedure can be invoked to find the tables that will be impacted by the upgrade and to list the errors.The default tables

sys.dst$affected_tables and sys.dst$error_table

will be populated:

 

SQL> exec DBMS_DST.FIND_AFFECTED_TABLES;
PL/SQL procedure successfully completed.
SQL> select count(*) from sys.dst$affected_tables;

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

The errors can be fixed by looking at the documentation:

 

Finally, end the prepare window:

SQL> exec DBMS_DST.END_PREPARE;

A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES

  3  WHERE PROPERTY_NAME LIKE 'DST_%'

  4  ORDER BY PROPERTY_NAME;


PROPERTY_NAME                  VALUE
------------------------------ --------------------

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

 

6. Start an upgrade window:

The following steps will modify the database, so it is recommended to either take a backup of the database or to enable Flashback Database before continuing.

Before starting an upgrade window using DBMS_DST.BEGIN_UPGRADE, the database needs to be restarted in UPGRADE mode:

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.



Total System Global Area 2181038080 bytes

Fixed Size                  8604296 bytes

Variable Size            1258296696 bytes

Database Buffers          905969664 bytes

Redo Buffers                8167424 bytes

Database mounted.

Database opened.

SQL> set serveroutput on

SQL> exec DBMS_DST.BEGIN_UPGRADE(26);



PL/SQL procedure successfully completed.



SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES

  3  WHERE PROPERTY_NAME LIKE 'DST_%'

  4  ORDER BY PROPERTY_NAME;



PROPERTY_NAME                  VALUE

------------------------------ --------------------

DST_PRIMARY_TT_VERSION         26

DST_SECONDARY_TT_VERSION       14

DST_UPGRADE_STATE              UPGRADE

DBMS_DST.BEGIN_UPGRADE will upgrade the system tables (owner by SYS, DBSNMP, etc.) that contain TSTZ data and will mark user tables (containing TSTZ data) with UPGRADE_IN_PROGRESS in the ALL_TSTZ_TABLES view:

col OWNER format a30
col TABLE_NAME format a30
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            ACLMVREFSTAT$                  NO
SYS                            ALERT_QT                       NO
SYS                            AQ$_AQ_PROP_TABLE_L            NO
SYS                            AQ$_AQ_PROP_TABLE_S            NO
SYS                            AQ$_DEQUEUE_LOG_PARTITION_MAP  NO
...
GSMADMIN_INTERNAL              AQ$_CHANGE_LOG_QUEUE_TABLE_L   YES
GSMADMIN_INTERNAL              AQ$_CHANGE_LOG_QUEUE_TABLE_S   YES
...

Restart the database in normal mode:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.



Total System Global Area 2181038080 bytes

Fixed Size                  8604296 bytes

Variable Size            1258296696 bytes

Database Buffers          905969664 bytes

Redo Buffers                8167424 bytes

Database mounted.

Database opened.

Truncate the sys.dst$error_table and sys.dst$trigger_table error and trigger tables before starting the upgrade process:

 

SQL> truncate table sys.dst$error_table;

Table truncated.

SQL> truncate table sys.dst$trigger_table;

Table truncated.

Then, upgrade the TSTZ data in all user tables by invoking DBMS_DST.UPGRADE_DATABASE:

 

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.



SQL> set serveroutput on

SQL> VAR numfail number

SQL> BEGIN

  2     DBMS_DST.UPGRADE_DATABASE(:numfail,

  3              parallel                  => TRUE,

  4              log_errors                => TRUE,

  5              log_errors_table          => 'SYS.DST$ERROR_TABLE',

  6              log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',

  7              error_on_overlap_time     => TRUE,

  8              error_on_nonexisting_time => TRUE);

  9     DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

 10  END;

 11  /

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

Failures:0

PL/SQL procedure successfully completed.
SQL> SELECT * FROM sys.dst$error_table;
no rows selected

The upgrade took just a few seconds since there is almost no user data to be modified in my database. The errors can be fixed by looking at the documentation:

Finally, end the prepare window:

 

SQL> BEGIN

  2     DBMS_DST.END_UPGRADE(:numfail);

  3     DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

  4  END;

  5  /

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

7. Check the status of the database:

SQL> select * from v$timezone_file;


FILENAME                VERSION     CON_ID

-------------------- ---------- ----------

timezlrg_26.dat              26          0

SQL> select TZ_VERSION from registry$database;


TZ_VERSION

----------

        14


SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;


PROPERTY_NAME                  VALUE
------------------------------ --------------------

DST_PRIMARY_TT_VERSION         26
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE


As you see above, v$timezone_file and registry$database don’t report the same information.  This is because registry$database is only populated by the Pre-Upgrade information tool.  It can be manually updated as shown in this MOS note:

  • Different Time Zone Version In Registry$Database And V$Timezone_file (Doc ID 1255474.1)

 

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.
SQL> commit;
Commit complete.
SQL> select TZ_VERSION from registry$database;
TZ_VERSION

----------

26

SQL> exit

 

This completes this blog post on upgrading the time zone file and timestamp with time zone data in a 12.2 Oracle database.

Share on:

More from this Author

From The DBA Perspective: Highlights from Oracle OpenWorld 2017

From The DBA Perspective: Highlights from Oracle OpenWorld 2017

Again this year Oracle OpenWorld was packed with too many keynotes, sessions and activities to choose from. In my very first blog post, I would like ... Read More

Back to Top