Oracle 11g DataGuard (Automatic Block Media Repair)

在oracle 11g DataGuard 中引入Automatic Block Media Repair，自动的块恢复

如果在主库发现有损坏的数据块，该特性将通过后台进程ABMR自动将物理备库(physical standby)上的好的数据块传输到主库(primary database)上恢复
同样的若物理备库上发现数据块损坏那么也可以利用到以上特性来修复

此特性默认是开启的
SQL> select * from v$version where rownum <=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> select
  2    x.ksppinm  name,
  3    y.ksppstvl  value,
  4    y.ksppstdf  isdefault,
  5    decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
  6    decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
  7  from
  8    sys.x$ksppi x,
  9    sys.x$ksppcv y
 10  where
 11    x.inst_id = userenv('Instance') and
 12    y.inst_id = userenv('Instance') and
 13    x.indx = y.indx and
 14    x.ksppinm like '%_&par%'
 15  order by
 16    translate(x.ksppinm, ' _', ' ')
 17  /
Enter value for par: auto_bmr
old  14:   x.ksppinm like '%_&par%'
new  14:   x.ksppinm like '%_auto_bmr%'

NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ
------------------------------ ------------------------- --------- ---------- -----
_auto_bmr                      enabled                   TRUE      FALSE      FALSE
_auto_bmr_bg_time              3600                      TRUE      FALSE      FALSE
_auto_bmr_fc_time              60                        TRUE      FALSE      FALSE
_auto_bmr_pub_timeout          10                        TRUE      FALSE      FALSE
_auto_bmr_req_timeout          60                        TRUE      FALSE      FALSE
_auto_bmr_sess_threshold       30                        TRUE      FALSE      FALSE
_auto_bmr_sys_threshold        100                       TRUE      FALSE      FALSE

7 rows selected.

下面我们做一个实验：
主库上创建实验表
SQL> create table goolen as select * from dba_objects;

Table created.

SQL> select count(*) from goolen;
  COUNT(*)
----------
     74432

SQL> SELECT
  2      dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
  3      dbms_rowid.rowid_block_number(rowid) BLOCKNO
  4      from &table_name where rownum <=10;
Enter value for table_name: goolen
old   4:     from &table_name where rownum <=10
new   4:     from goolen where rownum <=10
   REL_FNO    BLOCKNO
---------- ----------
         1      71969
         1      71969
         1      71969
         1      71969
         1      71969
         1      71969
         1      71969
         1      71969
         1      71969
         1      71969

10 rows selected.

SQL> select dest_name,status,error,target,process from v$archive_dest where substr(dest_name,-1) in (1,2);
DEST_NAME                      STATUS    ERROR                TARGET  PROCESS
------------------------------ --------- -------------------- ------- ----------
LOG_ARCHIVE_DEST_1             VALID                          PRIMARY ARCH
LOG_ARCHIVE_DEST_2             VALID                          STANDBY LGWR

+++备库查看此表是否已经同步到备库
SQL> select count(*) from goolen;

  COUNT(*)
----------
     74432

+++我们看到备库已经把这张表同步过来了
+++下面我们在主库上人为破坏一个块
RMAN> recover datafile 1 block 71969 clear;

Starting recover at 2013-12-27 11:17:02
using channel ORA_DISK_1
Finished recover at 2013-12-27 11:17:03

+++查询一下
SQL> select count(*) from goolen;
  COUNT(*)
----------
     74432
 
++++然后我们查看alter日志中的信息：
Corrupt block relative dba: 0x00411921 (file 1, block 71969)
Bad header found during multiblock buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00411921
 last change scn: 0x0002.1a0a61e7 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x99
 consistency value in tail: 0x61e70601
 check value in block header: 0xa7f1
 computed block checksum: 0x8723
Reading datafile '/tol/oradata/test11g/system01.dbf' for corruption at rdba: 0x00411921 (file 1, block 71969)
Reread (file 1, block 71969) found same corrupt data
Starting background process ABMR
Fri Dec 27 11:17:22 2013
ABMR started with pid=116, OS id=4266 
Auto BMR service is active.
Requesting Auto BMR for (file# 1, block# 71969)
Waiting Auto BMR response for (file# 1, block# 71969)
Auto BMR successful

+++在备库模拟
++++在备库手工损坏一个block
RMAN> recover datafile 1 block 71969 clear;

Starting recover at 2013-12-27 11:17:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1424 device type=DISK
Finished recover at 2013-12-27 11:18:04

+++dbv检测
[oracle@dg_136 ~]$ dbv file=/tol/oradata/test11g/system01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Fri Dec 27 11:20:21 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /tol/oradata/test11g/system01.dbf
Page 71969 is marked corrupt
Corrupt block relative dba: 0x00411921 (file 1, block 71969)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x00411921
 last change scn: 0x0002.1a0a61e7 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x61e70601
 check value in block header: 0x204b
 computed block checksum: 0x82ae

DBVERIFY - Verification complete

Total Pages Examined         : 256000
Total Pages Processed (Data) : 227158
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 14076
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3459
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 11306
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 436888803 (2.436888803)

++++查询这张表
SQL> select count(*) from goolen;

  COUNT(*)
----------
     74432

++++但是在备库的alter日志里面没有发现相关的信息