A while ago I blogged about Fallback Strategy: Flashback Database to Guaranteed Restore Points. I included a recommendation that you must not change the COMPATIBLE setting. But I should have been a bit more clear and precise about Guaranteed Restore Points and COMPATIBLE parameter settings.
Guaranteed Restore Points and COMPATIBLE parameter
First of all, you can’t use FLASHBACK DATABASE to a Guaranteed Restore Point when you changed to COMPATIBLE parameter. This was a common pitfall in older Oracle releases.
But with Oracle Database 12.2.0.1 we add a tiny change which prevents you from accidentally changing COMPATIBLE while your fallback relies on a Guaranteed Restore Point.
Quick example:
CREATE RESTORE POINT grp GUARANTEE FLASHBACK DATABASE; Restore point created.
In Oracle Database 12.2 you’ll get the following error when you try to advance COMPATIBLE:
ORA-38880: Cannot advance compatibility from 11.2.0.4.0 to 12.2.0.0.0 due to
guaranteed restore points
Hence you have to drop the guaranteed restore point first:
DROP RESTORE POINT grp;
But as you will change COMPATIBLE usually in the SPFILE with:
ALTER SYSTEM SET compatible='12.2.0.1' SCOPE=SPFILE;
you’ll receive the ORA-38880 only during a failed startup. Your database won’t mount anymore.
In this case use the following workaround:
SQL> ALTER SYSTEM SET compatible='12.2.0' SCOPE=SPFILE; System altered. SQL> startup force ORACLE instance started. Total System Global Area 1577058304 bytes Fixed Size 8621136 bytes Variable Size 503317424 bytes Database Buffers 1056964608 bytes Redo Buffers 8155136 bytes ORA-38880: Cannot advance compatibility from 11.2.0.4.0 to 12.2.0.0.0 due to guaranteed restore points SQL> create pfile from spfile; File created. SQL> shutdown immediate ORA-01507: database not mounted
Then edit your PFILE in $ORACLE_HOME/dbs – and restart the database using this PFILE.
Then drop the restore point first – and afterwards you can advance COMPATIBLE.
–Mike
WOW! Thanks heaps for this one, Mike!
No clue this would be a problem but it makes total sense!
hi mike good that there is some protection now 😉
but the workaround is still the same as what i encountered while going from 10.2 to 11.2 😉 http://pfierens.blogspot.be/2012/10/upgrade-to-11203-some-grains-of-sand.html
still you have to manually recreate the spfile,
Hi Philippe,
you are (unfortunately) 100% correct – and I agree with you. The basic issue is that, as soon as you use, SCOPE=SPFILE you can basically do all sorts of nonsense. Nobody protects you from doing this (e.g. SGA_TARGET=10000000G). And this happens here as well. So yes, there’s sort of protecting against breaking your fallback. But you still have to rebuild your PFILE 🙁
Cheers,
Mike
Love the blog Mike.
In this case, you can change the spfile. This is because the instance started. It was the mount attempt that failed. Since the instance is started, you can issue the alter system set compatible command, using the scope=spfile parameter. The spfile will be changed. Then you just shutdown and restart.
Notice in your output, the instance started just fine – it was the attempt to mount the database that failed. I just tested this on 12.2, and it worked fine.
So, after the ORA-38880, the instance is still up. You simply issue an alter system set compatible=”11.2.0.0.0″ scope=spfile;
Shutdown the database and issue a startup command. No muss, no fuss.
Thanks Robert – good point!
Cheers,
Mike
Hi Mike. Nice post about FB DB. I have a doubt. In order to flashback changes on primary database protected with physical standby. Do I need to create garantee restore point on standby first? and hours later Do I need to cancel mrp on standby and flashback primary database ?
Williams,
good question.
Often a GRP on the standby is not necessary as you can flashback it far back as long as the archive logs are present and not cleaned up. This means:
You delete SCOTT accidentally on PRIM, then you FLASHBACK the standby and export SCOTT and reimport it on PRIMARY. The standby will synch automatically after the flashback operation – and the SCOTT schema will be propagated again to the standby.
But you don’t need a GRP on the standby in addition when you FLASHBACK the primary. The standby is intelligent to synch again after you flashed back the primary.
Thanks,
Mike
Nice blog. it worked for me.
Thanks.
Luckily I found your blog, I’ve tried to change the compatible from 11.2.0.4 to 19.0.0 on EXACS, in the physical standby and primary. I dropped the restore point first in the Physical but you need to stop and mount the database, after that I’ve followed the process.
Thank you very much!
Thanks a lot, Lizzeth!
Cheers,
Mike
Hi Mike. First of all, Your blog is awesome and i really admire you. I want to ask a question. Is it possible to flashback database to a time (not to a GRP) in flashback time retention period but before the point of time of changing COMPATIBLE parameter?
I have encountered that issue, i need an accidentally deleted table value. Can i rewind my physical standby to time before delete operation that took place just before we changed compatible parameter?
Thanks.
Hi Osman,
thanks for your feedback.
When your database has FLASHBACK ON (check in v$database) then you can flashback to any point in time being still covered in your flashback logs. Since this is dependent on your disk space assigned to the FRA, you may need to check the max flashback time.
SELECT to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') FROM v$flashback_database_log;But if you change COMPATIBLE, there is no way back beyond this change. We prevent changing COMPATIBLE since 12.2.0.1 when you have a GRP. Since you don’t have a GRP, there is no blocking – and hence, you can go backwards exactly to the point AFTER you changed COMPATIBLE if your flashback logs allow you that span.
Thanks,
Mike