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

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.