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>

Recover a corrupted blocks in data file.

1) Corrupted blocks can be identified as follows;

(i) using sql:

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS    CORRUPTIO_CANGE# CORRUPTON_TYPE
-----         ------           ------ --         --------------                         --------------
83         147                 2                         0          CORRUPT

SQL>

(ii) using dbv command:

-bash-4.1$ dbv file=/data/oradata/rman_test01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Tue Aug 25 09:07:31 2015

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

DBVERIFY - Verification starting : FILE = /data/oradata/rman_test01.dbf
Page 147 is marked corrupt
Corrupt block relative dba: 0x14c00093 (file 83, block 147)
Bad header found during dbv:
Data in bad block:
 type: 116 format: 5 rdba: 0x20636f75
 last change scn: 0x0a6d.72727074 seq: 0x1 flg: 0x04
 spare1: 0x73 spare2: 0x74 spare3: 0x0
 consistency value in tail: 0x656d0601
 check value in block header: 0x5a9
 computed block checksum: 0x8a4e

Page 148 is marked corrupt
Corrupt block relative dba: 0x14c00094 (file 83, block 148)
Bad header found during dbv:
Data in bad block:
 type: 116 format: 5 rdba: 0x20636f72
 last change scn: 0x2066.72757074 seq: 0x6f flg: 0x72
 spare1: 0x73 spare2: 0x74 spare3: 0x6563
 consistency value in tail: 0x656d0601
 check value in block header: 0x2072
 block checksum disabled


DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 105
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 144
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1029
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3100913189 (1389.3100913189)
-bash-4.1$


2) Use "block recovery" command as follows;

RMAN> recover datafile 83 block 147 from tag=THUFULL;

Starting recover at 25-AUG-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00083
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 block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 01:06:35

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

.

.

.

media recovery complete, elapsed time: 00:09:44

Finished recover at 25-AUG-15

RMAN>

RMAN> recover datafile 83 block 148 from tag=THUFULL;

3) Check the corrupted blocks again:

-bash-4.1$ dbv file=/data/oradata/rman_test01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Wed Aug 26 09:38:15 2015

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

DBVERIFY - Verification starting : FILE = /data/oradata/rman_test01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 107
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 144
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1029
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3100913189 (1389.3100913189)
-bash-4.1$