Recover corrupted blocks using Flashback Logs

Starting with 11g Oracle can perform block media recovery using flashback logs to get good copies of the blocks.

Flashback database is not enabled by default so first step would be to enable it. When enabled a process RVWR (Recovery Writer) copies modified blocks to flashback buffer. Later this buffer is flushed to disk (flashback logs). It is important to notice that flashback logging is not getting log of a changes but complete block images.


Enable flashback database.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;

ORACLE instance started.

Total System Global Area  467652608 bytes
Fixed Size                  2160752 bytes
Variable Size             180357008 bytes
Database Buffers          281018368 bytes
Redo Buffers                4116480 bytes
Database mounted.
SQL>
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

Create small table and simulate block corruption.

SQL> create table test_corrupt (id number, text varchar2(100)) tablespace users;

Table created.

SQL> insert into test_corrupt values (5,'Marko');

1 row created.

SQL> commit;

Commit complete.

SQL> select id,text from test_corrupt;
 
        ID TEXT
---------- ----------
         5 Marko

Simulate data block corruption.

set heading off
set lines 113
SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
       to_number(S.HEADER_BLOCK + 1) || ' << EOF',
       'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
       'EOF'
  FROM DBA_SEGMENTS S, dba_data_files f
 WHERE f.tablespace_name = 'USERS'
   and S.SEGMENT_NAME = 'TEST_CORRUPT';


dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=523 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

-bash-3.2$ dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=523 << EOF
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.0265188 seconds, 4.2 kB/s

Check block corruption.

SQL> alter system flush buffer_cache;

System altered.

SQL> select id,text from test_corrupt;
select id,text from test_corrupt
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 523)
ORA-01110: data file 4: '/u01/app/oradata/testdb/users01.dbf'

We can try to perform block media recovery using RECOVER command.
RMAN will search flashback logs for good past image of the block. After good copy of the block is found, it is restored and recovered with information from redo logs.

But this will not work always.
In some cases flashback logs could be used to speed up block media recovery, but you shouldn't rely only on flashback logs.

RMAN> recover datafile 4 block 523;

Starting recover at 17-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
searching flashback logs for block images
finished flashback log search, restored 0 blocks

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/17/2013 10:30:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore

In this case Oracle tried to find good block image in flashback logs but it failed in that attempt. We need valid backup to perform block media recovery.


Let’s try another scenario.

SQL> create table test_corrupt (id number, text varchar2(100)) tablespace users;

Table created.

SQL>  insert into test_corrupt values (5,'Marko');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> update test_corrupt set text='Okram' where id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.


set heading off
set lines 113
SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
       to_number(S.HEADER_BLOCK + 1) || ' << EOF',
       'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
       'EOF'
  FROM DBA_SEGMENTS S, dba_data_files f
 WHERE f.tablespace_name = 'USERS'
   and S.SEGMENT_NAME = 'TEST_CORRUPT';

dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=523 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

-bash-3.2$ dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=131 << EOF
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF

0+1 records in
0+1 records out
112 bytes (112 B) copied, 7.5846e-05 seconds, 1.5 MB/s

In this scenario I have different block corrupted.

SQL> select id, text from test_corrupt;
select id, text from test_corrupt
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 131)
ORA-01110: data file 4: '/u01/app/oradata/testdb/users01.dbf'

Perform block media recovery.

RMAN> recover datafile 4 block 131;

Starting recover at 17-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
searching flashback logs for block images
finished flashback log search, restored 1 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 17-MAY-13

You can notice that this time Oracle found good block image in flashback logs and it completed recovery successfully.

SQL> select id, text from test_corrupt;

ID TEXT
-- --------------------
 5 Okram

Flashback database is nice opinion that enables faster point in time recoveries, or quicker block recoveries, but it cannot completely replace good valid backup.