PROBLEM:

While making a datafile offline, got below error.

SQL> ALTER database datafile ‘/export/home/oracle/user02.dbf’ offline;
ALTER database datafile ‘/export/home/oracle/user02.dbf’ offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SOLUTION:

To make a datafile offline, archive mode need to be enabled.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/12.1.0.2/dbhome_1/dbs/arch
Oldest online log sequence 84
Current log sequence 85

Below are steps for enabling archive mode:

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/app/oracle/product/12.1.0.2/dbhome_1/dbs/arch
Oldest online log sequence     84
Next log sequence to archive   85
Current log sequence           85
SQL>

Now try to make the datafile offline.

SQL> ALTER database datafile '/export/home/oracle/user02.dbf' offline;

Database altered.