dataguard主庫壞塊的修復

這種模式下主庫出現壞塊，當資料庫被下一次被用到時自動利用備庫來修復。

主庫
SQL> select file_id, block_id, blocks from dba_extents where owner = 'SCOTT' and segment_name = 'T';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
     4     520          8


SQL> select min(rowid), max(rowid) from t;

MIN(ROWID)     MAX(ROWID)
------------------ ------------------
AAASwmAAEAAAAILAAA AAASwmAAEAAAAIPAAj

自動段空間管理的資料是從第四個塊開始。
可以透過dbms_rowid驗證一下。
SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAASwmAAEAAAAILAAA') min_block, DBMS_ROWID.ROWID_BLOCK_NUMBER('AAASwmAAEAAAAILAAj') max_block from dual;

MIN_BLOCK  MAX_BLOCK
---------- ----------
       523    523

構造壞塊
RMAN> recover datafile 4 block 523 clear;

Starting recover at 05-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
Finished recover at 05-FEB-15

再次查詢，資料直接就出來了。
SQL> select count(*) from t;

  COUNT(*)
----------
  72


alert日誌
Thu Feb 05 14:30:48 2015
Hex dump of (file 4, block 523) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6998.trc
Corrupt block relative dba: 0x0100020b (file 4, block 523)
Bad header found during multiblock buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x3c24280b
last change scn: 0x982f.a9d3a0d0 seq: 0xa2 flg: 0x5e
spare1: 0x0 spare2: 0x0 spare3: 0x2f
consistency value in tail: 0x38850602
check value in block header: 0xbbcf
computed block checksum: 0x4e0
Reading datafile '/u01/oradata/orcl/users01.dbf' for corruption at rdba: 0x0100020b (file 4, block 523)
Reread (file 4, block 523) found same corrupt data (no logical check)
Starting background process ABMR
Thu Feb 05 14:30:48 2015
ABMR started with pid=32, OS id=7016
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 4, block# 523)
Thu Feb 05 14:30:48 2015
Automatic block media recovery successful for (file# 4, block# 523)
Automatic block media recovery successful for (file# 4, block# 523)
WARNING: AutoBMR fixed mismatched on-disk block 3c24280b with in-mem rdba 100020b.


換成最大效能模式，重新制造壞塊，查詢時報錯。
SQL> select count(*) from scott.t;
select count(*) from scott.t
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 523)
ORA-01110: data file 4: '/u01/oradata/orcl/users01.dbf'

alert日誌
Thu Feb 05 14:36:11 2015
Hex dump of (file 4, block 523) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7120.trc
Corrupt block relative dba: 0x0100020b (file 4, block 523)
Bad check value found during multiblock buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x0100020b
last change scn: 0x0000.00103885 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x38850602
check value in block header: 0x5b3d
computed block checksum: 0x8201
Reading datafile '/u01/oradata/orcl/users01.dbf' for corruption at rdba: 0x0100020b (file 4, block 523)
Reread (file 4, block 523) found same corrupt data (no logical check)
Thu Feb 05 14:36:11 2015
Corrupt Block Found
         TSN = 4, TSNAME = USERS
         RFN = 4, BLK = 523, RDBA = 16777739
         OBJN = 76838, OBJD = 76838, OBJECT = T, SUBOBJECT =
         SEGMENT OWNER = SCOTT, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7120.trc  (incident=18160):
ORA-01578: ORACLE data block corrupted (file # 4, block # 523)
ORA-01110: data file 4: '/u01/oradata/orcl/users01.dbf'
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_18160/orcl_ora_7120_i18160.trc
Thu Feb 05 14:36:13 2015
Sweep [inc][18160]: completed
Hex dump of (file 4, block 523) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_18160/orcl_m000_7173_i18160_a.trc
Corrupt block relative dba: 0x0100020b (file 4, block 523)
Bad check value found during validation
Data in bad block:
type: 6 format: 2 rdba: 0x0100020b
last change scn: 0x0000.00103885 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x38850602
check value in block header: 0x5b3d
computed block checksum: 0x8201
Reread of blocknum=523, file=/u01/oradata/orcl/users01.dbf. found same corrupt data
Reread of blocknum=523, file=/u01/oradata/orcl/users01.dbf. found same corrupt data
Reread of blocknum=523, file=/u01/oradata/orcl/users01.dbf. found same corrupt data
Reread of blocknum=523, file=/u01/oradata/orcl/users01.dbf. found same corrupt data
Reread of blocknum=523, file=/u01/oradata/orcl/users01.dbf. found same corrupt data
Thu Feb 05 14:36:13 2015
Dumping diagnostic data in directory=[cdmp_20150205143613], requested by (instance=1, osid=7120), summary=[incident=18160].


此時oracle無法自動修復壞塊。但是可以透過rman修復
接下來分兩種情況
1 如果有備份，就從備份恢復。分別讀資料檔案備份和歸檔備份。
RMAN> recover datafile 4 block 523;

Starting recover at 05-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_02_05/o1_mf_nnndf_TAG20150205T135105_bf6169pq_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_02_05/o1_mf_nnndf_TAG20150205T135105_bf6169pq_.bkp tag=TAG20150205T135105
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 37 is already on disk as file /u01/oradata/orcl/arch/log_1_37_853863284.arc
archived log for thread 1 with sequence 38 is already on disk as file /u01/oradata/orcl/arch/log_1_38_853863284.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_02_05/o1_mf_annnn_TAG20150205T135243_bf619cvv_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_02_05/o1_mf_annnn_TAG20150205T135243_bf619cvv_.bkp tag=TAG20150205T135243
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
media recovery complete, elapsed time: 00:00:01
Finished recover at 05-FEB-15



2 如果沒有備份，就自動從備庫修復
RMAN> recover datafile 4 block 523;

Starting recover at 05-FEB-15
using channel ORA_DISK_1
finished standby search, restored 1 blocks

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

Finished recover at 05-FEB-15


alert日誌
Thu Feb 05 14:48:17 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Started Block Media Recovery
Recovery of Online Redo Log: Thread 1 Group 2 Seq 50 Reading mem 0
  Mem# 0: /u01/oradata/orcl/redo02.log
Completed Block Media Recovery