Steps for Handling (Salvage) Block Corruption in Large Partitioned tables using EXCHANGE PARTITION method when no Backups available (Doc ID 1324772.1)

Last updated on APRIL 22, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 22-Apr-2013***
.
There are corrupted blocks (e.g. ORA-1578) in partitioned table and need to get rid of these corrupted blocks instead of using DBMS_REPAIR method (in case it can be used) as it does not remove the corrupted block.


Goal

When a corruption is identified in a huge table partition block (e.g. ORA-01578), and we don't have backups (e.g. RMAN, OS level, Export, or any external resource) to recover the corruption, we can still try to salvage the remaining data in the table using 10231 event (with some possible data loss and inconsistency by skipping corrupted data blocks).

One method to achieve this is:

a) create a salvage table (using 10231 event) and insert data from corrupted partition (using CTAS or INSERT INTO SELECT...) :

b) truncate the partition (using alter table <original_table> truncate partition <partition_name>) or DELETE the rows in partition

c) Insert the data from salvage table to truncated corrupted partition (using INSERT INTO SELECT...)


In above method, step c) will take significant amount of time depending on the number of rows in the partition i.e. if the table partition contains large number of rows.

We can significantly reduce the time taken in step c) above by using EXCHANGE PARTITION method which can be used with partitioned tables.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms