In addition to the real time query capability of the 11g Active Data Guard feature, we can also add to our high availability capability by using the Automatic Block Media Repair feature whereby data block corruptions on the Primary database can be repaired by obtaining those blocks from the standby site – all performed by a background process (ABMR) transparent to the application.

The same functionality can be used to repair block corruptions on the Active Standby site by applying blocks which are conversely now received from the Primary site.

Let us see a test case of the same.

We create a test table and assign it to the USERS tablespace.

SQL> create table myobjects
2 tablespace users
3 as select * from all_objects;

Table created.

Using DBMS_ROWID, we determine the blocks which this table occupies (if you like, just restrict the query to the first 5 blocks in case the table contains many blocks)

SQL> select * from
2 (select distinct dbms_rowid.rowid_block_number(rowid)
3 from myobjects)
4 where rownum < 6; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ------------------------------------ 12 13 14 15 16 We can then corrupt any one of these blocks (in our case block 13) to simulate a block corruption - don't do this in production! dd of=/u03/oradata/testdb/users01.dbf bs=8192 seek=13 conv=notrunc count=1 if=/dev/zero

We now run a query on the Primary database (after flushing the buffer cache first to force a new data block read) and even though we have corrupted a data block, the query completes without an ORA-01578 block corruption error – we notice a slight glitch while the blocks are transported over the network.

But if we examine the database alert log, we will see that a block corruption was detected, but a background process (ABMR) was started which repaired the corrupt blocks.

ALTER SYSTEM: Flushing buffer cache
Fri Sep 24 10:45:18 2010
Corrupt block relative dba: 0x0100008b (file 4, block 13)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u03/oradata/testdb/users01.dbf’ for corruption at rdba: 0x0100008b (file 4, block 13)
Reread (file 4, block 13) found same corrupt data
Starting background process ABMR
Fri Sep 24 10:45:18 2010
ABMR started with pid=40, OS id=6369
Auto BMR service is active.
Requesting Auto BMR for (file# 4, block# 13)
Waiting Auto BMR response for (file# 4, block# 13)
Auto BMR successful

Let us see how the same scenario pans out on the Active Standby site.

We run the same block corruption ‘dd’ command now on the standby host and when we run the query the first time, we will get an error as shown below.

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from myobjects;
select count(*) from myobjects
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 13)
ORA-01110: data file 4: ‘/u03/oradata/testdb/users01.dbf’

But if we run the same query again, we will not see any error as the blocks have now been repaired from the Primary database site.

SQL> select count(*) from myobjects;


Please follow and like us:

Last Update: May 30, 2020  

September 28, 2010 214 Gavin Soorma
Total 0 Votes:

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , ,

Leave a Comment

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


Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?