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

2 thoughts on “Flashback and Data Guard

  1. Hi, Daniel. Excellent topics and recommendations put it in a simple words. Good job.
    I have a question. After upgrade and before go live Can I change or upgrade the timezone version file and still does a flashback database in case of necessary?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s