Guenadi N Jilevski's Oracle BLOG

Recovering from ORA-1578 and ORA-8103

Recovering from ORA-1578 and ORA-8103

1.  ORA-1578 related steps:
There are two ways in which we can extract the data from a corrupted table:
1.1 Using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
1.2 Using Event 10231

1.1 Using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to treat ORA-1578
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);

1.2 Using Event 10231 to treat ORA-1578
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.  ORA-8103 related steps
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

May 5, 2009 - Posted by gjilevski | oracle