Identifying corrupt blocks

Recently we had a datafile with a number of corrupt blocks and once it was fixed I resolved to try out all the methods available of identifying block corruption. Searching around I came across an excellent blog by   Asif Momen – Practising block recovery which provided me pretty much what I wanted. However I did think it worth adding a blog entry as I wanted to document all the various methods available to identify corruption.  I also wanted to cover how to test causing a corruption when using ASM datafiles.

Create a datafile, add data and find out which block that data is in

 create smallfile tablespace bad_data datafile '+DATA' size 10M;

Create table test (username varchar2(9), password varchar2(6)) tablespace bad_data;

DECLARE

u  VARCHAR2(9);

p  VARCHAR2(6);

BEGIN

FOR jump  IN 1 ..10000  LOOP

u := 'TEST'||jump;

p := 'P'||jump;

insert into test values (u,p);

END LOOP;

commit;

END;

/

PL/SQL procedure successfully completed.

select

rowid ,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot

from test where password='P7777'

ROWID                   OBJID    FILENUM   BLOCKNUM    ROWSLOT

—————— ———- ———- ———- ———-

AAAXaiAAHAAAACdAAr      95906          7        156         43