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

 

Share this: