Recovering a Deleted Datafile Without a Backup

Guess what, RMAN can handle lost datafiles even if you do not have a backup.

The conditions:
1. The control file knows about the datafile, that is, the user backed up the control file after datafile creation, but the datafile itself is not backed up.

2. If the datafile record is in the control file, then RESTORE creates the datafile in the original location or in a user-specified location (for example, with SET NEWNAME). The RECOVER command can then apply the necessary logs to the datafile.

3. The control file does not have the datafile record, that is, the user did not back up the control file after datafile creation. During recovery, the database will detect the missing datafile and report it to RMAN, which will create a new datafile and continue recovery by applying the remaining logs. If the datafile was created in a parent incarnation, it will be created during restore or recover as appropriate.

Example:
1. Make a full database backup of your ARCHIVELOG mode database.
RMAN> BACKUP DATABASE TAG="FULL";

2. You create a tablespace "TEST" containing a single datafile called /orcl/test01.dbf.

3. create a table and populate some data.

4. Archive all the active online redo logs.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

5. Delete the file /orcl/test01.dbf.

6. Take the tablespace with the missing datafile offline.
SQL> ALTER TABLESPACE TEST OFFLINE IMMEDIATE;

7. Restore the tablespace even without backup.
RMAN> RESTORE TABLESPACE "TEST";

8. Recover tablespace.
RMAN> RECOVER TABLESPACE "TEST";

9. Bring the recovered tablespace online.
SQL> ALTER TABLESPACE TEST ONLINE;

10. Verify the contents of the tablespace.