Steps to HandlingBlock Corruption in Large Partitioned tables using EXCHANGE PARTITION method when no Backups available Metalink Note ID 1324772.1]

Goal of this DOC-------

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 truncate partition ) 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

Here are the steps:

0) Determine the corrupted table partition when we have identified file# and block# for the corrupted blocks (e.g. by reviewing alert.log for ORA-01578):

SQL> select owner, segment_name, partition_name, segment_type, tablespace_name, relative_fno, file_id
FROM dba_extents
WHERE file_id = &corrupted_file#
AND &corrupted_block# BETWEEN block_id AND block_id + blocks - 1 ;

There are other ways to identify the corrupted blocks in the database :


Note 352907.1 Script To Run DBV On All Datafiles Of the Database

Note 472231.1 How to identify all the Corrupted Objects in the Database reported by RMAN


1) Set event 10231 to skip the corrupted blocks:

SQL> alter session set events '10231 trace name context forever, level 10';


2) Create salvage table with good blocks data from table partition:

SQL> create table scott.salvage_emp_p5 tablespace users parallel (degree 4) as select * from scott.emp partition (emp_p5) ;

If populating above table also fails with block corruption messages (e.g. ORA-1578 on a BITMAP BLOCK), we can use other methods, e.g. the one in below Article:

Note 422547.1 Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS


3) (Optional) Verify, the count is appropriate in salvage table in comparsion to orignal table with the fact of number of corrupted blocks:

SQL> select count(*) from scott.salvage_emp_p5 ;

SQL> select count(*) from scott.emp partition (emp_p5) ;


4) Exchange the corrupted partition with salvage table created. By using WITHOUT VALIDATION clause, the exchange will be faster as it will NOT validate that each row from salvage table maps correctly to the table partition (We don't worry about it because the salvage table is created from same partition):

SQL> alter table scott.emp exchange partition emp_p5 with table scott.salvage_emp_p5 without validation ;


5) Unset the event 10231 to make sure we don't ingore any unnoticed corrupted blocks while selecting from exchanged partition:

SQL> alter session set events '10231 trace name context off';


6) (Optional) Verify, if the data in corrupted partition is now accessible and valid. One of the verifications is to count the rows:

SQL> select count(*) from scott.emp partition (emp_p5) ;


7) Drop the temporary salvage table as it now contains the corrupted blocks exchanged with corrupted table partition :

SQL> drop table scott.salvage_emp_p5 purge ;


8) Identify the UNUSABLE non-partitioned indexes and index partitions:

--- for non-partitioned indexes

select a.owner index_owner, a.index_name
from dba_indexes a
where a.table_owner='SCOTT'
and a.table_name='EMP'
and a.partitioned='NO'
and a.status='UNUSABLE'
order by a.owner, a.index_name ;

--- for index partitions

select a.owner index_owner, a.index_name, b.partition_name, b.partition_position
from dba_indexes a, dba_ind_partitions b
where a.owner=b.index_owner
and a.index_name=b.index_name
and a.table_owner='SCOTT'
and a.table_name='EMP'
and b.status='UNUSABLE'
order by a.owner, a.index_name, b.partition_position ;


9) Rebuild the UNUSABLE non-partitioned indexes and index partitions identified in step 8):

SQL> alter index scott.emp_dept rebuild online ;

SQL> alter index scott.emp_pk rebuild partition emp_p5 ;