Fix data block corruption in 7.3.4

The database is in 7.3.4 running on HP box with a size of 9GB. This is a very old database and the box too.

After expected power failure, the Instance came up. However the application cannot access as data block was corrupted for certain data blocks.


SVRMGR> analyze table oem.invoice_file validate structure cascade;
analyze table oem.invoice_file validate structure cascade
*
ORA-01578: ORACLE data block corrupted (file # 3, block # 863317)
ORA-01110: data file 3: '/u03/oradata/js/data1a.dbf'

We are not able to do export/query or any activity with this table.

The following was resolved the issue from the metalink.

"The event 10231 allows Oracle to skip certain types of corrupted blocks on full table scans ONLY hence allowing export or "create table as select" type operations to retrieve rows from the table which are not in the corrupt block. Data in the corrupt block is lost.Set this event at session level:"

SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';

Now the select * from query works and created new table using "create table as select" and drop original table.

This was fixed the data block corruption.

Reference:
28814.1 "Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g".