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.
|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|
If your Oracle Database is running Standard Edition 2, you are not licensed to use Flashback Database. Instead look at partial offline backup.
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.
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
- AutoUpgrade was configured to keep the restore point after upgrade (default, controlled by
- $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. 184.108.40.206).
- The environment in your session is set to the new Oracle Home.
- Stop Data Guard broker on all databases:
alter system set dg_broker_start=false scope=both sid='*'
- Stop standby database (all instances, if RAC).
- Flashback primary database using AutoUpgrade. nn is the AutoUpgrade job id that executed the upgrade:
AutoUpgrade handles everything on primary database, like
java -jar autoupgrade.jar -config ... -restore -jobs nn
- Grid Infrastructure downgrade
- Start standby database in mount mode (only one instance, if RAC).
- Flashback the standby database:
flashback database to restore point ...
- Shut down the standby database.
- Downgrade the standby database clusterware configuration. Grid Infrastructure will now start the database in the old Oracle Home (in this example it is 220.127.116.11):
$NEW_ORACLE_HOME/bin/srvctl \ downgrade database \ -db $ORACLE_UNQNAME \ -oraclehome $OLD_ORACLE_HOME \ -targetversion 18.104.22.168
- Start the standby database.
- 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.
- : Ensure that your Data Guard configuration works. Use
validate databasecommand in Data Guard broker on all databases and ensure they are ready for switchover.
- Optionally, test your Data Guard by doing a switchover.
- Remember to drop the guaranteed restore points on all databases.
Flashback of a CDB running RAC and Data Guard environment:
2 thoughts on “Flashback and Data Guard”
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?
It is a good question. I decided to write a blog post about it: https://dohdatabase.com/2022/08/01/upgrade-of-time-zone-file-and-flashback-database/
Short answer: Yes!