How to recover corrupted data blocks using 'recover datafile' RMAN syntax on a system critical datafile

Tweet

This post continues a series of previous posts related to the recovery process of corrupt data blocks:
1) How to recover contiguous corrupted data blocks using 'recover datafile' RMAN syntax;
2) How to recover sparse corrupted data blocks using 'recover datafile' RMAN syntax;

What does it happen when a corrupt block is related to SYSTEM datafile ?
Let's simulate this scenario:

[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/system01.dbf bs=8k conv=notrunc seek=1000 count=5

DBVERIFY detects your SYSTEM datafile is corrupt. Five data blocks are no more available.

[oracle@localhost trace]$ dbv file=/home/oracle/app/oracle/oradata/orcl/system01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.2.0 - Production on Tue Mar 19 09:01:17 2013

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

DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/orcl/system01.dbf
Page 1000 is marked corrupt
Corrupt block relative dba: 0x004003e8 (file 1, block 1000)
Completely zero block found during dbv: 

Page 1001 is marked corrupt
Corrupt block relative dba: 0x004003e9 (file 1, block 1001)
Completely zero block found during dbv: 

Page 1002 is marked corrupt
Corrupt block relative dba: 0x004003ea (file 1, block 1002)
Completely zero block found during dbv: 

Page 1003 is marked corrupt
Corrupt block relative dba: 0x004003eb (file 1, block 1003)
Completely zero block found during dbv: 

Page 1004 is marked corrupt
Corrupt block relative dba: 0x004003ec (file 1, block 1004)
Completely zero block found during dbv: 

DBVERIFY - Verification complete

Total Pages Examined         : 116608
Total Pages Processed (Data) : 73223
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 18058
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 11301
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 14021
Total Pages Marked Corrupt   : 5
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 14866073 (0.14866073)

Let's update the V$DATABASE_BLOCK_CORRUPTION view issuing a backup validate command.

RMAN> backup validate tablespace system;

Starting backup at 19-03-2013 09:02:43
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    FAILED 0              14021        116611          14866295  
  File Name: /home/oracle/app/oracle/oradata/orcl/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              73223           
  Index      0              18058           
  Other      5              11306           

validate found one or more corrupt blocks
See trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7493.trc for details
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              604 
Finished backup at 19-03-2013 09:03:21

The V$DATABASE_BLOCK_CORRUPTION view contains the list of corrupt blocks, all related to datafile number 1.

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         1       1000          5                  0 ALL ZERO

Let's try to recover those blocks.

RMAN> recover corruption list;

Starting recover at 19-03-2013 09:04:22
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 00001
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_17/o1_mf_nnndf_TAG20130317T224547_8nfbjnn5_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_17/o1_mf_nnndf_TAG20130317T224547_8nfbjnn5_.bkp tag=TAG20130317T224547
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:02:26

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 19-03-2013 09:06:51

It worked. And the view doesn't contain any data.

SQL> select * from v$database_block_corruption;

no rows selected

So the recovery process of a corrupt block of the SYSTEM tablespace is the same and there is no need to bounce the instance.
Next step will be about how to perform a recovery when the first block of a datafile, containing the datafile header, becomes corrupt.
That's all and happy birthday to me :)