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

 

5 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 ๐Ÿ™

      Cheers,
      Mike

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

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

Leave a Reply

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