Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
“How To Connect To Oracle With Toad“,
“How To Connect To Oracle With sqlplus“,
1 | select * from v$database_block_corruption; |
If we run the query, the screen will appear as below.
For RAC Databases;
1 | 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..
1 2 | 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.
1 2 | 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; |
or
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | 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#; |
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.
1 | RMAN> blockrecover corruption list; |
Repair a particular block
1 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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.
1 | dbv file=+DATA/orcl/datafile/users.411.795847253 blocksize=8192 userid=sys/***** |
