1 最理想的情况是11g dataguard配成用standby redolog实时应用。
这种模式下主库出现坏块,当数据库被下一次被用到时自动利用备库来修复。
主库
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.
2 换成最大性能模式,重新制造坏块,查询时报错。
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-1427455/,如需转载,请注明出处,否则将追究法律责任。