Recover a corrupted or lost datafile

Assumption: You have complete RMAN backup.

When the data file corrupted or lost an alert log show error message as follow;

bash-4.1$ tail -50f alert_TEST.log

Errors in file /binaries/oratest/product/11.2.0/db_1/admin/TEST_dopaoratest/diag/rdbms/test/TEST/trace/TEST_m000_23088.trc:
ORA-01116: error in opening database file 83
ORA-01110: data file 83: '/data/oradata/rman_test01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
Sun Aug 30 08:16:19 2015
Checker run found 1 new persistent data failures
Sun Aug 30 08:19:52 2015
Incremental checkpoint up to RBA [0x10c.136160.0], current log tail at RBA [0x10c.136325.0]
Sun Aug 30 08:21:31 2015

Recovery steps

step 1) Check an available valid backups

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
39      B  F  A DISK        18-AUG-15       1       1       YES        TAG20150818T131754
40      B  F  A DISK        18-AUG-15       1       1       YES        TAG20150818T131754
41      B  F  A DISK        18-AUG-15       1       1       YES        TAG20150818T131754
42      B  F  A DISK        18-AUG-15       1       1       YES        TAG20150818T131754
43      B  F  A DISK        18-AUG-15       1       1       NO         TAG20150818T150403
44      B  A  A DISK        18-AUG-15       1       1       YES        TAG20150818T150419
45      B  A  A DISK        18-AUG-15       1       1       YES        TAG20150818T150419
46      B  A  A DISK        18-AUG-15       1       1       YES        TAG20150818T150419
47      B  A  A DISK        18-AUG-15       1       1       YES        TAG20150818T150419
48      B  F  A DISK        18-AUG-15       1       1       NO         TAG20150818T151908
49      B  F  A DISK        18-AUG-15       1       1       NO         TAG20150818T151910
50      B  0  A DISK        20-AUG-15       1       1       YES        THUFULL
51      B  0  A DISK        20-AUG-15       1       1       YES        THUFULL
52      B  0  A DISK        20-AUG-15       1       1       YES        THUFULL
53      B  0  A DISK        20-AUG-15       1       1       YES        THUFULL
54      B  F  A DISK        20-AUG-15       1       1       NO         TAG20150820T125630
55      B  A  A DISK        20-AUG-15       1       1       YES        THUDAYARCH
56      B  A  A DISK        20-AUG-15       1       1       YES        THUDAYARCH
57      B  A  A DISK        20-AUG-15       1       1       YES        THUDAYARCH
58      B  A  A DISK        20-AUG-15       1       1       YES        THUDAYARCH
59      B  F  A DISK        20-AUG-15       1       1       YES        THUCTL
60      B  F  A DISK        20-AUG-15       1       1       NO         TAG20150820T130914
61      B  F  A DISK        23-AUG-15       1       1       NO         TAG20150823T123408

RMAN>


Step 2) Stop database

In my case shutdown "immediate option" was taken time and then I used "shutdown abort" option.

-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 30 08:16:55 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
ORA-01116: error in opening database file 83
ORA-01110: data file 83: '/data/oradata/rman_test01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL>

SQL> shut abort;
ORACLE instance shut down.
SQL>

Step 3) Connect to RMAN and bring up database in mount mode:

-bash-4.1$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 30 08:32:56 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area    4277059584 bytes

Fixed Size                     2166160 bytes
Variable Size               1174409840 bytes
Database Buffers            3087007744 bytes
Redo Buffers                  13475840 bytes


Step 4) Restore data file form the latest backup:

In my case I have used latest full backup tagged as "THUFULL"

RMAN> restore datafile 83 from tag='THUFULL';

Starting restore at 30-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 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 00083 to /data/oradata/rman_test01.dbf
channel ORA_DISK_1: reading from backup piece /data/backup/TEST/backupset/2015_08_20/o1_mf_nnnd0_THUFULL_bxbzbyvy_.bkp
channel ORA_DISK_1: piece handle=/data/backup/TEST/backupset/2015_08_20/o1_mf_nnnd0_THUFULL_bxbzbyvy_.bkp tag=THUFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 01:06:26
Finished restore at 30-AUG-15

RMAN>

Step 5) Recover data file

RMAN> recover datafile 83;

Starting recover at 30-AUG-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 209 is already on disk as file /data/backup/TEST/archivelog/2015_08_20/o1_mf_1_209_bxccl0w1_.arc
archived log for thread 1 with sequence 210 is already on disk as file /data/backup/TEST/archivelog/2015_08_20/o1_mf_1_210_bxch2v5m_.arc
archived log for thread 1 with sequence 211 is already on disk as file /data/backup/TEST/archivelog/2015_08_20/o1_mf_1_211_bxcp2mkx_.arc
archived log for thread 1 with sequence 212 is already on disk as file /data/backup/TEST/archivelog/2015_08_20/o1_mf_1_212_bxcsms34_.arc
archived log for thread 1 with sequence 213 is already on disk as file /data/backup/TEST/archivelog/2015_08_20/o1_mf_1_213_bxcwv8ko_.arc
archived log for thread 1 with sequence 214 is already on disk as file /data/backup/TEST/archivelog/2015_08_20/o1_mf_1_214_bxcx6o5p_.arc
archived log for thread 1 with sequence 215 is already on disk as file
/data/backup/TEST/archivelog/2015_08_20/o1_mf_1_215_bxd0q5fd_.arc
.
.
.
.
/data/backup/TEST/archivelog/2015_08_27/o1_mf_1_263_bxx6hr5j_.arc
archived log for thread 1 with sequence 264 is already on disk as file /data/backup/TEST/archivelog/2015_08_27/o1_mf_1_264_bxx6kfqt_.arc
archived log for thread 1 with sequence 265 is already on disk as file /data/backup/TEST/archivelog/2015_08_27/o1_mf_1_265_bxx6ls45_.arc
archived log for thread 1 with sequence 266 is already on disk as file /data/backup/TEST/archivelog/2015_08_27/o1_mf_1_266_bxx6mxkp_.arc
archived log for thread 1 with sequence 267 is already on disk as file /data/backup/TEST/archivelog/2015_08_28/o1_mf_1_267_by1fh7bl_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=205
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=206
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=207
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=208
channel ORA_DISK_1: reading from backup piece /data/backup/TEST/backupset/2015_08_20/o1_mf_annnn_THUDAYARCH_bxc5l832_.bkp
channel ORA_DISK_1: piece handle=/data/backup/TEST/backupset/2015_08_20/o1_mf_annnn_THUDAYARCH_bxc5l832_.bkp tag=THUDAYARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:05
archived log file name=/data/backup/TEST/archivelog/2015_08_30/o1_mf_1_205_by58ockn_.arc thread=1 sequence=205
channel default: deleting archived log(s)
archived log file name=/data/backup/TEST/archivelog/2015_08_30/o1_mf_1_205_by58ockn_.arc RECID=160 STAMP=889094147
archived log file name=/data/backup/TEST/archivelog/2015_08_30/o1_mf_1_206_by58ock8_.arc thread=1 sequence=206
.
.

.
.
archived log file name=/data/backup/TEST/archivelog/2015_08_27/o1_mf_1_265_bxx6ls45_.arc thread=1 sequence=265
archived log file name=/data/backup/TEST/archivelog/2015_08_27/o1_mf_1_266_bxx6mxkp_.arc thread=1 sequence=266
media recovery complete, elapsed time: 00:07:47
Finished recover at 30-AUG-15

RMAN>


Step 6) open database


RMAN> alter database open;

database opened

RMAN>