oracle dataguard坏块,dataguard主库坏块的修复

最理想的情况是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.

换成最大性能模式，重新制造坏块，查询时报错。

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-1476289/，如需转载，请注明出处，否则将追究法律责任。