Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction :

Oracle provides different tools to detect, fix corrupted blocks.
Corruption can be caused by a software bug or a media failure or a human error.
In this note, we are going to simulate a block corruption in Oracle 11gR2 database, and we will go through the process of detecting the corruption, analyzing the corruption and finally fixing it.

Applies to :

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0.
Information in this document applies to any platform.

Environment test details :

Operating system: Windows XP service pack 2
Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 (32 bit)

Demonstration :

First let ‘s create a new tablepsace with a new datafile; Then a new table named TEST_CORRUPT belongs to this tablepspace TST_CORRUPT.

We backup the database :

Shutdown the database and simulate a corruption by modifying the datafile. I am going to modify the blocks presented in the middle of the datafile. This will help me then to open the database. If you modify the header of the datafile , you will may recieve an error opening the database.
(Be careful to do it in your test database because will be really dangerous to simulate it in a production database).

Startup the database, the database is opened without problems ;

Detect the block corruption, with the ANALYZE statement;

Now, let’s collect more details about the corrupted blocks; for this we are going to use the DBMS_REPAIR package to create an admin table named “REPAIR_TABLE”.

Assure that the table “REPAIR_TABLE” is create in Sys schema ;

Run the DBMS_REPAIR package with the check_object procedure to populate the “REPAIR_TABLE” table with information about the corrupted block;

We have now details about the file ID and the block ID, so let ‘s fix the block using RMAN ;

Conclusion :

The block corruption detection and fix is a straightforward process. You make sure of adjusting and following some steps and most of times the corruption is fixed using RMAN block recovery.

Nice reading,
Wissem