Using_block_media_recovery

6.6 Block Media Recovery
6.6.1 Create an ORA-1578 scenario

Compile program  cr-1578.c ( Compilation is   from my Solars 11 box ) :
supsunhh3> make cr-1578
cc    -o cr-1578 cr-1578.c

Create table:
connect scott/tiger
drop table hh_tab;
create table hh_tab (
id number ,
info varchar(68) ) tablespace hh_test;

insert into hh_tab values ( 1, ‘Helmut’);
commit;
select * from hh_tab;

Create checkpoint to flush data to datafile
RMAN>    sql ‘alter tablespace hh_test offline ‘;

Corrupt data:
cr-1578 /u01/app/ora112/oradata/DB1DUP/hh_test_copy.dbf Helmut E
Output:
–>  occurence of  String ‘Helmut’ found and corrupted as ‘Eelmut’

Make tablespace ONLINE again
RMAN> sql ‘alter tablespace hh_test online’;

Use SQL to verify ORA-1578 :
SQL> select * from hh_tab;
select * from hh_tab
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 174)
ORA-01110: data file 4: ‘/u01/app/ora112/oradata/DB1DUP/hh_test_copy.dbf’
6.6.2 Recover a single block  
SQL> select * from hh_tab;
select * from hh_tab
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 174)
ORA-01110: data file 4: ‘/u01/app/ora112/oradata/DB1DUP/hh_test_copy.dbf’

RMAN> recover datafile 4 block 174;

channel ORA_DISK_1: restoring block(s) from datafile copy +BIG_DATA/db1dup/datafile/hh_test.258.807871839

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

Finished recover at 20-FEB-13 16:30:20

SQL> select * from hh_tab;

ID INFO
———- ——————————————————————–
1 Helmut

Full output from used Recovery commands !

6.6.3 Recover a list of blocks
6.6.3.1 Create tables and ORA-1578 errors
SQL @cr_tab2

RMAN>  sql ‘alter tablespace hh_test offline ‘;
supsunhh3%  cr-1578 /u01/app/ora112/oradata/DB1DUP/hh_test_copy.dbf Table1 A
–>  occurence of  String ‘Table1’ found and corrupted as ‘Aable1’
supsunhh3%  cr-1578 /u01/app/ora112/oradata/DB1DUP/hh_test_copy.dbf Table2 B
–>  occurence of  String ‘Table2’ found and corrupted as ‘Bable2’
RMAN>  sql ‘alter tablespace hh_test online’;
SQL> select * from hh_tab1;
ORA-01578: ORACLE data block corrupted (file # 4, block # 230)
ORA-01110: data file 4: ‘/u01/app/ora112/oradata/DB1DUP/hh_test_copy.dbf’

SQL>  select * from hh_tab2;
ORA-01578: ORACLE data block corrupted (file # 4, block # 238)
ORA-01110: data file 4: ‘/u01/app/ora112/oradata/DB1DUP/hh_test_copy.dbf’

6.6.3.2 Recovering multiple blocks using Recover Corruption List

RMAN> backup validate check logical database;
SQL> select * from V$database_block_corruption;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
4        230          1                  0 CHECKSUM
4        238          1                  0 CHECKSUM

RMAN> recover corruption list;

Starting recover at 20-FEB-13 17:24:47
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: restoring block(s) from datafile copy +BIG_DATA/db1dup/datafile/hh_test.258.807871839

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

Finished recover at 20-FEB-13 17:25:04

SQL> select * from V$database_block_corruption;
no rows selected

Full output from used Recovery commands !

6.6.4 Lessons learned in this section

• You can use recover corruption list to recover from mulitple ORA-1578 error
• V$database_block_corruption gives you a good idea about the block corruption type
• After a successfull block recovery V$database_block_corruption will be cleaned