Drop guaranteed restore points: ORA-38781

I cannot disable archive log for my Oracle database 12c, after creating a restore point with the guaranteed option. In this article, I will list the steps how to drop a guaranteed restore points.

Descriptions:

Oracle database EE 12.1.0.2

OS: Oracle Linux 6.5 (x64)

1. Disable the archive log, and the ORA-38781:

In the mount state

SQL> alter database noarchivelog;
 alter database noarchivelog
 *
 ERROR at line 1:
 ORA-38781: cannot disable media recovery - have guaranteed restore points
SQL> 

2. Check the current restore points:

a. Check the name of the restore point:

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME ,TIME from v$restore_point;
GUARANTEE_FLASHBACK_DATABASE   NAME           TIME
YES                            SETUP_01       07-NOV-19 03.24.57.000000000 PM

b. Check the flashback status:

SQL> select name,flashback_on from v$database;
NAME        FLASHBACK_ON
ORCL        RESTORE POINT ONLY

3. Drop the guaranteed restore point:

SQL> drop restore point SETUP_01;
Restore point dropped.
SQL> 

4. Check the status of the restore point:

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME ,TIME from v$restore_point;
no rows selected
SQL> select name,flashback_on from v$database;
NAME        FLASHBACK_ON
ORCL        NO
SQL> 

5. Disable archive log

SQL> alter database noarchivelog;
Database altered.
SQL> 

Conclusion

Disable archive log after dropping a guaranteed restore point