ORA-38774 whiles disabling archivelog mode

I got the following error while disabling archivelog mode on a database.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:

ORA-38774: cannot disable media recovery – flashback database is enabled

We will simulate and correct this error in this post.

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 12 14:31:10 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select log_mode,flashback_on from v$database;

LOG_MODE      FLASHBACK_ON
--------- ------------------
ARCHIVELOG   YES

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

mount the database

SQL> startup mount
ORACLE instance started.

Total System Global Area 7.5162E+10 bytes
Fixed Size                 15997288 bytes
Variable Size            1.7046E+10 bytes
Database Buffers         5.7982E+10 bytes
Redo Buffers              118218752 bytes
Database mounted.
SQL>

Disabling archivelog mode will give an error because FLASHBACK IS ON

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:

ORA-38774: cannot disable media recovery - flashback database is enabled

SOLUTION

This error occurred because flashback mode is enabled.
Disable flashback mode in the database and proceed to disable archivelog mode.

1. Drop restore point if any

SQL> Select name from v$restore_point;

no rows selected

If there is an existing restore point then drop it with the command below. Replace <RESTORE_POINT_NAME> with the restore point name.

SQL> drop restore point <RESTORE_POINT_NAME>;

2. Disable flashback mode

SQL> alter database flashback off;

Database altered.

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
TESTDB   NO

3. Proceed to disable archivelog mode.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     30
Current log sequence           32
SQL>
SQL>