Friday, August 24, 2018

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.

No comments:

Post a Comment

Popular Posts - All Times