Oracle Dataguard Auto Block Recovery

In this post, I will demonstrate automatic block media recovery introduced in 11g Data Guard. This feature can be used only if active dataguard is enabled. This feature enables the automatic repair of corrupt blocks transparent to the user and application. If corruption occurs on the primary database, block media recovery is performed automatically using a good copy of the block from the standby database and vice versa.

OVERVIEW:
– Create and populate a test table on primary
– Corrupt two blocks containing table data
– Check that blocks corrupted using dbv
– Flush buffer cache and issue query for corrupt blocks
– The query succeeds as blocks have been repaired automatically
– Verify automatic BMR using

. Alert log of Primary
. dbv utility
— IMPLEMENTATION –
– Create a test tablespace on primary


PRI> drop tablespace test including contents and datafiles;

create tablespace test datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ size 30M;

– CREATE A TEST USER ON PRIMARY

PRI> create user test 
     identified by test 
     default tablespace test;
grant connect, resource to test;

– CREATE AND POPULATE TABLE TEST_TAB IN TEST TABLESPACE ON PRIMARY
PRI> Create table test.test_tab as select * from hr.employees;
insert into test.test_tab select * from test.test_tab;
/
commit;

select table_name, 
       tablespace_name 
  from dba_tables 
 where table_name=’TEST_TAB';
select count(*) 
  from test.test_tab;
– CHECK THE LEAST BLOCK OCCUPIED BY THE TABLE IN DATAFILE
PRI> select min(dbms_rowid.rowid_block_number(rowid))
       from test.test_tab;
MIN(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
—————————————–
131
– corrupt two blocks
#dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=136 count=2
– check if datafile corrupted

[oracle@host1 ~]$ dbv file=/u01/app/oracle/oradata/orcl/test01.dbf blocksize=8192
Page 136 is marked corrupt
Corrupt block relative dba: 0x01800088 (file 6, block 136)
Completely zero block found during dbv:
Page 137 is marked corrupt
Corrupt block relative dba: 0x01800089 (file 6, block 137)
Completely zero block found during dbv:
Total Pages Marked Corrupt : 2
-- FLUSH BUFFER CACHE AND ISSUE QUERY ON CORRUPTED BLOCKS
- QUERY SUCCEEDS AS BLOCKS HAVE BEEN REPAIRED AUTOMATICALLY

PRI>alter system flush buffer_cache;
select count(*) from test.test_tab;
– CHECK THE ALERT LOG OF PRIMARY DATABASE
# tailf /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
——————
Tue Dec 18 12:43:01 2012
ALTER SYSTEM: Flushing buffer cache
Tue Dec 18 12:43:26 2012
– BLOCK 136 IS FOUND CORRUPT AND IS DUMPED IN TRACE FILE
Hex dump of (file 6, block 136) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12608.trc
Corrupt block relative dba: 0x01800088 (file 6, block 136)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ for corruption at rdba: 0x01800088 (file 6, block 136)
Reread (file 6, block 136) found same corrupt data
– REQUEST FOR AUTO BMR SENT FOR BLOCK 136
Requesting Auto BMR for (file# 6, block# 136)
– BLOCK 137 IS FOUND CORRUPT AND IS DUMPED IN TRACE FILE
Hex dump of (file 6, block 137) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12608.trc
Corrupt block relative dba: 0x01800089 (file 6, block 137)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ for corruption at rdba: 0x01800089 (file 6, block 137)
Reread (file 6, block 137) found same corrupt data
– REQUEST FOR AUTO BMR SENT FOR BLOCK 136
Requesting Auto BMR for (file# 6, block# 137)
– AUTO BMR SUCCESSFUL FOR BLOCK 136
Waiting Auto BMR response for (file# 6, block# 136)
Auto BMR successful
– AUTO BMR SUCCESSFUL FOR BLOCK 137
Waiting Auto BMR response for (file# 6, block# 137)
Auto BMR successful
– CHECK THAT BLOCKS HAVE BEEN REPAIRED
oracle@host1 ~]$ dbv file=/u01/app/oracle/oradata/orcl/test01.dbf blocksize=8192
Total Pages Marked Corrupt : 0

Thanks for your time. Your comments and suggestions are welcome !!!
References:

ABMR – Automatic Block Media Recovery

————————————————————————————–
Related links:
HOME
11g Dataguard Index
11g DataGuard : Automatic Client Failover

11g DataGuard : Automatic Job Failover
11g DataGuard: Flashback Standby After Resetlogs On Primary
Flashback Through Role Transition For Physical Standby
Recover Standby Datafile From Primary
———————————-

——————

Leave a Reply

Your email address will not be published. Required fields are marked *