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.
Create small table and simulate block corruption.
Simulate data block corruption.
Check block corruption.
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.
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.
In this scenario I have different block corrupted.
Perform block media recovery.
You can notice that this time Oracle found good block image in flashback logs and it completed recovery successfully.
Flashback database is nice opinion that enables faster point in time recoveries, or quicker block recoveries, but it cannot completely replace good valid backup.
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.
