What are Possible ways to detect 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.
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.
Ø 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)
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.
$ dbv FILE=/u01/test/system01.dbf FEEDBACK=100
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.
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?
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.
How 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-15 using channel
ORA_DISK_1
starting media recoverymedia recovery complete,
elapsed
time: 00:00:00
Finished
recover at 29-APR-15
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:
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.
No comments:
Post a Comment