查看相关参数
SQL> @p bmr
NAME VALUE
_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
_bmr_prefered_standby
_auto_bmr_max_rowno 1024
_dra_bmr_number_threshold 1000
_dra_bmr_percent_threshold 10
11 rows selected.
不同场景测试
1、dd 清空数据块
---主库 object_id=10这条记录存储在FILE# 4,BLOCK 203
SQL> create table dbmt.obj as select * from dba_objects;
SQL> update dbmt.obj set object_name='ABCDE' where object_id=10;
1 row updated.
SQL> commit;
Commit complete.
--备库dd出object_id=10所在的块，最好flush buffer_cache后
[oracle@testa ~]$ dd if=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf of=./t.dat bs=8192 skip=203 count=1
1+0 records in
1+0 records out
---查看块数据有没有ABCDE,验证是不是刚刚更新的块
[oracle@testa ~]$ hexdump -C t.dat |grep -i ABCDE
00000460 03 0e 03 53 59 53 05 41 42 43 44 45 ff 02 c1 0b |...SYS.ABCDE....|
[oracle@testa ~]$
--备库dd把块清空
[oracle@testa ~]$ dd of=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf if=/dev/zero bs=8192 seek=203 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000294364 s, 27.8 MB/s
---依然可以查出
select * from dbmt.obj where object_id=10;
--主再次update,object_name='abcde'
SQL> update dbmt.obj set object_name='abcde' where object_id=10;
1 row updated.
SQL> commit;
Commit complete.
---依然可以查出，object_name变成了小写
select * from dbmt.obj where object_id=10;
--此时查询alert日志，无相关日志输出，估计ORACLE自动从主读取了该数据块
--dd清空主库数据块后依然可以正常查，无报错，无日志输出
--把备库关了后，主再dd数据块，查询alert 报错如下
SQL> select * from dbmt.obj where object_id=10;
select * from dbmt.obj where object_id=10
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 203)
ORA-01110: data file 4: '/oracle/app/oracle/oradata/TESTA/datafile/o1_mf_users_h70tx1p3_.dbf'
---主库db alert log输出
Error 3135 for archive log file 3 to 'stby'
Errors in file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_nsa2_1952.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 3 thread 1 sequence 12 (3135)
Tue Mar 17 22:10:53 2020
ALTER SYSTEM: Flushing buffer cache
Tue Mar 17 22:11:12 2020
Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1938.trc
Corrupt block relative dba: 0x010000cb (file 4, block 203)
Completely zero block found during user buffer read
Reading datafile '/oracle/app/oracle/oradata/TESTA/datafile/o1_mf_users_h70tx1p3_.dbf' for corruption at rdba: 0x010000cb (file 4, block 203)
Reread (file 4, block 203) found same corrupt data (no logical check)
Starting background process ABMR
Tue Mar 17 22:11:12 2020
ABMR started with pid=31, OS id=2234
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 4, block# 203)
Tue Mar 17 22:11:12 2020
Errors in file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_bmr0_2236.trc:
ORA-17627: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
ORA-17629: Cannot connect to the remote database server
Automatic block media recovery failed for (file# 4, block# 203)
[No standby available]
Tue Mar 17 22:11:12 2020
Corrupt Block Found
TSN = 4, TSNAME = USERS
RFN = 4, BLK = 203, RDBA = 16777419
OBJN = 87380, OBJD = 87380, OBJECT = OBJ, SUBOBJECT =
SEGMENT OWNER = DBMT, SEGMENT TYPE = Table Segment
Errors in file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1938.trc (incident=42153):
ORA-01578: ORACLE data block corrupted (file # 4, block # 203)
ORA-01110: data file 4: '/oracle/app/oracle/oradata/TESTA/datafile/o1_mf_users_h70tx1p3_.dbf'
---把备库起来， 开启real time apply
---主再次dd该块
[oracle@testa ~]$ dd of=/oracle/app/oracle/oradata/TESTA/datafile/o1_mf_users_h70tx1p3_.dbf if=/dev/zero bs=8192 seek=203 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00164909 s, 5.0 MB/s
---再查询正常，完成了auto bmr
---db alert log
Tue Mar 17 22:16:24 2020
Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1938.trc
Corrupt block relative dba: 0x010000cb (file 4, block 203)
Completely zero block found during user buffer read
Reading datafile '/oracle/app/oracle/oradata/TESTA/datafile/o1_mf_users_h70tx1p3_.dbf' for corruption at rdba: 0x010000cb (file 4, block 203)
Reread (file 4, block 203) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 4, block# 203)
Tue Mar 17 22:16:24 2020
Automatic block media recovery successful for (file# 4, block# 203)
Automatic block media recovery successful for (file# 4, block# 203)
2、主改密码,密码文件不一致,但DG传输未中断，还在继续，dd清空数据块
---备库flush buffer cache
---dd 清空块后查询，不能auto bmr
---db alert log
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /arch/stby/arch_1035296583_1_12.arc
Media Recovery Waiting for thread 1 sequence 13 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 13 Reading mem 0
Mem# 0: /oracle/app/oracle/oradata/STDBY/onlinelog/o1_mf_5_h713vwvw_.log
Completed: alter database recover managed standby database using current logfile disconnect from session
Tue Mar 17 22:24:21 2020
ALTER SYSTEM: Flushing buffer cache
Tue Mar 17 22:25:05 2020
Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/stby/stby/trace/stby_ora_2306.trc
Corrupt block relative dba: 0x010000cb (file 4, block 203)
Completely zero block found during user buffer read
Reading datafile '/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf' for corruption at rdba: 0x010000cb (file 4, block 203)
Reread (file 4, block 203) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 4, block# 203)
Tue Mar 17 22:25:06 2020
Errors in file /oracle/app/oracle/diag/rdbms/stby/stby/trace/stby_pr02_2342.trc:
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
---密码文件复制成一致的后,再次查询可正常完成auto bmr（不用重启MRP）
SQL> select * from dbmt.obj where object_id=10;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ------------ ------------ ------------------- ------- - - - ---------- ------------------------------
SYS abcde 10 10 CLUSTER 24-AUG-13 24-AUG-13 2013-08-24:11:37:35 VALID N N N 5
---db alert log
Tue Mar 17 22:26:12 2020
Sweep [inc][4953]: completed
Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/stby/stby/incident/incdir_4953/stby_m000_2446_i4953_a.trc
Corrupt block relative dba: 0x010000cb (file 4, block 203)
Completely zero block found during validation
Reread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt data
Reread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt data
Reread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt data
Reread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt data
Reread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt data
Tue Mar 17 22:26:12 2020
Dumping diagnostic data in directory=[cdmp_20200317222612], requested by (instance=1, osid=2306), summary=[incident=4953].
Tue Mar 17 22:27:23 2020
Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/stby/stby/trace/stby_ora_2306.trc
Corrupt block relative dba: 0x010000cb (file 4, block 203)
Completely zero block found during user buffer read
Reading datafile '/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf' for corruption at rdba: 0x010000cb (file 4, block 203)
Reread (file 4, block 203) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 4, block# 203)
Tue Mar 17 22:27:24 2020
Automatic block media recovery requested for (file# 4, block# 203)
Automatic block media recovery successful for (file# 4, block# 203)
Automatic block media recovery successful for (file# 4, block# 203)
3、checksum错误
–bbed 修改数据块checksum为错误值，可正常auto bmr
—alert log
Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1966.trc
Corrupt block relative dba: 0x010000cb (file 4, block 203)
Bad check value found during user buffer read <<<<
Data in bad block:
type: 6 format: 2 rdba: 0x010000cb
last change scn: 0x0000.00122ca6 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x2ca60601
check value in block header: 0x33
computed block checksum: 0x338d
Reading datafile ‘/oracle/app/oracle/oradata/TESTA/datafile/o1_mf_users_h70tx1p3_.dbf’ for corruption at rdba: 0x010000cb (file 4, block 203)
Reread (file 4, block 203) found same corrupt data (no logical check)
Starting background process ABMR
Wed Mar 18 13:07:55 2020
ABMR started with pid=32, OS id=2770
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 4, block# 203)
Wed Mar 18 13:07:55 2020
Automatic block media recovery successful for (file# 4, block# 203)
Automatic block media recovery successful for (file# 4, block# 203)
4、修改block tail,制造Fractured block
–修改block tail为错误值，可正常auto bmr
–db alert log
Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1966.trc
Corrupt block relative dba: 0x010000cb (file 4, block 203)
Fractured block found during user buffer read <<<<
Data in bad block:
type: 6 format: 2 rdba: 0x010000cb
last change scn: 0x0000.00122ca6 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x2ca60602
check value in block header: 0x33be
computed block checksum: 0x3
Reading datafile ‘/oracle/app/oracle/oradata/TESTA/datafile/o1_mf_users_h70tx1p3_.dbf’ for corruption at rdba: 0x010000cb (file 4, block 203)
Reread (file 4, block 203) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 4, block# 203)
Wed Mar 18 13:11:27 2020
Automatic block media recovery successful for (file# 4, block# 203)
Automatic block media recovery successful for (file# 4, block# 203)
