11g Active Dataguard的Automatic Block Repair特性

11g出来这么多年了，虽然早就知道这个特性，但一直也没有亲自测试一下，今天正好有业务需求，简单测试了下，记录之。

1. 在主库中创建测试用户和测试表(test.adg)：
create user test identified by test;

 

create tablespace test datafile '+dg_data' size 100m autoextend off;

 

create table test.adg tablespace test as select * from dba_users;
insert into  test.adg select * from dba_users;
commit;

 

select rowid,
     dbms_rowid.rowid_relative_fno(rowid) rel_fno,       
     dbms_rowid.rowid_block_number(rowid) blockno, 
      dbms_rowid.rowid_row_number(rowid) rowno
         from test.adg    
order by rowid;

 

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAVbKAAGAAAACFAAY          6        133         24
AAAVbKAAGAAAACFAAZ          6        133         25
AAAVbKAAGAAAACFAAa          6        133         26
AAAVbKAAGAAAACFAAb          6        133         27
AAAVbKAAGAAAACFAAc          6        133         28
AAAVbKAAGAAAACFAAd          6        133         29
AAAVbKAAGAAAACFAAe          6        133         30

 

可以看出，测试数据主要分布在6号数据文件的133号数据块中。

 

 

 

2. 查看6号数据文件所在位置及文件大小：
SQL> select file#||' '||name||' '||bytes from v$datafile ;

 

FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 +DG_DATA/sdswhxcx/datafile/system.278.1119621897 786432000
2 +DG_DATA/sdswhxcx/datafile/sysaux.277.1119621899 555745280
3 +DG_DATA/sdswhxcx/datafile/undotbs1.276.1119621901 94371840
4 +DG_DATA/sdswhxcx/datafile/users.267.1119621901 5242880
5 +DG_DATA/sdswhxcx/datafile/undotbs2.265.1119622095 26214400
6 +DG_DATA/sdswhxcx/datafile/test.301.1119639739 104857600

 

6 rows selected.

 

SQL>

 

 

 

3.将主库的6号数据文件从ASM中复制到文件系统中：

 

sql>alter tablespace test offline;

 

# su - grid

 

ASMCMD> cp +DG_DATA/sdswhxcx/datafile/test.301.1119639739 /tmp/testdg.dbf

 

sql>alter tablespace test online;

 

# chown oracle:oinstall /tmp/testdg.dbf

 

4编译bbed环境，破坏主库6号数据文件的133号数据块.

 

[oracle@19crac1 ~]$ more filelist.txt
6 /tmp/testdg.dbf 104857600

 

[oracle@19crac1 ~]$ more bbed.par
blocksize=8192
listfile=filelist.txt
mode=edit
[oracle@19crac1 ~]$

 

bbed parfile=bbed.par

 

set file 6 block 133

 

modify /x 8888 offset 4

 

5. dbv验证文件系统的6号数据文件已经存在一个坏块。
dbv file=/tmp/testdg.dbf

 

6. 将故意损坏的数据文件拷贝回主库的ASM中：
alter tablespace test offline;

 

ASMCMD> cp /tmp/testdg.dbf +DG_DATA
copying  -> +DG_DATA/testdg.dbf

 

alter database rename file '+DG_DATA/sdswhxcx/datafile/test.301.1119639739' to'+DG_DATA/testdg.dbf';
recover datafile 6;
alter tablespace test online;

 

 

 

7. 备库的dataguard不是real-time apply的情况下（也即不是active dataguard）：
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database disconnect from session;
SQL> select open_mode from v$database;

 

OPEN_MODE
--------------------
READ ONLY WITH APPLY

 

--主库查询仍然会提示坏块
SQL> select count(*) from test.adg;
select count(*) from test.adg
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 133)
ORA-01110: data file 6: '+DG_DATA/testdg.dbf'

 

SQL>

 

8. 备库的dataguard修改为real-time apply的情况下（也即是active dataguard）：
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select open_mode from v$database;

 

OPEN_MODE
--------------------
READ ONLY WITH APPLY

 

--主库查询，坏块自动修复
SQL> select count(*) from test.adg;

 

  COUNT(*)
----------
        62

 

SQL>

 

9.此时主库自动修复坏块的日志如下所示：
Tue Nov 01 19:18:40 2022
Corrupt Block Found
         TSN = 6, TSNAME = TEST
         RFN = 6, BLK = 133, RDBA = 25165957
         OBJN = 87754, OBJD = 87754, OBJECT = ADG, SUBOBJECT =
         SEGMENT OWNER = TEST, SEGMENT TYPE = Table Segment

 

Tue Nov 01 19:19:26 2022
Starting background process ABMR
Tue Nov 01 19:19:26 2022
ABMR started with pid=51, OS id=2443
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 6, block# 133)
Tue Nov 01 19:19:27 2022
Automatic block media recovery successful for (file# 6, block# 133)
Automatic block media recovery successful for (file# 6, block# 133)