In this article, we will see how to fix block corruption when there is a block corruption on a data file.
If Block Corruption is on the index, you can solve the problem by dropping and recreating the index.
In some cases, block corruption can also be on free pages.
Detect Corrupt Blocks
Block corruption in Oracle databases is not a fearful dream. Block corruption can be easily corrected in databases that are in Archivelog mode and that are regularly backed up by rman.
Connect to the database which has block corruption and detect the block corruption with the following script.
if you dont know to connect to Oracle, you may want to read the below articles.
select * from v$database_block_corruption;
If we run the query, the screen will appear as below.
For RAC Databases;
select * from gv$database_block_corruption;
Repair Block Corruption Using RMAN
Then connect to RMAN and repair block corruption with the following script.
To connect to RMAN you may want to read the article titled “How To Connect To RMAN“.
Write the file number returned from the above script insted of “x” in the below script.
Write the block number returned from the above script instead of “y” in the below script..
blockrecover datafile x block y;
backup validate check logical datafile x;
Find Which Object is Corrupted
You can use the following query to determine which objects are on the corrupted blocks.
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id =x and y
between block_id AND block_id + blocks - 1;
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#
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
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#;
When you run the query, it will tell you where the block corruption is, as shown below.
Repair All Block Corruptions
To repair all the corruptions in this list, simply run the following command with RMAN.
RMAN> blockrecover corruption list;
Repair a particular block
RMAN> blockrecover datafile 151 block 3454464;
You can find the datafile and block number from
v$database_block_corruptionas I mentioned above.
Finally you need to check the corresponding datafiles. With the following command, you can scan the entire datafile and see if there are problems.
RMAN> backup validate check logical datafile 151;
Starting backup at 31-MAR-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00151 name=+DATA/orcl/datafile/data_ts.1491.871430401
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
List of Datafiles
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
151 OK 0 153363 3932160 629800241304
File Name: +DATA/orcl/datafile/data_ts.1491.871430401
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 3718487
Index 0 3829
Other 0 56481
Finished backup at 31-MAR-16
If the Status OK and Blocks Failing values are zero as in the above output, we can say that the problem is fixed.
If you want to check the datafile offline, you can also use the DB verify tool. The sample command is as follows.
dbv file=+DATA/orcl/datafile/users.411.795847253 blocksize=8192 userid=sys/*****