How To Start an Oracle Database with Corrupted or Lost dbfile

Getting an error message telling one datafile is lost or corrupted.

ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/db/test.dbf'

If the database is in ARCHIVELOG mode with a working online backup concept then, recover the database and everything is OK. But what can we do, if the database is in NOARCHIVELOG mode and no backup is present ?

a). If the database is still running do the following

1). Switch the damaged datafile to the RECOVER status 

ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;

SELECT file#, status,bytes,name FROM v$datafile;

  FILE# STATUS       BYTES      NAME
------- -------- --------- ------------------------------
      1   SYSTEM     104857600  /u01/db/SOL3/sys/SOL3_sys1.dbf
      2   RECOVER     2097152     /u01/db/test.dbf

2). Stop and Start the database to verify that the database can be started without ' test.dbf '. 

SHUTDOWN IMMEDIATE;
STARTUP;

3). DROP the tablespace to which the datafile belongs

DROP TABLESPACE test INCLUDING CONTENTS;

b). If the database is not running do the following

The database can only be stopped with SHUTDOWN ABORT with a damaged or lost datafile and the datafile is still in ONLINE mode. Therefore it's better to switch the datafile to the RECOVER status as shown above before stopping the database. However there is a way to switch the datafile to the RECOVER status when the database is stopped.

1). Mount the database and switch the damaged datafile to the RECOVER status

STARTUP MOUNT;
ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
ALTER DATABASE OPEN;

2). DROP the tablespace to which the datafile belongs

DROP TABLESPACE test INCLUDING CONTENTS;

3). Stop and Start the database to verify that the database can be started without ‘test.dbf '. 

SHUTDOWN IMMEDIATE;
STARTUP;

Important note

·         The term ' OFFLINE DROP ' is misleading; it is not possible to drop a datafile with this command. The only purpose of this command is to startup a database with damaged or missing datafile and the databae is in NOARCHIVELOG mode.

·         The command ' ALTER DATABASE DATAFILE ... OFFLINE DROP ' changes the datafile status from ONLINE to RECOVER. In this mode, the database can be started even if the datafile is not present.

·         If space management (e.g. CREATE TABLE ..) occurs for this datafile, Oracle will try to allocate space in this ' dropped ' datafile and fails.

·         The only way to drop a datafile is to drop the tablespace to which the datafile belongs. Unfortunately you will lose data if you don't have an actual backup.