Someone asked me on the blog:

Can you revert an upgrade of the time zone file using Flashback Database?

And the short answer: Yes!

A time zone file update is a two-step process:

Time zone patch to Oracle Home
Time zone file upgrade of the database

Applying the patch to the Oracle Home is a simple process. The Oracle Home already has a lot of time zone files present (the previous versions), so you can safely add a newer version. It doesn’t hurt anything; you should generally never roll them off again. The time zone patch adds a file with time zone definitions (in $ORACLE_HOME/oracore/zoneinfo).

The second step is to upgrade the time zone file inside the database. This step will make changes to the data dictionary using the information from the time zone file in the Oracle Home. All the changes made in this step are made inside the database. There are situations when you want to revert the second step: Upgrading the time zone file in the database. For instance:

If the time zone file upgrade fails.
You performed a database upgrade to a new release and upgraded the time zone file afterward. Now you find a critical issue in the new release and decide to roll everything back.

How To Upgrade

Let’s give it a try and see how it works. I start with an Oracle Database running 19c, and I want to upgrade the time zone file and then revert the operation.

The database uses the default time zone file version (32 in Oracle Database 19c):

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32 	        0

I create a user and a table. Also, I insert data related to a time zone change in Yukon, Canada, that comes in with time zone file 35:

SQL> create user sales no authentication;
SQL> alter user sales quota unlimited on users;
SQL> grant create table to sales;
SQL> create table sales.t1 (
        c1 timestamp with time zone
     );   

SQL> insert into sales.t1 values(TIMESTAMP '2020-03-07 12:00:00.00 America/Whitehorse');
SQL> insert into sales.t1 values(TIMESTAMP '2020-03-08 12:00:00.00 America/Whitehorse');
SQL> insert into sales.t1 values(TIMESTAMP '2020-03-09 12:00:00.00 America/Whitehorse');
SQL> commit;

I download the time zone patch from Master Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1). Time zone files are cumulative, so I take the latest one, version 38. I apply it using opatch like any other patch to the Oracle Home. This is the result after applying the patch:

$ ./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.28
Copyright (c) 2022, Oracle Corporation.  All rights reserved.

...

Patch  34006614     : applied on Sat Jul 30 12:46:16 CEST 2022
Unique Patch ID:  24751709
Patch description:  "RDBMS - DSTV38 UPDATE - TZDATA2022A"
   Created on 27 Apr 2022, 04:16:52 hrs PST8PDT
   Bugs fixed:
     34006614

So far – no changes have been made to the database. It still runs with the old time zone file.

Before performing the time zone file upgrade, I create a restore point:

SQL> create restore point before_tz_upg guarantee flashback database;

Restore point created.

I start the time zone file upgrade by running the pre-upgrade check:

SQL> @utltz_upg_check.sql

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

It looks good, so go ahead with the actual upgrade. This step requires a maintenance window:

SQL> @utltz_upg_apply.sql

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv38 .
WARNING: This script will restart the database 2 times
...
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv38 .
INFO: The RDBMS DST update is successfully finished.

That’s it. I have upgraded the time zone file in the database.

SQL> select * from v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_38.dat 	     38 	 0

How To Fall Back

To answer the original question: Can I use Flashback Database to back out of a time zone file upgrade?

I restart the database in mount mode:

SQL> shutdown immediate
...
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
...
Database mounted.

I try to use Flashback Database

SQL> flashback database to restore point before_tz_upg;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

It works. The database time zone file is back at version 32:

SQL> select * from v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 	     32 	 0

I can also select my data again:

SQL> select * from sales.t1;
 
C1
-----------------------------------------------
07-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE
08-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE
09-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE

Don’t roll off the time zone patch from the Oracle Home. It makes no difference to the database. Once you have reverted the second step, the database will work like before the upgrade. If you insist on doing so, just ensure that no other database uses the time zone file that came in with the patch.

Conclusion

This proves that you can use Flashback Database to revert an upgrade of the database time zone file. Flashback Database is easy and effective. There is no need to roll off the time zone patch from the Oracle Home. It makes no difference to the database.

Tag: Time zone

Upgrade of Time Zone File and Flashback Database

How To Upgrade

How To Fall Back

Conclusion