Manually Corrupting the Data Block in Linux

Article by author Kamran Agayev Agamehdi

Kamran Agayev Agamehdi  is an Oracle Certified Professional DBA (9i, 10g) with over 5 years experience with UNIX Systems and with Oracle Databases and author of Oracle Backup & Recovery by Rampant Tech Press.

There are times, in order to test the RMAN’s  BLOCKRECOVER command, we need to corrupt the specific data block and recover it for testing purposes. To do it in Linux, use dd command.

In the following example, we will create a table and corrupt it manually (Don’t try it on the production database or you will be out of a job ):

SQL> CREATE TABLE corruption_test (id NUMBER);
Table created.

SQL> INSERT INTO corruption_test VALUES(1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM corruption_test;
        ID
———-
         1

SQL> SELECT header_block FROM dba_segments WHERE segment_name=’CORRUPTION_TEST’;
HEADER_BLOCK
————
          67

[oracle@localhost ~]$ dd of=/u02/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=68 << EOF
> testing corruption
> EOF
0+1 records in
0+1 records out

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SQL> SELECT * FROM corruption_test;
select * from corruption_test
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 68)
ORA-01110: data file 4: ‘/u02/oradata/orcl/users01.dbf’
SQL>

Now connect to the RMAN and recover the data block (not the whole datafile or database) using BLOCKRECOVER command as follows:

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 68;

Starting blockrecover at 01-MAR-10

<… output trimmed … >
<… output trimmed … >

Finished blockrecover at 01-MAR-10

RMAN> exit

Connect to SQL*Plus and query the table:
SQL> SELECT * FROM corruption_test;

        ID
———-
         1

SQL>