Blog Block Media Recovery of Standby WITHOUT ACTIVE DATAGUARD

21. October 2012 Johannes Ahrends

You can read everywhere how great the Automatic Block Media Recovery of the Oracle 11g Release 2 is. That is true, for sure, but with the disadvantage of therefore having to license the quite expensive Active Data Guard Option. But is that really necessary?

No!
Block Media Recovery works even without Active DataGuard – just not automatically, but you must enter a “very difficult” command.

… so how does it actually work?

The whole procedure probably becomes clearer by an example. Just therefore we first have to “damage” a data file. So a tablespace (corrupts) is created and a simple table (corrupt_table). The header block of the table shall be changed so that by a SELECT command the corruption can be detected.

And now we damage…

When the database files are located in a regular file system, you can easily write the file damaged by the UNIX command dd. If however ASM is used, it is a little bit more difficult. Best you first create a copy of the data file in a “regular” file system (i.e. /tmp) and afterwards clobber the above stated header of the table corrupt_table in this file with something nice.

Recovery with the RMAN and the Standby Database

As you already know from my other blogs I am utter RMAN fan. And here RMAN is unbeatable, too. Though there is the possibility of directly stating blocks that shall be repaired, but you can also tell the RMAN to simply repair all blocks. But therefore the blocks must be detected. Like in the example above by SELECT on the table corrupt_table every corrupt clock found is registered in the tablev$database_block_corruptionand can accordingly be queried, too:

As already mentioned, a standby database shall be used for the recovering and not an already created backup or e.g. flashback logs.

One requirement is however that the database (primary and standby) are registered in the Recovery Manager Catalog, because only via this it can be determined that there is a relationship between the databases. Additionally the standby database must be opened for this action (in read only though).

Important

Before opening the standby database the log apply process must be closed, otherwise the database would be opened as Active Data Guard and Oracle would be happy with additional licenses.

As I I assume that you manage your standby databases with the Data Guard Broker, the log apply should also be closed via the Data Guard Broker and then the database can be opened. The “read only” option is not necessary, because the database “knows” from the control file that it is a standby database.

Only the command RECOVER CORRUPTION LIST takes care that the blocks are being repaired again. By the inconspicuous message “finished standby search” you see that the block of the standby database was read.

Now the standby database just has to be reset to recovery mode and the apply process started again.

Johannes Ahrends

1. It doesn’t matter where you run RMAN but in this case is was the Primary Database
2. NO JALIN11 is the Primary Database
3. The RMAN catalog is required because only the catalog knows that there are two identical databases (the Primary and the standby). Due to the catalog Information the blocks are read from the standby and recovered to the Primary.