Fallback Strategy: Flashback to Guaranteed Restore Points

You can attempt an Oracle database upgrade without having a fallback strategy. Of course you can. If you are brave enough. But I would recommend you this Fallback Strategy: Flashback to Guaranteed Restore Points. It is simple, easy, fast. And yes, it is an Enterprise Edition only feature. I have blogged about it in the past already but I realized it need some refresh.

Fallback Strategy: Flashback to Guaranteed Restore Points

One of the fastest way to revert from a big change (i.e. a database upgrade, an application upgrade, etc) is Flashback Database to a previously created Guaranted Restore Point.

Myth No.1 – Flashback Database must be enabled

I can’t exactly remember in which release we lifted this restriction. As far as I remember since Oracle Database 11.2.0.1 you don’t need to enable Flashback Database anymore in order to set a Guaranteed Restore Point. But your database must operate in Archive Log mode (SQL> archive log list). If it doesn’t, shutdown the database, start it in MOUNT mode only, enable archive log mode (SQL> alter database archivelog;) and OPEN it afterwards.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------
NO

Myth No.2 – To enable Flashback Database the database must be stopped

This isn’t true either since – I believe – Oracle Database 11.2.0.1. You can disable and enable Flashback Database at any time without changing the status of the database.

Myth No.3 – You can only rewind with Flashback Database

Nakamichi Dragon – THE tape deck everybody dreamed about when I was young and felt in love with HiFi

Again, this isn’t true. You can rewind and fast forward as often as you want. No limits. And of course you can’t go into the future or further backwards than your restore points allow.

Even when you did an ALTER DATABASE OPEN RESETLOGS you can use Flashback Database afterwards again.

And you can have more than one Guaranteed Restore Point. Therefore multiple flashback operations in all directions are possible. No limits.

Setting a Guaranteed Restore Point

Setting a guaranteed restore point is fairly simple. You must define the DB_RECOVERY_FILE_DEST_SIZE and the DB_RECOVERY_FILE_DEST upfront. And the directory (in my example: arch) must exist or created upfront.

SQL> alter system set db_recovery_file_dest_size=10G;
System altered.

SQL> alter system set db_recovery_file_dest='/u02/oradata/CDB1/arch';
System altered.

No shutdown is necessary to adjust these values.

You set a Guaranteed Restore Point:

SQL> create restore point GRP1 guarantee flashback database;
Restore point created.

And you can check the existing restore points:

SQL> select con_id, name, time, guarantee_flashback_database from v$restore_point order by 1,2;

    CON_ID NAME       TIME                             GUARANTEE
---------- ---------- -------------------------------- ---------
	 0 GRP1       21-AUG-17 02.03.07.000000000 PM  YES

Protect your SPFILE

It sounds odd – but it is really important. Protect your SPFILE’s contents. Just in case …

SQL> create pfile='/u01/app/oracle/product/12.1.0.2/dbs/initMYDB.ora' from spfile;

Using a Guaranteed Restore Point after an upgrade

After the database upgrade completes you can easily revert to the restore point you have created upfront. Start in the environment you have upgraded to, e.g Oracle Database 12.2.0.1:

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to restore point GRP1;
SQL> shutdown immediate;

Then switch back to your previous environment and take care that SPFILE and Password File must be accessible:

SQL> startup mount
SQL> alter database open resetlogs;

Please just ensure that you drop the restore point the sooner or later as otherwise you will run out of space on disk one day:

SQL> drop restore point GRP1;

The correct environment is very important

Please follow exactly these step-by-step instructions and issue the commands always in the correct environment. If you issue for instance the FLASHBACK DATABASE TO RESTORE POINT command in the wrong environment you may end up in trouble.

FLASHBACK DATABASE always in the correct environment

You must not change COMPATIBLE

If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database across a compatibility change of your database. Never.

And in addition, you can’t use the DBUA’s option to move data file locations (thanks, Krishnakumar).

How much Flashback data will be generated?

That is a question I can’t answer precisely as it has too many dependencies in a real world environment. But a typical guess estimates the amount of flashback database approximately 50% the amount of archive logs written during the database upgrade.

Just as an example which you’ll be easily redo by yourself by using this blog post about upgrading everything-at-once with our Hands-On Lab with CDB1:

1. I created two additional PDBs in CDB1
2. Then I run the database upgrade for CDB1 with PDB$SEED and two PDBs with -n 6
3. The amount of archive/redo generated approximates at 5GB
4. The amount of flashback logs generated sums up to less than 2GB

Again, your real-world numbers may vary.

Summary

Flashing back to a guaranteed restore point is fast and simple. I use it on a everyday basis when I test. You can use them not only for database upgrades but also of course to ensure a convenient SLA when you upgrade your applications in case of failure. You can use them in every Enterprise Edition database, no license fee is required. Your database must be in archivelog and you must define a DB_RECOVERY_FILE_DEST_SIZE and a DB_RECOVERY_FILE_DEST. No database restart is required. And – most important – COMPATIBLE must not be changed.

Finally, Guaranteed Restore Points work with Oracle Multitenant databases as well. When you approach an upgrade please set the Guaranteed Restore Point within the CDB$ROOT container. Beginning with Oracle Database 12.2.0.1 if you have enabled Local Undo you can use Guaranteed Restore Points as well in PDBs to Flashback a PDB only.

Further Information

• Fallback Strategy: Flashback to a Guaranteed Restore Point
• Fallback Strategy: Flashback Pluggable Database
• Fallback Strategy: Partial Offline Backups
• Fallback Strategies with Full Transportable Export/Import

–Mike