Getting an error message telling one datafile is lost or corrupted.
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
2). Stop and Start the database to verify that the database can be started without ' test.dbf '.
3). DROP the tablespace to which the datafile belongs
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
2). DROP the tablespace to which the datafile belongs
3). Stop and Start the database to verify that the database can be started without ‘test.dbf '.
· 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.