How To Detect and Repair Block Corruption in Oracle

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“,

If we run the query, the screen will appear as below.

For RAC Databases;

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..

Find Which Object is Corrupted

You can use the following query to determine which objects are on the corrupted blocks.

or

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.

Repair a particular block

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.

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *