Recover a Lost Datafile without backup.

In my database I have added one data file. I have not back up that data file. Now someone accidentally drop that datafile. Can I get back the my data file now? The answer is yes if my database run in archivelog mode and I have the available archive logs science the creation of datafile.

With an example I will demonstrate the procedure.

1)I create tablespace data with one datafile and after some time I have added another datafile. Both data file don't have any backup.

SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG

SQL> create tablespace data datafile '/oradata2/data.dbf' size 2M;
Tablespace created.

SQL> alter tablespace data add datafile '/oradata2/data1/data02.dbf' size 2M;
Tablespace altered.

SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/users01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
6 rows selected.

2)Creating Tables to Populate the Datafiles.

SQL> create table before_delete tablespace data as select level a1 from dual connect by level <9999;Table created.

SQL> insert into before_delete select level a1 from dual connect by level <9999;9998 rows created.

SQL> commit;
Commit complete.

SQL> select file_name from dba_data_files where file_id in (select file_id from dba_extents where segment_name='BEFORE_DELETE');

FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data.dbf
/oradata2/data1/data02.dbf

3)Now delete both datafile by OS command.

SQL> !rm /oradata2/data.dbf

SQL> !rm /oradata2/data1/data02.dbf

4)Now I want to get back both datafile. Connect to RMAN and make the affected Tablespace offline immediate.

SQL> !rman target /
RMAN> sql'ALTER TABLESPACE DATA OFFLINE IMMEDIATE';

5)Perform Recovery of The tablespace.
RMAN> RECOVER TABLESPACE DATA;

6)Make the status online of the tablespace.
RMAN> sql'ALTER TABLESPACE DATA ONLINE';

In this case oracle at first create and empty datafile and then apply all archived redo logs and online redo logs on the tablespace up to the current time.