| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: consistency check
Introduction
This short article describes several features of Oracle which may be
used on new databases to help detect problems as close to when they
occur as possible. Some of these features are publicly documented
and some are not. All should be used with extreme care.
Oracle Support recommends the use of these features for the creation
of new databases. However, care should be exercised when introducing
any of the features on existing databases as they may show up
existing
problems.
Block Checksums
In various Oracle releases there is a special provision to add
checksums to Oracle database blocks. This helps detect media
corruptions when a block is next read by Oracle.
What does it do ?
Block checksumming forces Oracle to compute a checksum and place
this in the header of each database block before it is written
out to disk. When the block is subsequently read the checksum is
recomputed and the stored value is checked with this computed value.
Any difference is treated as a media error and an ORA-1578 error
is signaled. The checksum itself is added to the block just before
the block is written out.
Benefits
Early detection of media problems.
Disadvantages
A small performance overhead is incurred. This should be checked
as acceptable before introducing this option on a live database.
This overhead is unlikely to be significant but its impact depends
heavily on the application and data access so is difficult to
predict.
Enabling block Checksumming
Add the relevant parameter below to your INIT.ORA file.
Oracle Versions Parameter Setting to Enable Checksums
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
9.0.1 - DB_BLOCK_CHECKSUM = TRUE
Default is TRUE -> Processes all tablespaces and
writes checksumming for redo log blocks as well.
If set to FALSE -> Processes only SYSTEM
tablespace
8.1.5 - 8.1.7 DB_BLOCK_CHECKSUM = TRUE
If set to TRUE -> Processes all tablespaces and
writes
checksumming for redo log blocks as well.
Default is FALSE -> Processes only SYSTEM
tablespace
7.2.0 - 8.0.6 DB_BLOCK_CHECKSUM = TRUE
If set to TRUE -> Processes all tablespaces
Default is FALSE -> Processes only SYSTEM
tablespace
7.0.12 - 7.1.6 _DB_BLOCK_COMPUTE_CHECKSUMS = TRUE
Note that with 8.1.5 or higher, DB_BLOCK_CHECKSUM also automatically
takes care of writing checksumming information into the redo log
blocks
written to the online redo logs. In versions prior to 8i, there was
a
separate parameter LOG_BLOCK_CHECKSUM that used to control this.
This
LOG_BLOCK_CHECKSUM parameter is now obsolete with 8i and above.
Also note that a checksum is only added to a datablock if the block
has been modified by Oracle so when this parameter is first set
most of the datablocks will NOT have any checksum on them.
It is often desirable to add checksums to existing blocks to ensure
a problem is detected as early as possible - in Oracle8 onwards this
can be achieved by using RMAN to copy the datafiles as described
below.
In all releases this could also be achieved by a mass update of the
main tables to ensure each datablock is modified by Oracle and thus
has a checksum added to it.
Using RMAN to add Checksums to a file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This is an example to demonstrate how RMAN can be used to add
checksums to a datafile. You can use this approach even if you
do not use RMAN for your backups:
# Ensure DB_BLOCK_CHECKSUM is set to TRUE on the target database
# Mount the target database after a clean shutdown and then:
rman nocatalog
RMAN> connect target
RMAN-06005: connected to target database: V806
RMAN> run {
allocate channel dev1 type disk;
copy datafile '/prod/original.dbf' to '/prod/backup.dbf';
}
# The copied file (/prod/backup.dbf here) will now contain
# checksums on each block.
# You can then either point the database at the copied file
# or copy the backup back to the original location
# Eg:
RMAN> sql "alter database rename file ''/prod/original.dbf'' to
''/prod/backup.dbf''";
Log Block Checksums
From Oracle 7.2 onwards you can enable logfile block checksumming.
Like database block checksums this causes a checksum to be computed
for each redo block written.
What does it do ?
Log block checksumming forces Oracle to compute a checksum and
place this in the header of each redo block when it is written
out to the current redo log. When the block is subsequently read
by the 'ARCH' archive process this checksum is checked and the
archive process will stop and report an error if it sees a
corrupt redo block.
Benefits
Redo record corruptions are found as soon as the log is archived.
Without this option certain types of media corruption in the redo
logs can go un-noticed until one tries to restore a backup and roll
forward through the corrupt block.
Disadvantages
A small performance overhead is incurred. This should be checked
as acceptable before introducing this option on a live database.
Any noticed corruption has the potential to suspend database
activities until remedial action is taken. This is in order to
protect
the integrity of the database.
As this parameter requires the database COMPATIBILITY parameter to
be
set to 7.2.0 or higher any redo generated will not be readable by
earlier releases of Oracle. This may prevent the database being
downgraded to an earlier Oracle release.
Enabling log block Checksumming
Add the relevant parameters below to your init.ora file.
Oracle Versions Parameter Setting to Enable Checksums
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8.1.5 onwards DB_BLOCK_CHECKSUM = TRUE enables log block
checksums in Oracle8i
8.0.3 - 8.0.6 LOG_BLOCK_CHECKSUM = TRUE
7.2.0 - 7.3.4 LOG_BLOCK_CHECKSUM = TRUE
and COMPATIBILITY=7.2.0 or higher
Logical Block Checking
This feature forces Oracle to perform a health check on the integrity
of data blocks when they are modified.
What does it do ?
Whenever Oracle modifies a data block a full health check is performed
on the block to check it is fully consistent. Any errors found cause
an internal error to be signaled (Eg: ORA-600 [6599]). There are
a range of internal errors that may be raised depending on the
type of block change being performed immediately prior to the
corruption being detected.
If a problem is detected in Oracle7 through 8.1.5 then Oracle
performs cache recovery of the block. If the error repeatedly
recurs during cache block recovery the block is marked as software
corrupt and the data in the block is lost to normal application
access. The object to which the block belongs will then
raise an ORA-1578 error whenever this block is requested.
(A "software corrupt" block is produced by setting certain
bytes in the block header to indicate that the block is to be
considered as corrupt).
As of Oracle 8.1.6 a new error (ORA-607) is signaled if block
level recovery fails. In Oracle 8.1.6 onwards a block is not
immediately soft corrupted. However, any such corrupt block will
subsequently cause the database writer process to crash which may
not be desirable. This scenario is documented in [BUG:1168406]
and is fixed in 8.1.6.2 and 8.1.7.0 onwards.
Benefits
Logical block checking allows potential corruptions to be spotted
as soon as they occur potentially preventing wide-spread corruption
from occurring before it causes a more severe error.
Disadvantages
A small performance overhead is incurred. This should be checked
as acceptable before introducing this option on a live database.
Prior to 8.1.6:
~~~~~~~~~~~~~~~
As the blocks are marked software corrupt remedial action is
required
immediately to allow normal application access to affected data.
This
will normally require a rebuild of the affected object.
Data in the affected block is effectively lost.
In 8.1.6
~~~~~~~~
There is a problem in 8.1.6.0/8.1.6.1 in that block checking does
not
mark blocks as soft corrupt. This in turn can cause database
writer to crash and hence the instance to abort.
This problem is documented in [BUG:1168406] and is fixed in 8.1.6.2
onwards (including 8.1.7.0 onwards).
WARNING
This feature should never be enabled on a live system without
first checking the logical consistency of the database outside of
normal operational time.
This can be achieved using the DBVERIFY utility available with
Oracle 7.3 onwards, or on older releases can be checked by
restoring a copy of the database and performing a full export
AND performing index range scans on all major indexes to prove
they are intact.
This is important to give some confidence in the original data
blocks.
You should also give consideration to the potential downtime incurred
if a corruption should be detected.
Enabling Logical Block Checking
Add the relevant parameters below to your init.ora file.
These events apply to all Oracle releases. However, from Oracle
8.1 onwards it is preferable to use the published init.ora
parameters to enable logical block checking.
Oracle Versions Parameter Setting to Enable Block Checking
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8.1.6 - 8.1.7 DB_BLOCK_CHECKING = TRUE
If set to True -> enables block checking on
ALL tablespaces.
Default is FALSE -> sets block checking on
the
SYSTEM tablespace only.
NB: See [NOTE:96117.1] if upgrading to 8.1.6
or higher.
8.1.5 DB_BLOCK_CHECKING = TRUE
If set to True -> enables block checking on
ALL tablespaces.
Default is FALSE -> sets block checking on
the
SYSTEM tablespace only
8.0.3 - 8.0.6 Use the events described below
7.0 - 7.3.4 Use the events described below
Logically Check Parameter Setting to Enable Logical block checking
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Data blocks EVENT="10210 trace name context forever, level 10"
Index blocks EVENT="10211 trace name context forever, level 10"
Cluster blocks EVENT="10212 trace name context forever, level 10"
Important: Read [NOTE:75713.1] before setting any events on a database.
WARNING: On some releases Event:10211 should NOT be set due to
[BUG:792610] which can cause UNDO corruption if an index
corruption is detected.
DBVERIFY
that use file level locking prevents this.
DBVERIFY checks data and index blocks and is supplied as standard
from Oracle 7.3 onwards. As of 8.1.6 addition block types are also
checked.
See [NOTE:35512.1] for more information about DBVERIFY.
ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE
This SQL command allows Oracle to perform a health check on the
named table and any related indexes.
What does it do ?
The 'ANALYZE ... VALIDATE ..' command performs the same block checks
as the logical block checking above but does NOT mark blocks as
corrupt. It also checks that table and index entries match.
Any problems found are reported into the user session trace file
in USER_DUMP_DEST.
Benefits
This allows important tables to be regularly checked for corruptions
during a convenient timeslot.
Disadvantages
The command takes system resource and time to run.
Using ANALYZE
This is a standard SQL command so can be issued against any table
whenever required. It is advisable to have a script pre-prepared to
check all the major application tables using this feature.
DBMS_REPAIR
DBMS_REPAIR is a package introduced in Oracle 8.1.5.
The DBMS_REPAIR.CHECK_OBJECT procedure can be used to perform
the same type of checking that ANALYZE TABLE ... VALIDATE STRUCTURE
performs.
At present DBMS_REPAIR has a number of limitations and should be
used with extreme care. It is not documented in this note.
"Bud Socks" <bud_socks_at_gmx.net> wrote in message news:a4gonq$g9p$00$1_at_news.t-online.com...
> Hi there, > > we have a 7x24h productive db environment. what would be the most > recommended > method to check block corruption ? > > Thanks > > Bud > > > >Received on Thu Feb 14 2002 - 10:31:03 CST
 |
 |