ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB.

Here I came a cross error - ORA-01143.

I followed the below steps.

1. SQL> select name from v$database;

NAME
------------
UATB

2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME;

NAME
---------
UATB_COPY

Here I'm going to restore the database to the above restore point.

NOTE: The flashback database restore has to be done in MOUNT stage of the database.

SQL> select name from v$database;

NAME
---------
UATB

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

SQL> startup mount

ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             167775108 bytes
Database Buffers          436207616 bytes
Redo Buffers                7135232 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO RESTORE POINT UATB_COPY;

Flashback complete.

SQL> alter database flashback off;

Database altered.

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

NOTE: We have guaranteed flashback restore point. Hence we have received the above error. Lets drop the existing restore point and try the same.


SQL> drop restore point UATB_COPY;

Restore point dropped.

SQL> ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE NOARCHIVELOG
*
ERROR at line 1:
ORA-01143: cannot disable media recovery - file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSTEM01.DBF'

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

After database restored from flashback restore point. It has to be opened with resetlogs clause otherwise it will through an error.

NOTE: Here I have open the database and bounced the database again to disable the archive log mode.

SQL> select name from v$database;

NAME
---------
UATB

SQL> SHUT IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             167775108 bytes
Database Buffers          436207616 bytes
Redo Buffers                7135232 bytes
Database mounted.

SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

Here we are done :)

Thank You !