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
———————————-
——————