How to fix block corruption in Oracle Database

How to fix block corruption in Oracle Database

Please do the following:

1) Set your $ORACLE_SID and $ORACLE_HOME appropriately if not already set.

2) Start RMAN in nocatalog mode and connect to your database:

From the operating system prompt issue:

$ rman target / nocatalog

or

$ rman target sys/<sys_password> nocatalog

3) From the RMAN> prompt issue the validate command with the "check logical"
clause:

The following example shows how to validate all datafiles:

run {
allocate channel d1 type disk;
backup check logical validate database; ( in some cases if only is needed a datafile ... backup check logical validate datafile <number>;
release channel d1;
}


4)identify and recover all the Corrupted Objects in the Database reported by RMAN

From the operating system prompt issue:

$ rman target / nocatalog
or
$ rman target sys/<sys_password> nocatalog

run {
allocate channel d1 type disk;
blockrecover corruption list;
release channel d1;
}


or
RMAN> run {
allocate channel d1 type disk;
restore datafile 8,9; -- example
recover datafile 8,9; -- example
}


5) Please run RMAN validate again to confirm that the database is now corruption free.

$ rman target /
RMAN> backup validate check logical database;

2) once RMAN validate has completed, run the following in SQL*Plus as SYSDBA
and show us the result

SQL> select * from v$database_block_corruption;

3) If v$database_block_corruption returns >= 1 rows,

Use below SQL query to Find All the Corrupted Objects in Your Database.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;