Real-Time Query and Automatic Block Media Recovery in 11gR2

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)

 

 

About these ads

, , , , , , ,

  1. #1 by Mike Shield on January 18, 2011 - 19:45

    Hi Uwe,

    This may seem trival, but I can’t find the part where you reference the RMAN validate which you referenced in this article

    “After using RMAN to validate as shown also already, I do the Blockrecovery”

    I’m trying to do the blockrecovery from a physical standby, and I can’t get the RMAN syntax right so that the backup can been seen on the primary and the blockrecovery can take place. The backups on the standby have been made to disk, which probably isn’t helping. Any suggestions would be most welcome, or a pointer to where you showed the rman command.

    Many thanks,

    Mike Shield

  2. #2 by Uwe Hesse on January 19, 2011 - 09:00

    Hi Mike,
    the command is
    RMAN> validate check logical database;
    RMAN> blockrecover corruption list;
    The posting where I have shown that is referenced with the link
    http://uhesse.wordpress.com/2010/11/16/dealing-with-oracle-database-block-corruption-in-11g/
    under “dealing with Block Corruption in general” in this posting. All RMAN syntax that you ever need is available from tahiti.oracle.com – give it a try :-)

  3. #3 by sunil bhola on February 9, 2012 - 11:46

    Just to update that the dataguard should be in active dataguard mode i.e. real-time apply. And ABMR is only to datafile blocks not for datafiles hearders.

  4. #4 by Dmitriy Royzenberg on September 5, 2012 - 20:15

    Hi Uwe
    Great post and great website,

    I’d like to understand how it works internally.

    Does the ABMR requires a flashback to be enabled, or it is would be sufficient to enable Active DG w/o flashback?

    Thanks,
    Dmitriy Royzenberg

  1. Automatic Block Media Recovery in Action on Video « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,635 other followers

%d bloggers like this: