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) 标签：Repair,11g,rowid,DG,datafile,133,Dataguard,SQL,test From： https://www.cnblogs.com/missyou-shiyh/p/16851147.html