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 YGZHOU tablespace.

SQL> create table ygzhou tablespace ygzhou 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 (select distinct dbms_rowid.rowid_block_number(rowid) from ygzhou) 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 f=/u01/app/oradata/db/ygzhou01.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 '/u01/app/oradata/db/ygzhou01.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 ygzhou;
select count(*) from ygzhou
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 13)
ORA-01110: data file 4: '/u01/app/oradata/db/ygzhou01.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 ygzhou;

COUNT(*)
———-
145352