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 automatc repair of corrupt blocks transparent to the user and application. If corruption occurs on the primary database, blockmedia recovery is performed automatically using a good copy of the block from the standby database and vice versa.
OVERVIEW:
– Create and polulate 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 POLULATE 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:
————————————————————————————–
Related links:
11g DataGuard : Automatic Client Failover
———————————-
——————
Thanks ma’am.
how BMR will be activated when lock got corrupted
Hi ABMR is only for logica corruption and not for physical corruption.
I think log files will fall under the physical corruption.