Flashback Database is the best way to protect your Oracle Database during upgrade. It requires that you create restore points before the upgrade. If the upgrade fails, getting back to the starting point is easy and fast.
Enterprise Edition
It requires Enterprise Edition, but so does Data Guard. So it is not an issue here.
Guaranteed Restore Points
The restore points that you create to protect your Oracle Database upgrade should be guaranteed restore points. This ensures that you can always go back to the starting point. If you run out of disk space in your Fast Recovery Area (FRA), the database will halt and thus not jeopardize your fallback plan. The alternative to regular restore points is that the database will overwrite flashback logs to maintain operation, which is not desirable in this situation. You are relying on the restore point as your fallback.
Order of Creation
Always follow this order when creating restore points:
- All standby databases
- Primary database
The SCN of the restore points on the standby database must be the same or lower than on the primary database.
Imagine this scenario:
| SCN | Primary | Standby |
|---|---|---|
| 100 | Restore point created | |
| 105 | Restore point created | |
| 110 | Upgrade starts | |
| 200 | Upgrade fails |
To flashback:
| Primary | Standby |
|---|---|
| Flash back standby database to SCN 100 | |
| Flash back primary database to SCN 105 | |
| Open primary database with resetlogs. Creates a new incarnation as of SCN 105 | |
| Roll forward standby database from SCN 101 to 105 and follow new incarnation |
If the restore point on the standby database was created at SCN 106, then the primary database is already at a new incarnation (as of SCN 105). The standby database can’t follow and needs to be completely rebuilt.
Primary Database
When you upgrade with AutoUpgrade, it will create a guaranteed restore point for you. It is the default behavior.
The restore point created by AutoUpgrade protects the primary database only.
Standby Database
Always create the restore points on the standby database:
- As guaranteed restore points
- Manually; Don’t rely on restore point replication (19c new feature)
From the docs:
Restore points that are created on a primary database are automatically replicated to the standby database. The restore points created on the standby database are called replicated restore points. Irrespective of whether a restore point on the primary database is a guaranteed restore point or a normal restore point, the corresponding replicated restore point is always a normal restore point.
By design, all replicated restore points are not guaranteed; even if it was guaranteed on the primary database. When you protect a standby database during upgrade, you want the restore point to be guaranteed; thus, you must create them manually.
To create a restore point on the standby database, you must cancel redo apply:
SQL> alter database recover managed standby database cancel;
SQL> create restore point STBY_GRP guarantee flashback database;
SQL> alter database recover managed standby database disconnect from session;
If you forget to cancel redo apply, you run into:
SQL> create restore point STBY_GRP guarantee flashback database;
create restore point STBY_GRP guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'STBY_GRP'.
ORA-01153: an incompatible media recovery is active
Remove Restore Points
Don’t forget to remove the restore points after the upgrade. Do so when you have run your tests, and you are sure you won’t go back to the old release:
SQL> drop restore point STBY_GRP;
- The order of removal is not important
- It does not require downtime to remove the restore points
If you instruct AutoUpgrade to remove the restore point after upgrade (drop_grp_after_upgrade), it will happen only on the primary database. You must manually remove the restore points from the standby database.
Failure to remove the restore points will eventually fill up the FRA bringing the database to a complete halt.
Databases Are Fun 