Search This Blog

Tuesday, April 29, 2008

Oracle Database Block corruption


My Signature Article





Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips





Oracle Database Block corruption

“Block corruption is rare but it does happen. As databases get larger and larger – the probability of it happening at some point nears 100%.”--Mr. Tom Kyte Oracle Expert

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed


ORA-01578:
ORACLE data block corrupted (file # string, block # string)
Whenever we encounter above error message mean we have BLOCK CORRUPTION.

NOTE: We can find detail information about block corruption in alert.log file

Two types of block corruption can happens

- Physical corruption (media corrupt)
- Logical corruption (soft corrupt)

Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;

Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.

Difference between logical and physical corruption

Logical corruption is header - footer - that is one of the checks, yes (it is looking for fractured blocks and when it hits one, it'll re-read it, that is why there is no need for "alter tablespace begin backup" with rman)

Physical corruption is "we cannot read the block from disk, something is physically preventing us from doing so”.

How to detect block corruption?
1. DBVERIFY utility


DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored.

http://dbataj.blogspot.com/2007/04/offline-database-verification-utility.html

2. Block checking parameters

There are two initialization parameters for dealing with block corruption:- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)causes 1-2% performance overhead- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)causes 1-10% performance overhead

Note: In10g db_block_checksum value TYPICAL is implying TRUE and db_block_checking value FULL implying TRUE.

DB_BLOCK_CHECKING Initialization Parameter
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3176

3. ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement

Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

ANALYZE: Reporting Corruption
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3173

4. RMAN BACKUP command with THE VALIDATE option

You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

Detection of Logical Block Corruption

Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used, the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.
For BACKUP commands the MAXCORRUPT parameter sets the total number of physical and logical corruptions permitted in a file. If the sum of physical and logical corruptions for a file is less than its MAXCORRUPT setting, the RMAN command completes successfully. If MAXCORRUPT is exceeded, the command terminates and RMAN does not read the rest of the file. V$DATABASE_BLOCK_CORRUPTION is populated with corrupt block ranges if the command succeeds. Otherwise, you must set MAXCORRUPT higher and re-run the backup to find out the corrupt block ranges.
RMAN found any block corruption in database then following Data Dictionary view populated.

V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION

Using RMAN to Validate Database Files
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup005.htm#i1006673

5. EXPORT/IMPORT command line utility

Full database EXPORT/IMPORT show=y is another method.

. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 4, block # 43)
ORA-01110: data file 4: 'C:\ORA10GHOME\ORADATA\ORCL10G\USERS01.DBF'

6. DBMS_REPAIR package

dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.

http://www.oracleutilities.com/Packages/dbms_repair.html

How to Repair & Fix block corruption?

We can recover everything but we have valid database backup.
Whenever we found block corruption then first need to find out which type of block corruption occurred because block corruption recovery depends on block corruption type.

Like Corrupted block related to TABLE segment, INDEX segment, TABLE
PARTITION segment, INDEX PARTITION segment, ROLLBACK segment, LOB segment.

Through below query we can find out corrupted block type

select segment_type,owner'.'segment_name
from dba_extents
where file_id = [&file_id] and [&block] between block_id and block_id+blocks -1;


Below is example with RMAN BLOCK MEDIA RECOVERY.

SQL> conn scott/tiger
Connected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 11)
ORA-01110: data file 5: 'C:\INDEXDATA01.DBF'

First check which type of block corruption happened through above mentioned query.

RMAN> blockrecover datafile 5 block 11;
Starting recover at 29-APR-08using channel
ORA_DISK_1 starting media recoverymedia recovery complete,
elapsed time: 00:00:00
Finished recover at 29-APR-08

If you are not using rman then applying below procedure
- if it is index then drop and recreate index

- if it is table and you have backup of that table then restore backup on another database and exp/imp the table.

Same Reference:
http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

NOTE: Find more information “Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g” Metalink Note: 28814.1

How to corrupt database block for practice purpose?
On Unix:

Use dd command
$man dd

On Windows:
Use Editor and open datafile write some junk character at middle of file and save it.

14 comments:

Leo James said...

Taj,

I visited you blog searching for some syntax. You have some valueble information in it. Keep the good work.

- Leo

Moid Muhammad said...

I like reading your posts. Great info. Can we recover undo segments? Or do we even need to as undo data is constantly over written?

--MM

Anonymous said...

I know easier ways to migrate Oracle database and fix possible errors in the same time. you may try the sql 2000 database suspect recovery tool program. I periodically use this application in our company to fix database errors

Unknown said...

How do I corrupt a single block using dd command?

Thanks

Anonymous said...

Your website is pretty slow to load in Firefox.

guaranteed payday loans

Mark Willium said...

Hi Taj,

I have regularly visit your blog for new update. I am late to comment but you can try stellar oracle recovery tool for review. This tool repairs corrupt oracle database.

Thanks,

Arka said...

good block

Anonymous said...

Yes the UNDO data has to be backed up and recovered - it is needed after the recovery after the database opens in order to roll back uncommitted transactions

Anonymous said...

Great post.

Here is my web page: bok of ra (launehosting.de)

Anonymous said...

hey there and thank you for your info – I've definitely picked up something new from right here. I did however expertise several technical points using this website, since I experienced to reload the web site lots of times previous to I could get it to load properly. I had been wondering if your web host is OK? Not that I'm complaining, but slow loading instances times will
very frequently affect your placement in google and can damage your quality score if ads and marketing with Adwords.
Well I am adding this RSS to my e-mail and can look out for much more of
your respective intriguing content. Make sure you update this again soon.


Also visit my web-site - Psn Code Generator

Anonymous said...

You'll be happy to know that there are plenty of wood knife block containers out there that can help.

My web page < BACK ()

Anonymous said...

best dentists in plano texass in Los Angeles is by
listening to their feedback. When considering the benefits of a beautiful smile.
We invite you to our office, his teeth were fractured, worn, discolored,
broken or have too many fillings.

my weblog ... Children dentist in plano

Anonymous said...

Your post sucks. Mines better! ..lol. Just kidding.
Just getting into this blogging thing myself, though I don't think anyone actually reads my posts. Does it come easier with time?

Visit my blog post :: Fort Worth Mom [Http://noonedied.com/tag/arguing/]

Anonymous said...

The homicide remains under investigation. S hospitals have
performed dexter missouri chevrolet the hand surgeries.
I am being released April 5th and was looking forward to cataloging anything in Work Flows.
He no longer is a deputy sheriff. 31 years later he finally met his father,
Warren Powell. The child dexter missouri chevrolet was found when an unidentified resident
called the police, alleging that Bishop was dancing naked in her driveway.


My blog post - Zach