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 184.108.40.206.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 220.127.116.11.0 (32 bit)
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 ;
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.