Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

September 17, 2010

Dealing with block corruption

Filed under: 10 G,10G,11 G,11G,RMAN — orawiss @ 1:18 pm
Tags: ,

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: