ORA-38880: Cannot Advance Compatibility From 12.1.0.2.0 To 12.2.0.1.0 Due To Guaranteed Restore Points

PROBLEM:

After successful db upgrade, I tried to update the compatible parameter and restart the database. I got below error.

 

 

 

SQL>  alter system set compatible="12.2.0.1" scope=spfile;

 

System altered.

 

 

 

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area 1.4663E+10 bytes

Fixed Size                 15697000 bytes

Variable Size            8455723928 bytes

Database Buffers         6140461056 bytes

Redo Buffers               51404800 bytes

ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 12.2.0.1.0 due to guaranteed restore points

 

 

 

 

SOLUTION:

The error is because, I have enabled flashback and created a guaranteed restore point, before the upgrade. So while trying to update the compatible parameter, it is not allowing. Because ,once compatible parameter is changed, we can’t downgrade.

To fix it, we need to drop the restore point. Follow below steps:

 

Set to the old existing compatible.

 

SQL> alter system set compatible="12.1.0.2.0" scope=spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area 1.4663E+10 bytes

Fixed Size                 15697000 bytes

Variable Size            8455723928 bytes

Database Buffers         6140461056 bytes

Redo Buffers               51404800 bytes

Database mounted.

Database opened.

 

Check the flashback restore point and drop it:

 

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;

 

GUARANTEE_FLASHBACK_DATABASE     NAME

-------------------------------- -----------------------------

YES                              GRP_1487689238734

 

SQL> select flashback_On from v$database;

 

FLASHBACK_ON

------------------

YES

 

SQL> alter database flashback off;

 

Database altered.

 

SQL> select flashback_On from v$database;

 

FLASHBACK_ON

------------------

RESTORE POINT ONLY

 

SQL> drop restore point GRP_1487689238734;

 

Restore point dropped.

 

SQL>  select flashback_On from v$database;

 

FLASHBACK_ON

------------------

NO

 

 

 Now update the compatible parameter in spfile and restart

 

SQL> show parameter comp

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cell_offload_compaction              string      ADAPTIVE

compatible                           string      12.1.0.2.0

db_index_compression_inheritance     string      NONE

nls_comp                             string      BINARY

noncdb_compatible                    boolean     FALSE

plsql_v2_compatibility               boolean     FALSE

SQL> alter system set compatible="12.2.0.1" scope=spfile;

 

System altered.

 

SQL> startup force;

ORACLE instance started.

 

Total System Global Area 1.4663E+10 bytes

Fixed Size                 15697000 bytes

Variable Size            8455723928 bytes

Database Buffers         6140461056 bytes

Redo Buffers               51404800 bytes

Database mounted.

Database opened.

SQL> show parameter comp

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cell_offload_compaction              string      ADAPTIVE

compatible                           string      12.2.0.1

db_index_compression_inheritance     string      NONE

nls_comp                             string      BINARY

noncdb_compatible                    boolean     FALSE

plsql_v2_compatibility               boolean     FALSE