Restoring a Datafile Using Archivelogs

Suppose a datafile that was recently added to the database is lost for some reasons. In this case, if the archive logs from the time the datafile was created are available, it is possible to recover that datafile.

However, as mentioned, this operation has a very strict requirement:

“All archive logs from the time the datafile was created must be available.”
Below, we will walk through a scenario related to this topic.

SQL> archive log list;

Database log mode              Archive Mode

SQL> alter tablespace USEF_TBS add datafile ‘/acfs/test2.dbf’ size 10m;

With the following commands, we create a new table that uses space from the new datafile so that after recovery we can verify that no data was lost.

SQL> create table usef_tbl1  tablespace usef_tbs as select * from dba_tables where 1=2;

SQL>  alter  table usef_tbl1 ALLOCATE EXTENT(DATAFILE ‘/acfs/test2.dbf’  size 5m);

Table altered.

SQL> insert into usef_tbl1 select * from dba_tables;

2323 rows created.

SQL> commit;

SQL> select count(*) from usef_tbl1;

      2323

At this stage, we delete the datafile and restart the database.

rm –rf   /acfs/test2.dbf

SQL> startup

ORACLE instance started.

Total System Global Area 8351150080 bytes

Fixed Size                  2701528 bytes

Variable Size            7348422440 bytes

Database Buffers          989855744 bytes

Redo Buffers               10170368 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 – see DBWR trace file

ORA-01110: data file 6: ‘/acfs/test2.dbf’

SQL> alter database datafile  ‘/acfs/test2.dbf’  offline;

Database altered.

SQL> alter database open;

Database altered

As the following command shows, the created table is not accessible.

SQL> select count(*) from usef_tbl1;

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: ORA-01110: data file 6: ‘/acfs/test2.dbf’

SQL> alter database create datafile ‘/acfs/test2.dbf’  ;

Database altered.
The recovery is performed with the following command:

SQL> recover datafile  ‘/acfs/test2.dbf‘;

Media recovery complete.

SQL> alter database datafile  ‘/acfs/test2.dbf‘ ONLINE;

Database altered.

SQL> select count(*) from usef_tbl1;

      2323