RMAN : ORA-01145: offline immediate disallowed unless media recovery enabled

I was working on an activity that required me to set some datafile offline when I received the following error:


SQL> alter database datafile 99 offline;
alter database datafile 99 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

After some analysis, I realized that the database was in no archive mode, and to be able to make a datafile offline, the database must be in archive mode. Therefore, I enabled the archive mode in the database by restarting the database in the MOUNT state, allowing the archive to mode option, and after it was possible to change the status of the database as below:


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

SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             436208824 bytes
Database Buffers          624951296 bytes
Redo Buffers                5541888 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +RECO
Oldest online log sequence     49
Next log sequence to archive   50
Current log sequence           50
SQL> show parameters recov

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0


SQL> ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='+RECO'  SCOPE=BOTH;

System altered.

SQL> alter database open;

Database altered.

SQL>
SQL> alter database datafile 99 offline;

Database altered.


Related posts

Leave a Comment