Recovering from ORA-1578 ORA-8103 (Logical Corruption)

1. For ORA-1578:

There are two ways in which we can extract the data from a corrupted table:

a) Using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
b) Using Event 10231

a) Connect as sysdba user:

execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘<schema_name>’,'<table_name>’);

Put the schema name and the table name of the corrupted table.

Then issue the following command to craete a new table from corrupted table:

create table new_table as select * from corrupted_table_name;

Now Drop the corrupted table, rename the newly created table to its original name and
clear the attribute for the new table as:

execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

(‘<schema_name>’,'<table_name>’,flags=>dbms_repair.noskip_flag);

b) Set the event 10231 at session level to skip corrupted rows:

ALTER SESSION SET EVENTS ‘10231 TRACE NAME CONTEXT FOREVER, LEVEL 10’;

Then issue the following command to craete a new table from corrupted table:

create table new_table as select * from corrupted_table_name;

Now Drop the corrupted table, rename the newly created table to its original name.

2. For ORA-8103

This is a case of LOGICAL CORRUPTION and cannot be taken care by skipping the corrupted rows.

Follow Metalink Note 422547.1 to salvage data in case of ORA-8103