Upgrade of Time Zone File and Flashback Database

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.

Flashback and Data Guard

When you upgrade your Oracle Database, you should also prepare for fallback. Data Guard plays nicely together with Flashback Database. You don’t have to rebuild the standby database following a Flashback Database if you follow the correct procedure.

When To Use Flashback Database

Flashback Database is easy to use, and it is our preferred fallback mechanism. However, Flashback Database also means data loss because the database is rewinded. So, Flashback Database is only useful before you go live on the new release.

A comparison between Flashback Database and downgrade.

Flashback Database Downgrade
Data loss No data loss
Use before go-live Use after go-live
After flashback, database is identical with before-upgrade state After downgrade, database is compatible with before-upgrade state, but not identical
Requires Enterprise Edition Works in all editions
Preferred method

If your Oracle Database is running Standard Edition 2, you are not licensed to use Flashback Database. Instead look at partial offline backup.

General Considerations

It is a requirement that you have not changed the COMPATIBLE parameter. As soon as you change COMPATIBLE after upgrade, you can no longer use Flashback Database. If you have already changed the COMPATIBLE parameter, you must use other fallback methods like Data Pump or RMAN restore.

The old release Oracle Home must still exist on primary and standby hosts. I recommend that you keep them until you are absolutely sure you will not flashback (nor downgrade) your Oracle Database.

When you flashback your database, I recommend that you leave your Grid Infrastructure at the new release. Don’t downgrade Grid Infrastructure as well. Ideally, in a previous maintenance window, you upgraded Grid Infrastructure to the new release in advance. Thus, you know it can handle the old release of the database. Save yourself the added complexity of also downgrading Grid Infrastructure.

Data Guard broker does not support going back to a previous version. You must disable the broker during flashback and afterward create a new configuration or restore broker configuration files from the old release.

The following works for Data Guard configuration with a physical standby database. Other considerations and a different procedure apply if you have a logical standby database.

Flashback

To flashback a database protected by Data Guard the following applies:

  • You must have a restore point on primary and all standby databases.
  • First, create restore points on standby database, then on primary database. The SCN of the restore points on the standby database must be lower than the SCN of the restore point on the primary database.
  • I recommend using guaranteed restore points when upgrading.
  • Don’t rely on restore point propagation. Manually create the restore points on the standby database.
  • The order of the operation is important: First, handle standby databases (order of standby databases is not important), then primary database.

On YouTube, we have a good introduction to using Flashback Database as fallback. You can also flip through the slides.

How To Flashback With AutoUpgrade

The following assumes:

  • You manually created a restore point on the standby database.
  • AutoUpgrade created a restore point on the primary database (default, controlled by restoration).
  • AutoUpgrade was configured to keep the restore point after upgrade (default, controlled by drop_grp_after_upgrade).
  • $NEW_ORACLE_HOME refers to the release you were upgrading to (e.g. 19c) and $OLD_ORACLE_HOME refers to the release you upgraded from (e.g. 12.1.0.2).

The procedure:

  1. The environment in your session is set to the new Oracle Home.
  2. Stop Data Guard broker on all databases:
    alter system set dg_broker_start=false scope=both sid='*'
    
  3. Stop standby database (all instances, if RAC).
  4. Flashback primary database using AutoUpgrade. nn is the AutoUpgrade job id that executed the upgrade:
    java -jar autoupgrade.jar -config ... -restore -jobs nn
    
    AutoUpgrade handles everything on primary database, like
    • /etc/oratab
    • Grid Infrastructure downgrade
    • SPFile
    • Etc.
  5. Start standby database in mount mode (only one instance, if RAC).
  6. Flashback the standby database:
    flashback database to restore point ...
    
  7. Shut down the standby database.
  8. Downgrade the standby database clusterware configuration. Grid Infrastructure will now start the database in the old Oracle Home (in this example it is 12.1.0.2):
    $NEW_ORACLE_HOME/bin/srvctl \
      downgrade database \
      -db $ORACLE_UNQNAME \
      -oraclehome $OLD_ORACLE_HOME \
      -targetversion 12.1.0.2
    
  9. Start the standby database.
  10. Start Data Guard broker on primary and standby database
    • Either restore broker config files from old release and start Data Guard broker.
    • Or, start Data Guard broker and recreate the configuration.
  11. : Ensure that your Data Guard configuration works. Use validate database command in Data Guard broker on all databases and ensure they are ready for switchover.
  12. Optionally, test your Data Guard by doing a switchover.
  13. Remember to drop the guaranteed restore points on all databases.

That’s it!

Demo

Flashback of a CDB running RAC and Data Guard environment:

Other Blog Posts in This Series