How to Recover a missing Datafile in Oracle
1-Before starting doing this make sure we have a valid backup of our database prior of dropping the database.
We will assume that you have your database on a everyday Rman backup. For the purpose of this tutorial i will make a full backup of my database.
RMAN> backup database plus archivelog;
Starting backup at 30-OCT-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=830181880
input archived log thread=1 sequence=2 RECID=2 STAMP=830181938
input archived log thread=1 sequence=3 RECID=10 STAMP=830184641
input archived log thread=1 sequence=4 RECID=8 STAMP=830184641
input archived log thread=1 sequence=5 RECID=9 STAMP=830184641
.....
.....
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
14 2.18M DISK 00:00:00 30-OCT-13
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20131030T155355
Piece Name: /opt/oracle/flash_recovery_area/TEST/backupset/2013_10_30/o1_mf_annnn_TAG20131030T155355_972kxnq8_.bkp
List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1037140 30-OCT-13 1039939 30-OCT-13
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 943.88M DISK 00:00:57 30-OCT-13
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20131030T155357
Piece Name: /opt/oracle/flash_recovery_area/TEST/backupset/2013_10_30/o1_mf_nnndf_TAG20131030T155357_972kxp1r_.bkp
List of Datafiles in backup set 15
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1039947 30-OCT-13 /opt/oracle/oradata/test/system01.dbf
2 Full 1039947 30-OCT-13 /opt/oracle/oradata/test/sysaux01.dbf
3 Full 1039947 30-OCT-13 /opt/oracle/oradata/test/undotbs01.dbf
4 Full 1039947 30-OCT-13 /opt/oracle/oradata/test/users01.dbf
5 Full 1039947 30-OCT-13 /opt/oracle/oradata/test/test.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
16 3.00K DISK 00:00:00 30-OCT-13
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20131030T155503
Piece Name: /opt/oracle/flash_recovery_area/TEST/backupset/2013_10_30/o1_mf_annnn_TAG20131030T155503_972kzq95_.bkp
List of Archived Logs in backup set 16
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 1039939 30-OCT-13 1039974 30-OCT-13
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 9.67M DISK 00:00:02 30-OCT-13
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20131030T155504
Piece Name: /opt/oracle/flash_recovery_area/TEST/autobackup/2013_10_30/o1_mf_s_830188504_972kzt2j_.bkp
SPFILE Included: Modification time: 30-OCT-13
SPFILE db_unique_name: TEST
Control File Included: Ckp SCN: 1039983 Ckp time: 30-OCT-13
We see that we have the backup available.
2-List the tablespaces the we have and drop one's datafile.
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 670 SYSTEM *** /opt/oracle/oradata/test/system01.dbf
2 490 SYSAUX *** /opt/oracle/oradata/test/sysaux01.dbf
3 30 UNDOTBS1 *** /opt/oracle/oradata/test/undotbs01.dbf
4 5 USERS *** /opt/oracle/oradata/test/users01.dbf
5 40 TEST *** /opt/oracle/oradata/test/test.dbf
3-Drop the TEST table space data file.
[oracle@clone test]$ rm test.dbf
So no more one table or test tablespace.
4-Next let's bounce our database and see what happens.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2212936 bytes
Variable Size 138415032 bytes
Database Buffers 167772160 bytes
Redo Buffers 4759552 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/test/test.dbf'
We see that it's saying that datafile is missing.... hmmm
5-Let's go and recover and restore the missing datafile.
[oracle@clone test]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 31 12:38:55 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=1667326662, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 670 SYSTEM *** /opt/oracle/oradata/test/system01.dbf
2 500 SYSAUX *** /opt/oracle/oradata/test/sysaux01.dbf
3 30 UNDOTBS1 *** /opt/oracle/oradata/test/undotbs01.dbf
4 5 USERS *** /opt/oracle/oradata/test/users01.dbf
5 0 TEST *** /opt/oracle/oradata/test/test.dbf
RMAN> restore datafile 5;
Starting restore at 31-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/test/test.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/TEST/backupset/2013_10_30/o1_mf_nnndf_TAG20131030T173042_972qm294_.bkp
channel ORA_DISK_1: piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2013_10_30/o1_mf_nnndf_TAG20131030T173042_972qm294_.bkp tag=TAG20131030T173042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 31-OCT-13
RMAN> recover datafile 5 ;
Starting recover at 31-OCT-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-OCT-13
RMAN> alter database open;
database opened
RMAN> report schema;
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 670 SYSTEM *** /opt/oracle/oradata/test/system01.dbf
2 500 SYSAUX *** /opt/oracle/oradata/test/sysaux01.dbf
3 30 UNDOTBS1 *** /opt/oracle/oradata/test/undotbs01.dbf
4 5 USERS *** /opt/oracle/oradata/test/users01.dbf
5 50 TEST *** /opt/oracle/oradata/test/test.dbf
RMAN>
For this type of recovery you need to use the data file id in this case 5.
