ORA-01145: offline immediate disallowed unless media recovery enabled

I faced this error while I was trying to take a datafile offline and my database was in mount state as well as in noarchivelog mode, at that time. Opening database would return ORA-01157 for this datafile.

SQL> alter database datafile 'd:\oracle\oradata\data01.dbf' offline; alter database datafile 'd:\oracle\oradata\data01.dbf' offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled

I did not have any backup of this datafile (this was just a testing environment) that I could restore, so I was thinking to drop this tablespace/datafile. Dropping a tablespace/datafile, however, needs database to be open and opening database was not possible because datafile was corrupt and needed to be offline before I could open the database. This was kind of a paradox that I was facing.

We can get out of this situation in 2 ways.
First option is that we take datafile offline with drop option and open the database. Following is what I did here.

SQL> startup ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size                  3046176 bytes Variable Size             520094944 bytes Database Buffers         1073741824 bytes Redo Buffers               13729792 bytes Database mounted. ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: 'd:\oracle\oradata\data01.dbf' SQL> alter database datafile 'c:\file1.dbf' offline; alter database datafile 'd:\oracle\oradata\data01.dbf' offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled SQL> alter database datafile 'd:\oracle\oradata\data01.dbf' OFFLINE DROP; Database altered.

Second option is to shift our database to archivelog mode and then take datafile offline because brining datafile offline needs database to be in archivelog mode.

SQL> ALTER DATABASE ARCHIVELOG; Database altered. SQL> alter database datafile 'd:\oracle\oradata\data01.dbf' offline; Database altered. SQL> alter database open; Database altered.

Next step is to either recover this datafile/tablespace, or drop it completely.