ORA-38781: cannot disable media recovery - have guaranteed restore points

 Scenario:- While trying to disable archivelog, following error is reported.

ORA-38781: cannot disable media recovery - have guaranteed restore points

Solution:-

Current environment details :-


OS

OEL 6.9

DB SID

Ora12c

DB Version

12.1.0.2 EE



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

Lets see what oerr utility says about this ora error.

[oracle@server3 u01]$ oerr ora 38781
38781, 00000, "cannot disable media recovery - have guaranteed restore points"
// *Cause: An attempt was made to disable media recovery while there is at
//         least one guaranteed restore point.
// *Action: Drop all guaranteed restore points and then disable media recovery.


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

Check the restore points in database.

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME ,TIME from v$restore_point;

GUARANTEE_FLASHBACK_DATABASE   NAME                           TIME
------------------------------ ------------------------------ ---------------------------------------------
YES                            TESTING                        14-SEP-20 08.56.24.000000000 AM

Check the status of flashback.

SQL> select name,flashback_on from v$database;

NAME                           FLASHBACK_ON
------------------------------ ------------------
ORA12C                         RESTORE POINT ONLY

Now drop the restore point and try disabling the archivelog.

SQL> drop restore point TESTING;

Restore point dropped.

SQL> select name,flashback_on from v$database;

NAME                           FLASHBACK_ON
------------------------------ ------------------
ORA12C                         NO


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

SQL> startup mount
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  2927528 bytes
Variable Size             482346072 bytes
Database Buffers          138412032 bytes
Redo Buffers                5459968 bytes
Database mounted.



SQL> alter database noarchivelog;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG


No comments:

Post a Comment