Oracle ABMR(Automatic Block Media Repair)测试笔记

Oracle ABMR(Automatic Block Media Repair)测试笔记

查看相关参数

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 1011 rows selected.

不同场景测试

1、dd 清空数据块

---主库 object_id=10这条记录存储在FILE# 4,BLOCK 203SQL> 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=11+0 records in1+0 records out---查看块数据有没有ABCDE,验证是不是刚刚更新的块[oracle@testa ~]$ hexdump -C t.dat |grep -i ABCDE00000460 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=notrunc1+0 records in1+0 records out8192 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 contactLNS: Failed to archive log 3 thread 1 sequence 12 (3135)Tue Mar 17 22:10:53 2020ALTER SYSTEM: Flushing buffer cacheTue Mar 17 22:11:12 2020Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1938.trcCorrupt block relative dba: 0x010000cb (file 4, block 203)Completely zero block found during user buffer readReading 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 ABMRTue Mar 17 22:11:12 2020ABMR 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 2020Errors in file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_bmr0_2236.trc:ORA-17627: ORA-01034: ORACLE not availableORA-27101: shared memory realm does not existLinux-x86_64 Error: 2: No such file or directoryORA-17629: Cannot connect to the remote database serverAutomatic block media recovery failed for (file# 4, block# 203) [No standby available]Tue Mar 17 22:11:12 2020Corrupt 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 SegmentErrors 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=notrunc1+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.00164909 s, 5.0 MB/s---再查询正常，完成了auto bmr---db alert logTue Mar 17 22:16:24 2020Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1938.trcCorrupt block relative dba: 0x010000cb (file 4, block 203)Completely zero block found during user buffer readReading 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 2020Automatic 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 logManaged Standby Recovery starting Real Time ApplyParallel Media Recovery started with 2 slavesWaiting for all non-current ORLs to be archived...All non-current ORLs have been archived.Media Recovery Log /arch/stby/arch_1035296583_1_12.arcMedia 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_.logCompleted: alter database recover managed standby database using current logfile disconnect from sessionTue Mar 17 22:24:21 2020ALTER SYSTEM: Flushing buffer cacheTue Mar 17 22:25:05 2020Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/stby/stby/trace/stby_ora_2306.trcCorrupt block relative dba: 0x010000cb (file 4, block 203)Completely zero block found during user buffer readReading 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 2020Errors in file /oracle/app/oracle/diag/rdbms/stby/stby/trace/stby_pr02_2342.trc:ORA-17627: ORA-01017: invalid username/password; logon deniedORA-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 logTue Mar 17 22:26:12 2020Sweep [inc][4953]: completedHex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/stby/stby/incident/incdir_4953/stby_m000_2446_i4953_a.trcCorrupt block relative dba: 0x010000cb (file 4, block 203)Completely zero block found during validationReread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt dataReread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt dataReread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt dataReread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt dataReread of blocknum=203, file=/oracle/app/oracle/oradata/STBY/datafile/o1_mf_users_h70tx1p3_.dbf. found same corrupt dataTue Mar 17 22:26:12 2020Dumping diagnostic data in directory=[cdmp_20200317222612], requested by (instance=1, osid=2306), summary=[incident=4953].Tue Mar 17 22:27:23 2020Hex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/stby/stby/trace/stby_ora_2306.trcCorrupt block relative dba: 0x010000cb (file 4, block 203)Completely zero block found during user buffer readReading 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 2020Automatic 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 logHex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1966.trcCorrupt block relative dba: 0x010000cb (file 4, block 203)Bad check value found during user buffer read <<<

4、修改block tail,制造Fractured block–修改block tail为错误值，可正常auto bmr–db alert logHex dump of (file 4, block 203) in trace file /oracle/app/oracle/diag/rdbms/testa/testa/trace/testa_ora_1966.trcCorrupt block relative dba: 0x010000cb (file 4, block 203)Fractured block found during user buffer read <<<