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 184.108.40.206 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 220.127.116.11. 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/18.104.22.168/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 22.214.171.124:
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.
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:
- 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 126.96.36.199 if you have enabled Local Undo you can use Guaranteed Restore Points as well in PDBs to Flashback a PDB only.
- 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
Also when we follow this method, we are not suppose to move datafile location during the upgrade. (DBUA has option to move datafile to different location during upgrade)
Mike are you saying its not required to run alter database flashback on to use flashback a DB to a restore point guaranteed or not.
yes – try it out by yourself 🙂 See my Myth No.1 🙂
I wanted to test a downgrade from 12.2 to 12.1. The 12.1 binaries are still present. Is it a correct assumption that I don’t need to run 12.2 patches deinstall script before doing the flashback, since it will be rolled back too once we perform the flashback to the GRP created while the DB was still in 12.1?
Basically, I just need to ensure that I have my spfile/init.ora/password file in 12.1 and if it’s Oracle Restart, I need to update the Oracle Home back to 12.1 using srvctl after performing the flashback, right?
your assumption is correct.
Btw, here’s a totally different topic but related to 12.2 upgrade. I have already performed the following:
1. Installed 12.2 GI and DB (silently)
2. Applied latest patches
Now, I couldn’t find the steps (couldn’t find in online docs and MOS note) how to upgrade my 12.1 GI to 12.2 using the GI software I installed silently.
I ran rootupgrade.sh – but it only advised me to run gridSetup.sh. If I run gridSetup.sh and choose the upgrade option, I’m thinking it might overwrite my existing 12.2 GI installation. I’ve googled already and it seem I needed to run roothas.pl script, but just want to confirm the correct command for an upgrade.
are you running Single Instance HA (Oracle Restart)? Then roothas.pl is the right script.
When we upgrade for 188.8.131.52 to 12.2 , the compatible parameter will be changed. Will flashback not work in this case?
COMPATIBLE never gets changed with two exceptions:
a) you changed it
b) we need to change it as the target version does not support the source compatible, e.g you have COMPATIBLE=10.1.0 in an 184.108.40.206 database and upgrade to 19c
Since 12.2, we don’t allow you to change COMPATIBLE when you have a GRP defined. Try it out – you get an error.
Thanks for the feedback.
This limits us from trying out multiple upgrades followed by testing the upgraded database with new features and flashing back the database.
you just need to make sure that your CDBs have equal COMPATIBLE values when you create them. I recommend this to all my customers until the point when they have everything on a homogeneous release, e.g. 19c. This way you allow all sorts of fallback possibilities. You just need to make sure to change the COMPATIBLE setting in DBCA when you create a new database (in the Advanced Parameter section pretty late in the workflow).
Thanks for your patience with me.
I am upgrading a 220.127.116.11 database to 12.2 and it is a non-CDB database. After the upgrade changing the compatible from 18.104.22.168 to 22.214.171.124 throws a error that database cannot be started due to GRP that was taken before the upgrade.
Yep – this is expected.
You need to drop the GRP at first. It’s a “safety net” built in since 126.96.36.199 to prevent you changing COMPATIBLE and destroying your fallback. You need to drop the GRP at first, then you can lift COMPATIBLE.
We have 12.2 rac with project using GRP to move to and fro to different application state. Everythings working fine with flashback’s until we hit a problem when flsahback to one of the GRP failed complaining on archvielog not found on disk. My assumption is rman doesnt remove archivelogs which are required by GRP after backups. But, its not true, the archivelog was backed to NetBackup and now out of retention (we couldnt restore it). Additionally, backup log for that archive lgo ony shows archhivelog being backed up and no messages of deletion. Generally for any archivelog which is required by GRP, rman prints message that its unable to delete. Now, I question how reliable are GRP’s and retention of any archvielogs required for it
For the upgrade itself it fully is. But I’ve had a similar discussion with another customer these days. And RMAN would flashback seamless in this case as it is able to restore the archives it did backup before. But the standard SQL process doesn’t find them on disk anymore, and hence will fail.
So yes, fully reliable – but you may have to switch to RMAN to trigger the flashback instead if the archives have been cleaned up in between.
Why do you advise to use another binary to flashback and not the one which used to create the restore point?
I do not see the reason why the flashback operation should take place under the post upgrade environment (19c in my case). I do it all under the pre upgrade environment 12c (except shutdown under 19c). And not by surprise it works (as well)…
Thanks and cheers, Andreas
because this is the approach which always works – yours may fail occasionally as customers found out.
Hello again – thanks, fair enough. Empiric answers sometimes are as good than technical. On the other hand: which binary does Oracle use to create the restore point when I use the autoupgrade functionality to do it? upg1.restoration=yes
I havn’t tried this yet… Thanks, Andreas
Oracle uses the source binary to create the GRP.
I expected this. Thanks. And which to flashback? Cheers, Andreas
Same as I describe in my post 🙂
We are using flashback database as follows. Before custom code deployment to DB, create GRP, then deploy code. If code deploy fails, flashback to GRP. This has worked fine for 2 years. Now we are in production and it has been raised that the “open database reset logs” after flashback will mean we cannot do point in time recovery. Can this be correct ? I assume we can recovery the database to a backup before the GRP and apply logs forward. The GRP will never have happened so we can get to PITR before GRP. If PITR is point after GRP, we could flashback database to that point. So do we lose anything ? If we did two GRPs after last full backup, can we still get to any point in time ? If we drop the GRPs so logs are removed, can we restore backup before GRP and apply logs to point past GRP ? Thanks in advance.
I agree – you can do a PITR when you go “before the GRP”. I don’t see any reason why this shouldn’t work.
Hi Mike – I’m preparing to upgrade a 188.8.131.52.190716 multitennant database (CDB with single pdb) to 19.10 using the autoupgrade utility. The upgrade testing has been successful, but fallback testing using the GRP as described in this post is failing with a ORA-00600 [kccocx_01] error.
Is a fallback to 184.108.40.206 from 19.10 supported using a GRP supported? I’ve been reading the GRP in a PDB wasn’t supported until 12.2, so I’m wondering if this is the source of my problem.
we have seen the exact same error when Daniel tested upgrades of 50 PDBs, and then wanted to go back to the GRP. The “flashback PDB” topic does not apply here as the entire environment (CDB with all PDBs= will be flashed back. It seems to be a Multitenant/Flashback issue. But it has nothing to do with AutoUpgrade. When you query v$restore_point, you will see the GRP – and you can try to flashback manually. But you may get the exact same error (I fear).
You may please need to open an SR with Oracle Support.
Thanks Mike. I’ve decided to take a full backup just prior to the upgrade and do a PITR in the event I need to downgrade.
However, I am curious about your statement “try to flashback manually”. Isn’t there only one method to execute the flashback? Or is there an alternative method to the one you describe in this blog post?
AutoUpgrade allows you to trigger the flashback. But the restore point stays in the database until it gets removed and it is independent of AutoUpgrade. Hence, you can flashback manually OR in AU.
I am currently looking at a migration and an upgrade.
Current set up is RAC 12c with 220.127.116.11 and 18.104.22.168 databases
Destination is RAC19c.
This is going to new hosts running OEL 7.8
The plan is to restore to the new hosts under the same RDBMS versions but Grid is 19c
Start them up and test connectivity etc. Once complete, shut them down and upgrade to 19c.
As there are 12 databases to migrate the addition to this is that some will take days to restore as they are upwards of 30Tb, so it was suggested that after the first initial restore, to rollback via GRP and keep the database in a state of recovery until the go live date – is that possible.
In addition, can we run an 11.2.04 database in ASM with AFD configured as all of that work has been done in preparation.
Thanks in advance
I haven’t tried it but technically this may work. When you try to open after flashing back to a GRP, you need to use OPEN RESETLOGS as afterwise the database stays closed. So please check and try it out.
But you could even refresh incremental backups as far as I’m aware of.
Thanks for responding Mike. We have yet to embark on this project but appreciate the comment that it might work. Fingers crossed
This helped tremendously, and it’s just so simple. I had been trying to use java/autoupgrade.jar to accomplish a rollback days after a successful upgrade. Das wäre mit Kanonen auf Spatzen schießen.
Thank you Mike.
I have just successed to test your advise/strategy of fallback.
1) Create guarantte restore point on both primay and seconday
2) Upgrade from 22.214.171.124 to 19.10 RU
Also, its about RAC database with RAC dataguard
3) Test fallback with Flashback strategy
In this case, the flashback rewind the old version and Datagurad resync without any problem, take acare of wallet opening on RAC environnment 🙂 long story… )
4) Again re-Upgrade to perform a perfect migration based on the lesson (Problem facing on the firt test)
5) All things are ok, and the dataguard resync again cucessfully.
Many Thanks Mike
Thanks for the feedback, Yahya!
Thank you for this. We have a 126.96.36.199 RAC upgrade coming up to 12.1, with dataguard in tow.
If the upgrade fails say mid way do we still flashback from the 12.1 binaries?
Also re the restore points – I am assuming that we need to set up the restore point on the standby before the one on primary?
And should we need to use the restore point, we use the one on the primary first and then the standby – then resume recovery?
Thanks in advance,
please see here:
and the demo:
Slides are here (Virtual Classroom Seminar 11):
does database flashback rewinds table/index stats either?
it rewinds everything – whatever you did.
It turns back on SCN – and stats runs are transactions, too.
Hi Mike. Does Flashback to guarantee restore point also works if I run the scripts to upgrade timezone file on the new version after upgrade database?
yes, it does.