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=5DBVERIFY 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:21The 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 ZEROLet'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:51It worked. And the view doesn't contain any data.
SQL> select * from v$database_block_corruption; no rows selectedSo 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 :)