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)
Pickleball spielen 
#1 von Mike Shield am Januar 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 von Uwe Hesse am Januar 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
https://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 von sunil bhola am Februar 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 von Dmitriy Royzenberg am 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
#5 von Tamas Uracs am Oktober 17, 2014 - 07:53
Hi Uwe,
I’ve set up Real time query on standby, and I have block corruption on primary, and v$database_block_corruption is populated after validate check. But i seems that the rman command blockrecovery corruption list does clear the view above, but does not read from standby and repairs block corruption. However ABMR is working if I do select from the damaged table.
Do You have any clues what do I miss?
Thanks
Tamas Uracs
#6 von Uwe Hesse am Oktober 17, 2014 - 13:56
Tamas Uracs, so your issue is that RMAN> blockrecover corruption list; is reading blocks from the backup instead of using blocks from the standby?
#7 von Tamas Uracs am Oktober 21, 2014 - 07:53
Hello Uwe,
Yes, thats correct. Actually we arent using rman catalog, and if we try to recover some blocks, there isn’t any backup entry in that standby control file becuase we aer doing backups on the primary.
KR,
Tamas
#8 von Tamas Uracs am Oktober 22, 2014 - 07:15
PS: We discovered block corruption on the standby system after we switched primary role to standby database.
#9 von Massimiliano Lambertini am März 25, 2015 - 18:23
Hello Uwe, I have an issue similar to Tamas’s. When I set up a block recovery situation my primary always ends up recoverying from the local full backup. As per internal requirements, our dataguard components — primary and standby use control file as their RMAN repository. Therefore, I’d like to know that block recovery using physical standby requires a recovery catalog. Thanks in advance for an answer.
#10 von Uwe Hesse am April 29, 2015 - 15:35
Hi Massimiliano, no a recovery catalog database is not required for block recovery with a physical standby. I have used none myself for the demo in the posting here.
#11 von Max Scalf am Juni 5, 2015 - 21:06
Hi Uwe,
Great post as always, I know this is a old post but wanted to find out something. you mentioned below
„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.“
From above i am understanding that as Active data guard has real time query, ABMR, BCT on standby. So dont you need to get license for active data guard for this to work/supported?
what i am trying to understand is, i know we are NOT licensed for active data guard. Can i use this feature or no ?
Also another question is, if i have my primary data blocks are corrupted wouldnt that be replicated to my physical standby ?? or your example corrupt stuff just at OS level and hence redo stream is not affect ??
#12 von Uwe Hesse am Juni 30, 2015 - 07:06
Max, it was always Oracle’s policy that all the features are available, regardless whether you have licensed them or not. It is your responsibility to ensure that only licensed features are in use, also but not only for Active Data Guard.
A physical corruption happens at the storage level and not in the database level, so the redo entries on the primary don’t contain the corruption, hence it is not shipped to the standby. A logical corruption like stupid insert/update/delete will be replicated on the standby, that’s true.
#13 von Anuj Sharma am September 24, 2015 - 12:25
Hi Uwe,
So in nutshell what I understood for ABMR its again a paid feature with Active Data Guard.
Anuj