Recovery en cas de bloc corrompu. Au lieu de restaurer tout un datafile, on peut ne restaurer que les blocs corrompus. S’il y en a peu, c’est plus rapide et évite de mettre le datafile offline.

Ici un exemple sur une base de demo créée ici.

Voici les blocs qui stokent les données de OE.ORDERS:

RMAN> select file_id,block_id,blocks from dba_extents where owner='OE' and segment_name='ORDERS';
using target database control file instead of recovery catalog
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         2        968          8

8 blocs alloués à partir du bloc 968. Je vais corrompre le bloc 971 en lui mettant des données aléatoires avec la commande dd, et je vérivie avec dbv:

+ dd if=/dev/urandom count=1 bs=8k seek=971 of=/u/DEMO/DATA/DEMO100/example01.dbf conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00157981 s, 5.2 MB/s

+ dbv /u/DEMO/DATA/DEMO100/example01.dbf
DBVERIFY - Verification starting : FILE = /u/DEMO/DATA/DEMO100/example01.dbf
Page 971 is marked corrupt
Corrupt block relative dba: 0x028003cb (file 10, block 971)
Bad header found during dbv:
Data in bad block:
 type: 90 format: 3 rdba: 0xfd75bf2c
 last change scn: 0xd97e.9447efe5 seq: 0xc7 flg: 0x96
 spare1: 0x80 spare2: 0x4f spare3: 0x225f
 consistency value in tail: 0x767ce7f0
 check value in block header: 0xe831
 computed block checksum: 0x9a28

Le bloc est bien corrompu, d’où l’erreur suivante lors d’un select:

RMAN> alter system flush buffer_cache;
using target database control file instead of recovery catalog
Statement processed
RMAN> select /*+ FULL(ORDERS) */ count(*) from OE.ORDERS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/17/2013 20:16:06
ORA-01578: ORACLE data block corrupted (file # 2, block # 971)
ORA-01110: data file 2: '/u/DEMO/DATA/DEMO100/example01.dbf'

On va d’abord tester Recovery Advisor:

RMAN> list failure;

RMAN> list failure;
using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
8098       HIGH     OPEN      17-NOV-13     Datafile 2: '/u/DEMO/DATA/DEMO100/example01.dbf' contains one or more corrupt blocks

RMAN> advise failure;

RMAN> advise failure;
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
8098       HIGH     OPEN      17-NOV-13     Datafile 2: '/u/DEMO/DATA/DEMO100/example01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=378 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform block media recovery of block 971 in file 2  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_3695178258.hm

mais Recovery Advisor ne propose que le restore/recover du datafile entier.

Je vérifie d’abord qu’il n’y a pas d’autres blocs corrompus:

RMAN> validate datafile 2;

RMAN> validate datafile 2;
Starting validate at 17-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/u/DEMO/DATA/DEMO100/example01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    FAILED 0              31954        45841           1913733   
  File Name: /u/DEMO/DATA/DEMO100/example01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              4534            
  Index      0              1219            
  Other      1              8133            

validate found one or more corrupt blocks
See trace file /u/DEMO/diag/rdbms/demo100/DEMO100/trace/DEMO100_ora_14863.trc for details
Finished validate at 17-NOV-13

RMAN> select * from v$database_block_corruption;

RMAN> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         2        971          1                  0 CORRUPT            0

Un seul bloc, donc je préfère faire un BLOCKRECOVER:

RMAN> blockrecover datafile 2 block 971;

RMAN> blockrecover datafile 2 block 971;
Starting recover at 17-NOV-13
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 00002
channel ORA_DISK_1: reading from backup piece /u/DEMO/backup/12op4mt1_1_1
channel ORA_DISK_1: piece handle=/u/DEMO/backup/12op4mt1_1_1 tag=TAG20131116T205457
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:15

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_2_98hmq0s6_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_1_98hmwbsf_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_2_98hp41w7_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_3_98hpgdt4_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_4_98hpgd5z_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_5_98hpgdh8_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_1_98hr6zvl_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_2_98hr701w_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_3_98hr70cc_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_1_98hrcjy5_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_2_98hrck57_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_3_98hrckdn_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_16/o1_mf_1_1_98hrcrp5_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_2_98kwmzy5_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_3_98l3gos8_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_4_98l3xxyc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_17/o1_mf_1_5_98l5hjrn_.arc
media recovery complete, elapsed time: 00:00:15
Finished recover at 17-NOV-13

C’est fait, on vérifie le datafile:

RMAN> validate datafile 2;

RMAN> validate datafile 2;
Starting validate at 17-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/u/DEMO/DATA/DEMO100/example01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              31954        45841           1913733   
  File Name: /u/DEMO/DATA/DEMO100/example01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              4535            
  Index      0              1219            
  Other      0              8132            

Finished validate at 17-NOV-13

Plus de corruption. On peut relancer la requête:

RMAN> select /*+ FULL(ORDERS) */ count(*) from OE.ORDERS;

RMAN> select /*+ FULL(ORDERS) */ count(*) from OE.ORDERS;
  COUNT(*)
----------
       105

La base est restée ouverte, le datafile est resté online.
C’est le dépannage le plus rapide, malheureusement il n’est pas proposé par Recovery Advisor.