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
