Restore Datafile Without Backup - Archivelog Mode ON

Datafile restoration without backup is only possible when archivelog is enabled during the time of datafile dropped. By using archivelog and redo logs we can restore datafile.

SQL> select log_mode from v$database;

                        LOG_MODE

                                ------------

                                ARCHIVELOG

• I have removed the emp.dbf datafile accidently

Oracle@test TEST$  rm emp.dbf

• When I try to insert a new data in employee table present in emp datafile it shows below error

SQL> insert into emp select * from emp;
insert into emp select * from emp
                           
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 6: '/d02/app/oracle/oradata/testdb/emp.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

• To recover the datafile we need to take it offline :

SQL> alter database datafile 6 offline;

• Create a new datafile with same old dropped datafile name/Path

SQL> alter database create datafile '/d02/app/oracle/oradata/testdb/emp.dbf ';

• Now recover the datafile

SQL> recover datafile 8;

• Bring the datafile online :

SQL> alter database datafile 6 online;

• Now insert the values and check

SQL> insert into emp select * from emp;

100 rows created.