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

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: