Block Media Recovery of Standby WITHOUT ACTIVE DATAGUARD

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.

SQL> CREATE TABLESPACE damaged_ts
     DATAFILE SIZE 10M;
SQL> CREATE TABLE damaged_table
     TABLESPACE damaged_ts
     AS SELECT * FROM ALL_TABLES;
SQL> SELECT header_file, header_block
     FROM dba_segments
     WHERE segment_name = 'DAMAGED_TABLE';
	 
HEADER_FILE HEADER_BLOCK
----------- ------------
          9          130

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.

RMAN> BACKUP AS COPY TABLESPACE damaged_ts
      FORMAT '/tmp/damaged_ts.bck';
$ dd of=/tmp/damaged_ts.bck bs=8192 conv=notrunc seek=130 << EOF
a nice text ...
EOF

Now this corrupt file is foisted on the database (if the database is located in the file system this is not necessary).

SQL> ALTER TABLESPACE damaged_ts OFFLINE; 
RMAN> RUN { 
           SET MAXCORRUPT FOR DATAFILE 9 TO 2; 
           RESTORE TABLESPACE damaged_ts; 
           RECOVER TABLESPACE damaged_ts; 
        }; 
SQL> ALTER TABLESPACE damaged_ts ONLINE;

Let's see if the file is actually damaged...

SQL> SELECT * FROM damaged_table;
SELECT * FROM damaged_table
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 130)
ORA-01110: data file 9: '+DATA01/jalin11/datafile/damaged_ts.862.790151237'

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:

SQL> SELECT *
     FROM v$database_block_corruption;
FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         9        130          1                  0 CORRUPT

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.

DGMGRL> EDIT DATABASE 'JALIN11_DG' SET STATE='apply-off';

SQL> ALTER DATABASE OPEN;

Now nothig gets in the way of the recovery anymore.

$ rman
RMAN> CONNECT TARGET sys/manager@JALIN11
RMAN> CONNECT CATALOG rman/manager@RCAT
RMAN> RECOVER CORRUPTION LIST;
Starting recover at 01-AUG-12
using channel ORA_DISK_1
finished standby search, restored 1 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 01-AUG-12

That's it!

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.

Good luck and as always I appreciate comments.