Automatic block media recovery to the rescue

Automatic block media recovery to the rescue

As pointed out earlier, Oracle Active Data Guard (ADG) is a lot more than just the read-only standby database. One of the technologies no other product beats us on in regards to disaster recovery is the automatic block media recovery, which is part of the Active Data Guard license. This gets automatically enabled when you open the standby database read-only with redo apply enabled. You do not need to do something specific for it, it is just there.

In spite of how critical it is to prevent data corruption from the perspective of most DBAs, this is ironically a pretty underestimated feature. It is important to keep in mind that although it is rare, database block corruption still happens. No matter what anyone tells you, it still happens in today’s world. The good thing is that you can avoid painful downtime and manual labor with Active Data Guard. You obviously don’t want to try this in your 24/7 OLTP mission critical database. Therefore, I will ruin (and have it automatically repaired) my Sandbox test database.

We have a few little prerequisites:
1) Have ADG running in read only with apply
2) (for the test, not the feature) have standby_file_management on AUTO.

Intro – Prerequisites

We will use my vbox demo environment here, as that uses the file system and will simplify introducing a corruption (thanks Kamil for the help with the DD command). You can do it with ASM too. However, for demo purposes, this is just easier. So, there you see that ASM adds a layer of additional protection to your data files.

My primary database

my standby database

Then we verify the database roles and check if ADG is active.
My primary database:

And of course my standby too:

So, we are good to go and our database is fully protected against corrupt database blocks.

I have created a few little scripts to avoid the typing. The first one creates a new tablespace. If you use / copy / paste it, it creates it in /home/oracle with the size of 1M.
It then creates a table in the tablespace, adds a record and commits it. We make sure it gets into the data files and then we retrieve the block_id from the row. This we will need in the next step, corruption of this row.

The script 01_abmr.sql

When I run it on my primary database inside the PDB it gives me following output:

Remember the 15 (in my case) as the block_id. This is where in the data file our record is stored.

In the meanwhile, you see the following in the alert log from primary:

That the new data file has been added to the PDB and in the standby we see:

The new data file has been added to the media recovery, so when we query the standby, we see that the inserted row is there as well:

Time for corruption

We will use 02_abmr.sql for this.

All it does, is just clean out our record in the datafile. So when we run it, it asks the block_id and that is the. number from the last script

The recovery

Or should we say Active Data Guard to the rescue. As this is a test system, not a lot activity and so on, we need to flush the buffer cache and then we access the row. ADG will see that we have block corruption and we will fetch the correct block from the standby and repair the primary, not causing outages.

You could guess, this is 03_abmr.sql to show this

So when we run it

Apparently nothing happened. You think! However, when you look in alert log, you will see what happened behind the scenes:

So, you see that Oracle fixes the problem before the user even sees the problem. This works vice versa as well of course, when the standby faces a corrupt block, it will be fetched and repaired again from the primary.


This feature which is automatically enabled as part of Active Data Guard offers a critical extra layer of protection against disasters. The more layers you add in the storage system, the higher the chance that something can go wrong. You can protect yourself against this by having Active Data Guard enabled from the beginning for a full protection of your primary and standby system.

Leave a Reply

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

12 + two =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: