Guaranteed Restore Points and COMPATIBLE parameter

Guaranteed Restore Points and COMPATIBLE parameterA 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 we add a tiny change which prevents you from accidentally changing COMPATIBLE while your fallback relies on a Guaranteed Restore Point.

Quick example:

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 to due to
guaranteed restore points

Hence you have to drop the guaranteed restore point first:


But as you will change COMPATIBLE usually in the SPFILE with:


you’ll receive the ORA-38880 only during a failed startup. Your database won’t mount anymore.

In this case use the following workaround:

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 to 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.



Share this:

7 thoughts on “Guaranteed Restore Points and COMPATIBLE parameter

    • 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 🙁


  1. 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=”″ scope=spfile;

    Shutdown the database and issue a startup command. No muss, no fuss.

  2. 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.


Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required


I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.