AskDba.org Weblog

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 … Read entire article »

ORA-1578 is indication of physical block corruption. Oracle computes an internal checksum before fetching the block from disk, when it is about to fetch the block it reads the block header and compare the checksum stored in it. If the checksum mismatches we get ora-1578 error.It indicates that a block is corrupted at hardware level i.e at OS. This error will always come whenever the corrupted block is read by Oracle. Reasons for ora-1578: 1. Bad sectors in the Storage Disk Drive.2. Formatting of blocks At OS level.( Zeroed out disk blocks).3. some corruption in underlying hardware which is making block unreadable. How to collect information for ora-1578: Error Text: ORA-01578: ORACLE data block corrupted (file # 14, block # 38976) The block # 38976 shows the corrupted block number and the file # 14 indicatesthe reletive … Read entire article »