Recover lost datafile without valid backup

I’ve decided to perform this test case and publish blog post after reading this topic on OTN forums - http://bit.ly/mLwaA9

How to recover lost datafile when you don’t have valid backup of your database. Your database is running in archivelog mode and you have all necessary archive logs.

In situation when you don’t have valid backup and your datatabase is running in noarchivelog mode - your datafile is lost forever.

So take regular backups of your database and enable archivelog mode ;-)


Environment for this test:
OS: OEL 5
DB: Oracle EE 10.2.0.4

To check am I running my database in archivelog mode.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     70
Next log sequence to archive   72
Current log sequence           72

I will create new tablespace for my test and name it - TS_TEST.

SQL> create tablespace ts_test datafile '/u01/oradata/orcl/ts_test01.dbf' size 256M;

Tablespace created.

Now to create table in TS_TEST tablespace. I will fill this table with 1000000 rows using Tom Kyte's script.

SQL> create table bigtab tablespace ts_test
  2  as
  3  select rownum id, a.*
  from all_objects a
 where 1=0;  4    5
 
 
 
SQL> select tablespace_name, segment_name from 
2         dba_segments where segment_name = 'BIGTAB';

TABLESPACE_NAME                SEGMENT_NAME
--------------- ---------------------------------------
TS_TEST                        BIGTAB
 
--
-- I'VE EXCLUDED SCRIPT LINES CAUSE
-- IT'S NOT IMPORTANT FOR THIS TEST CASE
--
SQL> select count(*) from bigtab;

  COUNT(*)
----------
   1000000

I can see in alert log that there were 5 log switches during insert.

Starting control autobackup
Control autobackup written to DISK device
        handle '/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_06_09/o1_mf_s_753372242_6z1folfy_.bkp'
Completed: create tablespace ts_test datafile '/u01/oradata/orcl/ts_test01.dbf' size 256M
Thu Jun  9 14:07:42 2011
Thread 1 advanced to log sequence 73 (LGWR switch)
  Current log# 1 seq# 73 mem# 0: /u01/oradata/orcl/redo01.log
Thu Jun  9 14:07:45 2011
Thread 1 advanced to log sequence 74 (LGWR switch)
  Current log# 2 seq# 74 mem# 0: /u01/oradata/orcl/redo02.log
Thu Jun  9 14:10:55 2011
Thread 1 cannot allocate new log, sequence 75
Checkpoint not complete
  Current log# 2 seq# 74 mem# 0: /u01/oradata/orcl/redo02.log
Thu Jun  9 14:10:56 2011
Thread 1 advanced to log sequence 75 (LGWR switch)
  Current log# 3 seq# 75 mem# 0: /u01/oradata/orcl/redo03.log
Thu Jun  9 14:10:58 2011
Thread 1 advanced to log sequence 76 (LGWR switch)
  Current log# 1 seq# 76 mem# 0: /u01/oradata/orcl/redo01.log
Thread 1 cannot allocate new log, sequence 77
Checkpoint not complete
  Current log# 1 seq# 76 mem# 0: /u01/oradata/orcl/redo01.log
Thu Jun  9 14:11:00 2011
Thread 1 advanced to log sequence 77 (LGWR switch)
  Current log# 2 seq# 77 mem# 0: /u01/oradata/orcl/redo02.log

To simulate disaster I will delete “ts_test01.dbf” datafile using OS command and flush buffer cache several times.

SQL> !rm /u01/oradata/orcl/ts_test01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Now my query throws an error because datafile 8 is missing.

SQL> select count(*) from bigtab;
select count(*) from bigtab
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/orcl/ts_test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

How to recover this datafile when you don’t have valid backup to restore it?

Trick is to create new datafile based on the file “ts_test01.dbf” using command:
ALTER DATABASE CREATE DATAFILE 'xxxx.dbf' AS 'xxxx.dbf' and fill it with data from archive logs.

SQL> alter database create datafile '/u01/oradata/orcl/ts_test01.dbf' as '/u01/oradata/orcl/ts_test01.dbf';
alter database create datafile '/u01/oradata/orcl/ts_test01.dbf' as '/u01/oradata/orcl/ts_test01.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 8 - file is in use or recovery
ORA-01110: data file 8: '/u01/oradata/orcl/ts_test01.dbf'

To avoid error - before creating new datafile, you must take the existing datafile (or the tablespace) offline.

SQL> alter database datafile 8 offline drop;

Database altered.


SQL> alter database create datafile '/u01/oradata/orcl/ts_test01.dbf' as '/u01/oradata/orcl/ts_test01.dbf';

Database altered.

Now I have new empty datafile which I will fill with records applying archive logs over it during recovery.

SQL> recover datafile 8;
ORA-00279: change 92069000 generated at 06/09/2011 14:03:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_72_%u_.arc
ORA-00280: change 92069000 for thread 1 is in sequence #72


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92069587 generated at 06/09/2011 14:07:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_73_%u_.arc
ORA-00280: change 92069587 for thread 1 is in sequence #73
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_72_6z1fw
gvn_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92069713 generated at 06/09/2011 14:07:45 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_74_%u_.arc
ORA-00280: change 92069713 for thread 1 is in sequence #74
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_73_6z1fw
kyj_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92070100 generated at 06/09/2011 14:10:56 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_75_%u_.arc
ORA-00280: change 92070100 for thread 1 is in sequence #75
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_74_6z1g2
jh1_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92071034 generated at 06/09/2011 14:10:58 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_76_%u_.arc
ORA-00280: change 92071034 for thread 1 is in sequence #76
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_75_6z1g2
lbg_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92071751 generated at 06/09/2011 14:11:00 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_77_%u_.arc
ORA-00280: change 92071751 for thread 1 is in sequence #77
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_76_6z1g2
nmz_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

Recovery is successfully completed and now to alter datafile 8 to be online again.

SQL> alter database datafile 8 online;

Database altered.


-- CHECK
SQL> select count(*) from bigtab;

  COUNT(*)
----------
   1000000


-- CLEANUP

SQL> drop tablespace ts_test including contents and datafiles;

Tablespace dropped.