 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
|
|
 |
Oracle Alternative Block Checking MechanismsOracle Tips by Burleson |
Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.
Alternative Block Checking Mechanisms
There is another utility that resides completely within the database for
identifying corrupt data. This
command is the analyze table … validate structure command.
The
analyze command can do things that dbv cannot and vice versa.
The analyze
command can validate that tables and indexes are in sync with each other.
However, the analyze command only processes an object up to the point
of its high water mark (HWM), whereas processes all blocks in a file.
Block corruption can occur in blocks above the HWM.
The analyze
command would have to be executed against an open database for each object in
the database. Dbv can work
against offline files and is much faster since it is strictly at the file
level. In addition, the
analyze
table
command places an exclusive lock on the object being analyzed.
Alternatively, dbv
works outside of the database in “read only” mode against the datafiles
and does not lock anything. Any
errors encountered by the analyze table
command are reported in the session trace file in the user dump destination
directory.
Some errors reported by dbv are transient in nature. Therefore, the utility
should be executed on the suspect file again to confirm block corruption.
If problems are again reported in the same page locations, then the
file is indeed corrupt. Once one or more corrupted blocks are detected, the
DBA must resolve the issue.
Below are some options available to the DBA to address block corruption:
-
Drop and recreate the corrupted object – If the loss of data is not an issue, this is the preferred approach. For Data Warehouses, the data can be reloaded from external sources and the loss of data is minor. For Indexes, the index can be rebuilt into another data file. However, for OLTP tables (customer_orders), no data can be lost without a serious negative impact on the business.
-
If a few blocks are corrupt, determine which object(s) are causing the corruption. This can be done in the following query by mapping the physical file location to an object(s) contained in the file.
select tablespace_name, segment_type, owner,
segment_name
from dba_extents
where file_id = <corrupted file id>
and <Block #>
between block_id
-
Restore the file from a backup – The tried and true method for restoring good blocks back into the datafiles.
-
Use dbms_repair – Dealing with block corruption is always a risky proposition, so limit the use of dbms_repair to extreme situations. Dbms_repair is a package utility supplied by Oracle that identifies and repairs block corruption (described in next section).
If the first two options are unacceptable, using dbms_repair
can resolve some block corruption issues.
|
|
Copyright © 1996 - 2011 by
Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.