12c pdb下的 ABMR测试 Automatic Block Media Recovery by Active Data Guard

- Automatic Block Media Recovery by Active Data Guard




ABMR 特性从 11g 版本开始


ABMR 无需额外设置 db_lost_write_protect参数.
ABMR 默认打开 "_auto_bmr" (默认为enabled)




[oracle@wh ~]$ ./orz.sh param | grep db_lost
db_lost_write_protect                    TRUE      FALSE IMMEDIATE NONE




[oracle@wh ~]$ ./orz.sh _param | grep bmr
_auto_bmr                                enabled
_auto_bmr_req_timeout                    60
_auto_bmr_sess_threshold                 30
_auto_bmr_pub_timeout                    10
_auto_bmr_fc_time                        60
_auto_bmr_bg_time                        3600
_auto_bmr_sys_threshold                  100
_auto_bmr_max_rowno                      1024
_dra_bmr_number_threshold                1000
_dra_bmr_percent_threshold               10




-- 查询 block size
SQL> show parameter db_block_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192


-- 在pdb中创建测试表
select name from v$pdbs;


alter session set container=POC_PDB;


create table ambr tablespace users as select * from dba_objects;




-- 查询 测试表的 block
select rowid, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_relative_fno(rowid) fno
from ambr
where rownum < 100
/




ROWID                 BLOCKNO        FNO
------------------ ---------- ----------
AAAWcWAAJAAAACEAAM        132          9
AAAWcWAAJAAAACEAAN        132          9






-- 关闭ambr特性
alter session set Container=CDB$ROOT;
alter system set "_auto_bmr"=disabled;




-- 
dd if=/dev/zero of=/u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf count=1 seek=132 bs=8192 conv=notrunc






-- 
alter session set Container=poc_pdb;


SQL> select count(*) from ambr;
select count(*) from ambr
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 132)
ORA-01110: data file 9: '/u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf'


-- 如果没有出现块折断问题,则 alter system flush buffer_cache; 一次.


SQL> drop table ambr purge; -- 由于没有rman备份,不能做块恢复,只能删除问题表.


Table dropped.








-- 开启ambr功能
alter session set Container=CDB$ROOT;
alter system set "_auto_bmr"=enabled;




-- 在pdb中创建测试表
select name from v$pdbs;


alter session set container=POC_PDB;


create table ambr tablespace users as select * from dba_objects;




-- 查询 测试表的 block
select rowid, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_relative_fno(rowid) fno
from ambr
where rownum < 100
/




ROWID                 BLOCKNO        FNO
------------------ ---------- ----------
AAAWcWAAJAAAACEAAM        132          9
AAAWcWAAJAAAACEAAN        132          9


-- 


dd if=/dev/zero of=/u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf count=1 seek=132 bs=8192 conv=notrunc


--
alter session set container=POC_PDB;


SQL> select count(*) from ambr;


  COUNT(*)
----------
     90991




-- 启用 _auto_bmr 后, 执行 select count(*) from ambr; 后台日志的如下;
- 从日志可以看到, 修复block 是由 ABMR 进程实现的.
- ABMR 只有在出现block块需要修复才会启动.
- ABMR 进程修复完成后,不会立即消失,会保持一段时间.


ALTER SYSTEM SET _auto_bmr='ENABLED' SCOPE=BOTH;
2017-02-20 14:41:43.409000 +08:00
Hex dump of (file 9, block 132) in trace file /u01/app/oracle/diag/rdbms/poc_wh/poc/trace/poc_ora_25255.trc
Corrupt block relative dba: 0x02400084 (file 9, block 132)
Completely zero block found during user buffer read
Reading datafile '/u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf' for corruption at rdba: 0x02400084 (file 9, block 132)
Reread (file 9, block 132) found same corrupt data (no logical check)
Starting background process ABMR
ABMR started with pid=33, OS id=25711 
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 9, block# 132)
Automatic block media recovery successful for (file# 9, block# 132)
Automatic block media recovery successful for (file# 9, block# 132)


[oracle@wh ~]$ ps -ef | grep 25711
oracle   25711     1  0 14:41 ?        00:00:00 ora_abmr_poc