How to recover or delete corrupted blocks

For corrupted blocks, we can use block media recovery to recover corrupt datablocks with remain the datafile online. If we don’t have backup available, we can recreate the table by skip the corrupted blocks.

Here are steps for block media recover.

Step1. Find out the bad blocks file# and block#
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
will show all recorded corrupt blocks.
also you may get it in alert log or other error outputs.
ORA-01578: ORACLE data block corrupted (file # 7, block # 3)
ORA-01578: ORACLE data block corrupted (file # 7, block # 4)
ORA-01578: ORACLE data block corrupted (file # 9, block # 235)

Step2. Recover by rman
rman> blockrecover corruption list;
or
rman> blockrecover datafile 7 block 3,4 datafile 9 block 235;

Here are steps for no backup available scenario:

To skip these blocks and recreate it as the last resort.

Method 1:
Step1.set the 10231 trace for system
SQL> ALTER SYSTEM SET EVENTS=’10231 trace name context forever,level 10′ ;

step2.using CTAS to create new table. It will skip corrupt data and do not report error .
SQL> CREATE TABLE new_table AS SELECT * FROM bad_table;

step3.check the data in new_table.
If acceptable, drop the bad one and rename it.
SQL> DROP TABLE bad_table;
SQL> RENAME good_table TO bad_table;

step4.turn off the trace
ALTER SYSTEM SET EVENTS ‘10231 trace name context off’;

Method 2: To do it manually.
Step1.Find out the corrupted segment
SQL>  SELECT segment_name,segment_type,extent_id,block_id, blocks
FROM dba_extents t
WHERE file_id = :file_id> AND :block_id between block_id and (block_id + blocks – 1) ;

Step2.If the corrupted segmetn is index, just drop it and recreate it.
If it is a table, go to step3

Step3.create new table using CTAS but skip the bad blocks.
SQL> create table good_table  as
select from bad_table where rowid not in
(select rowid from bad_table where to_number(substr(rowid,10,6)) = :block_id );

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

2 Responses to How to recover or delete corrupted blocks

  1. neworacledba says:

    you post great on unique topics…keep up the good job

  2. sorin says:

    No bloatware like on other sites! Really nice.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: