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 220.127.116.11 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 18.104.22.168. 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
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/22.214.171.124/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 126.96.36.199:
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.
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.
- I created two additional PDBs in CDB1
- Then I run the database upgrade for CDB1 with PDB$SEED and two PDBs with
- The amount of archive/redo generated approximates at 5GB
- The amount of flashback logs generated sums up to less than 2GB
Again, your real-world numbers may vary.
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 188.8.131.52 if you have enabled Local Undo you can use Guaranteed Restore Points as well in PDBs to Flashback a PDB only.
See my blog post about Flashback Pluggable Database (available on Aug 30, 2017):