Solving Oracle Data-Block Corruption

Oracle Certification, Database Administration, SQL, Application, Programming Reference Books Solving Oracle Data-Block Corruption

Easy Solutions for a Startling Problem

Whenever you encounter the following Oracle error messages, you are faced with what at first sight looks like a very serious problem:

ORA-00600: Internal message code, arguments: [01578] [...] [...] [] [] [].
ORA-01578: Oracle data block corrupted (file ..., block ...).

This message tells you that somewhere in your database, a block of data has been corrupted (at the byte level) in such a way that Oracle7 causes the internal exception to be raised each time a SQL statement accesses (reads or writes) that block. This article describes the options available to you for solving the ORA-00600[01578] error. As you'll see, in most cases the problem is easy to solve, although some cases require more effort and time.

IN THE BEGINNING

When you encounter the Oracle data-block corruption, the first thing you need to do is investigate the database segment (name and type) to which the corrupted block is allocated. Chances are that the block belongs either to an index or to a table segment. The following query will reveal the segment that holds the corrupted block identified by and (which were given to you in the error message):

        select ds.*
        from dba_segments ds, sys.uet$ e
        where ds.header_file=e.segfile#
        and ds.header_block=e.segblock#
        and e.header_file=
        and  between e.block# and e.block#+e.length-1;

If the segment turns out to be an index segment, you can solve the problem very quickly. Because all the table data required for re-creating the index is still accessible, you can drop and re-create the index (the block will be reformatted when it's taken from the free-space list and reused for the index).

If the segment turns out to be a table segment, you have several options for solving the problem:

Using datafile backups and archived redo log files, you can do a recovery of the datafile that holds the corrupted data block. You must be sure, however, that the data block was not already corrupted in the backup datafile.

This option involves a lot of effort: shutting down the database, restoring an old enough datafile, and then recovering that datafile by applying multiple archived log files and restarting the database. It will, however, restore your corrupted table segment completely up to the time you discovered the corrupted data block.

If you regularly make dump files using the Export utility, another option could be to restore the table segment by using the Import utility. Of course, you can do this only if the version of the table as recorded in the dump file is acceptable (in other words, not too old) compared to other related tables in your Oracle database. Before doing the table import, you must drop the current table, which deallocates the bad block.

The last option involves using SQL to select as much data as possible from the current corrupted table segment and saving the selected rows into a new table.

You can easily select data that is stored in segment blocks that precede the corrupted block by using a full table scan (via a cursor fetching rows one by one and at the same time saving them to a new table). Rows that are stored in blocks after the corrupted block cause a problem, because a full table scan will never reach them. However, you can still fetch these rows by using rowids (single-row lookups).

TABLE DATA RECOVERY USING SQL

Let's investigate the third option more closely. Part of the trick is to identify the rowids of the rows that lie behind the corrupted block. The presence of an index on the corrupted table will help a lot in this area.

Table Was Indexed

Suppose the table was indexed---which is a very likely assumption, since every table should have a primary key and primary keys are implemented with unique indexes. The index contains pointers (rowids) to each row in your corrupted table, which you can use to directly access rows that are stored after the corrupted data block in the table segment. Using an optimizer hint, you can write a query that selects the rows from the table via an index scan (using rowids), instead of via a full table scan. Let's assume your table is named X, with columns a, b, and c. If table X is indexed uniquely on columns a and b by index X_I, the query will look like this:

        select /*+index(X X_I) */ a, b, c
        from X;

The execution plan for this query is:

        Select statement
                >table access (by rowid) X
                        >index range scan X_I

You must now exclude the corrupted block from being accessed, to avoid the internal exception ORA-00600[01578]. Because the block and file numbers are substrings of the rowid, doing this is easy:

        select /*+index(X X_I) */ a, b, c
        from X
        where rowid not like ||'.%.'||;

(A rowid holds the physical address of a row. Its value is formatted as follows: xxxxxxxx.yyyy.zzz. The first part, x, equals the block offset within the datafile. The second part, y, equals the row offset within the data block. The last part, z, identifies the datafile. All values are in hexadecimal representation.)

The execution plan for this query stays the same:

        Select statement
                >table access (by rowid) X
                        >index range scan X_I

But it is important to realize that the WHERE clause gets evaluated right after the index is accessed and before the table is accessed. Otherwise, you'll still get the ORA-00600[01578] exception.

Using the above query as a subquery in an insert statement, you can restore all rows of still valid blocks to a new table.

Since the index holds the actual column values of the indexed columns, you can also use the index to restore all indexed columns of rows that reside in the corrupted block. The following query retrieves only indexed columns a and b from rows inside the corrupted block:

        select /*+index(X X_I) */ a, b
        from X
        where rowid like ||'.%.'||;

The optimizer will not access the table segment for this query. It can retrieve the column values using the index segment only. The execution plan for the above query is:

        Select statement
                >index range scan X_I

Using this technique, you can restore all of the indexed-column values of the rows inside the corrupted block, without having to access the corrupted block at all. Suppose that column c of table X was also indexed by index X_I2. This enables you to completely restore rows inside the corrupted block.

First, restore columns a and b by using index X_I:

        create table X_a_b(rowkey,a,b) as
        select /*+index(X X_I) */ rowid, a, b
        from X
        where rowid like ||'.%.'||;

Then restore column c, using index X_I2:

        create table X_c(rowkey,c) as
        select /*+index(X X_I2) */ rowid, c
        from X
        where rowid like ||'.%.'||;

And finally, join the columns together, using the restored rowid:

        select x1.a, x1.b, x2.c
        from X_a_b x1, X_c x2
        where x1.rowkey=x2.rowkey;

In summary, you can use indexes on the corrupted table segment to restore all columns of all rows that are stored outside the corrupted data blocks. Of rows inside the corrupted data blocks, you can restore only the columns that were indexed. You might even be able to use an old version of the table (via Import) to further restore nonindexed columns of these records.

Table Has No Indexes

Tables with no indexes should occur only rarely, because every table should have a primary key and therefore a unique index. When no index is present, however, you should consider all rows of corrupted blocks as lost. You can retrieve all other rows by using rowids. Since there is no index, you must build a rowid generator yourself. The SYS.UET$ table shows you exactly which extents (file#, startblock, endblock) you need to inspect for possible rows of table X. If you make an estimate of the maximum number of rows per block for table X, you can build a PL/SQL loop that generates possible rowids of records inside table X. By handling the invalid rowid exception and skipping the corrupted data block, you can restore all of the rows except for those inside the corrupted block.

declare
v_rowid varchar2(18);
v_xrec X%rowtype;
e_invalid_rowid exception;
pragma exception_init(e_invalid_rowid,-1410);
begin for v_uetrec in (select file# file, block# start_block,
                            block#+length#-1 end_block
                        from sys.uet$
                        where segfile#=6 and segblock#=64)
                        --- Identifies our segment X.
        loop for v_blk in v_uetrec.start_block..v_uetrec.end_block
                                loop if v_uetrec.file<>6 and v_blk<>886 
                                --- 886 in file 6 is our corrupted block.
                then for v_row in 0..200 
                --- 200 is maximum number of rows per block for segment X.
                        loop begin select a,b,c into v_rec
                                from x
                                where rowid=chartorowid('0000'||hex(v_blk)||'.'||
                                        hex(v_row)||'.'||hex(v_uetrec.file);
                                insert into x_saved(a,b,c)values
                                        (v_rec.a,v_rec.b,v_rec.c);
                                commit;
                        exception when e_invalid_rowid then null;
                        end;
                end loop; /*row-loop*/
                end if;
                end loop; /*blk-loop*/
        end loop; /*uet-loop*/
end;
/

This code assumes that block IDs never exceed four hexadecimal places.

Function Hex

Function hex used in the code shown in this article accepts a decimal number and returns the hexadecimal representation of this number.

create or replace function hex(v_num in binary_integer)
        return varchar2 as
--- Assumption: 'v_num' is less than power(16,4).
v_tmp3 binary_integer; v_tmp2 binary_integer;
v_tmp1 binary_integer; v_tmp0 binary_integer;
v_buf binary_integer;
function hexchr(v_c in binary_integer) return varchar2 as
begin if v_c between 0 and 9 then return to_char(v_c);
        elsif v_c=10 then return 'A'; elsif v_c=11 then return 'B';
        elsif v_c=12 then return 'C'; elsif v_c=13 then return 'D';
        elsif v_c=14 then return 'E'; elsif v_c=15 then return 'F';
        end if;
        end;
function div(i in binary_integer, j in binary_integer)
        return binary_integer as
v_buf binary_integer:=i; a binary_integer:=0;
begin while v_buf>=j
        loop a:=a+1; v_buf:=v_buf-j; end loop;
        return a;
        end;
begin v_buf:=v_num; 
        v_tmp3:=div(v_buf,4096);  v_buf:=v_buf-4096*v_tmp3;
        v_tmp2:=div(v_buf,256);  v_buf:=v_buf-256*v_tmp2;
        v_tmp1:=div(v_buf,16);   v_buf:=v_buf-16*v_tmp1;
        v_tmp0:=v_buf;
        return hexchr(v_tmp3)||hexchr(v_tmp2)||hexchr(v_tmp1)
                ||hexchr(v_tmp0);
end;
/

CONCLUSION

Solving Oracle data-block corruption is hopefully not an everyday task for you. But this article shows you that the solution is very straightforward. In many situations, you can restore almost all data from corrupted data segments. The presence of an index on the corrupted table segment greatly aids in restoring the data, since it not only supplies all the rowids but also holds column values of rows that cannot be accessed inside the table segment.

Exit to : Oracle Hints and Tips