Yesterday, I posted about dealing with Block Corruption in general. Special case is Block Corruption in a Data Guard Environment, where we introduced the New Feature Automatic Block Media Recovery (ABMR). Yes, another cool abbrevation from the  the Oracle Realm 🙂

To demonstrate this, I have created a Data Guard Configuration as described in my whitepaper 11g Data Guard on the command line, that you may get from the Downloads page. It’s still 11gR1 but can easily be adopted for 11gR2. Will publish an 11gR2 version of it there soon. My scenario looks like this:

SYS@prima > select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
DGMGRL> show configuration;

Configuration - myconf

 Protection Mode: MaxAvailability
 Databases:
 prima - Primary database
 physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database physt;

Database - physt

 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   0 seconds
 Apply Lag:       0 seconds
 Real Time Query: ON
 Instance(s):
 physt

Database Status:
SUCCESS

The first 11g New Feature related to Block Corruption in a Data Guard Environment is less striking than ABMR and similar as restoring blocks from Flashback Logs: If we have a Physical Standby Database present, the corrupted blocks can be restored from there very fast. I corrupted the emp table of scott in the same way as in the previous posting. After using RMAN to validate as shown also already, I do the Blockrecovery

RMAN> blockrecover corruption list;

Starting recover at 17-NOV-10
using channel ORA_DISK_1
finished standby search, restored 1 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 17-NOV-10

I need to emphasize that this restore of blocks from the Physical Standby does not require Real-Time Query there – so we don’t need to purchase Active Data Guard for that. But ABMR does. First ABMR scenario: Block Corruption happens on the Primary Database. I do the same steps as shown in the previous posting to corrupt the block containing the emp table of scott. Now scott connects and selects on the emp table. After a short (about 1 sec) break, the select produces the correct result set! The alert log file of the Primary Database records the following:

Wed Nov 17 08:54:29 2010
Hex dump of (file 4, block 131) in trace file /home/oracle/prima/diag/rdbms/prima/prima/trace/prima_ora_5169.trc
Corrupt block relative dba: 0x01000083 (file 4, block 131)
Bad header found during buffer read
Data in bad block:
 type: 0 format: 2 rdba: 0xffc00000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0xff66
 block checksum disabled
Reading datafile '/home/oracle/prima/users01.dbf' for corruption at rdba: 0x01000083 (file 4, block 131)
Reread (file 4, block 131) found same corrupt data (no logical check)
Starting background process ABMR
Wed Nov 17 08:54:29 2010
ABMR started with pid=33, OS id=5174
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 4, block# 131)
Wed Nov 17 08:54:30 2010
Automatic block media recovery successful for (file# 4, block# 131)
Automatic block media recovery successful for (file# 4, block# 131)
WARNING: AutoBMR fixed mismatched on-disk single block ffc00000 with in-mem rdba 1000083.

An end user will probably not even notice the Block Corruption on the Primary Database. Second ABMR scenario: Block Corruption happens on the Physical Standby Database. I destroy the block of the emp table in the same way as on the Primary before. Scott now connects on the Physical Standby and does a select on the emp table. Again a short break – no error message but the correct result set is returned! Alert Log of the Physical Standby records:

Wed Nov 17 08:58:10 2010
Hex dump of (file 4, block 131) in trace file /home/oracle/physt/diag/rdbms/physt/physt/trace/physt_ora_5281.trc
Corrupt block relative dba: 0x01000083 (file 4, block 131)
Bad header found during buffer read
Data in bad block:
 type: 0 format: 2 rdba: 0xffc00000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0xff66
 block checksum disabled
Reading datafile '/home/oracle/physt/users01.dbf' for corruption at rdba: 0x01000083 (file 4, block 131)
Reread (file 4, block 131) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 4, block# 131)
Wed Nov 17 08:58:11 2010
Automatic block media recovery requested for (file# 4, block# 131)
Automatic block media recovery successful for (file# 4, block# 131)
Automatic block media recovery successful for (file# 4, block# 131)

Conclusion: We have just seen another benefit of Real-Time Query. Apart from it’s main purpose of making it possible to offload Read-Only (or even Read-Mostly) Applications to the Physical Standby Database, it is now in 11gR2 also leveraged to resolve Block Corruption fast and transparently. We call this 11gR2 New Feature Automatic Block Media Recovery (ABMR)