Basics: Oracle Corruptions Series #3: How to find physical corruptions and limitations with each approach.

Typical Oracle physical corruptions may pose are,

(a) ORA-01578 - This error explains physical structural damage with a particular block.

(b) ORA-08103 - This error is a logical corruption error for a particular data block.

(c) ORA-00600 [2662] - This error is related to block corruption , and occurs due to a higher SCN than of database SCN.

1) How to check Oracle Physical block corruption?

a) Using DBV (dbverify)

DBV utility will scan through the files at OS level. While reading the blocks it checks for any fractured blocks , checksum failure & other checking failures. It will report corrupted blocks and also classifies them in what type of object were they (table/index/etc).

The following script shall be used to run DBV on all datafiles in UNIX platforms.

LIMITATIONS WITH DBV:

• DBV cannot understand any table / index rowcount mismatch.
• DBV cannot understand any complex corruptions, especially corruptions below cache layer of a datablock.
• Some blocks that may not be part of Oracle, they would have been dropped. But DBV will still report that block as corrupted. When you check with the query against dba_extents (given below) there wont be any rows returned. And this corrupt block will not affect normal database operations as Oracle is not going to read this block. But while performing RMAN backups we still report this block as corrupted.
• Logical corruptions with undo / redo records cannot be identified.
• Some types of corruptions that cannot be explored while we do an OS level read.

b) use RMAN to validate the database files.

 

RMAN> BACKUP VALIDATE DATABASE;

- If you want to validate a particular datafile you shall use the following command, this will vaildate the file# 1

RMAN> BACKUP VALIDATE DATAFILE 1;

LIMITATIONS WITH RMAN:

• All the limitations that apply to DBV are applicable to RMAN VALIDATE also.

 

(c) Export Utility / SELECT:

To check the corruption, we shall export the affected segment / schema / tablespace / full.

If we doubt a particular table, we shall either perform a full table scan (or) CTAS.

If there is any corruptions with the exporting object, export will fail with corruption error.

In case there no space in filesystem to perform an export, we shall do it to /dev/null.

LIMITATIONS WITH EXPORT:

• It fails immediately after encountering the first corruption., so complete picture will be missed-out.
• export performs full table scan, so we will not be checking the indexes. Any corruption in index (or) table/index rowcount/rowid mismatch will not be checked by export.
• A full database export reads data dictionary, but it doesn't mean that there is no corruption with data dictionary when this export completes successfully.

(d) Use ANALYZE command:

ANALYZE has been referred as the best utility that performs maximum checks.

This is the easy utility to check the corruptions associated with index on any means.

To check the corruption with a table do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE;

To check the corruption with an index do the following:
ANALYZE INDEX <OWNER.INDEX_NAME> VALIDATE STRUCTURE;

To check the corruption with the table and its index(s) to perform the cross reference checkings do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE;

For partition tables, we need to use ANALYZE command with INTO INVALID_ROWS option, similar to the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;

 

LIMITATIONS WITH ANALYZE:

• It is not a limitation, rather the common problem. It would be difficult to perform an analyze with huge segments as it needs to scan all the blocks with that segment. We shall use ONLINE option there. But still the performance problem may not be acceptable for busy segments.
• Again this fails when it encounters the first corruption, it will not proceed after that.

2) Parameters that helps to check the block corruptions

a) DB_BLOCK_CHECKING=TRUE
DB_BLOCK_CHECKING controls whether Oracle performs block checking for data blocks. When this parameter is set to true, Oracle performs block checking for all data blocks. When it is set to false, Oracle does not perform block checking for blocks in the user tablespaces. However, block checking for the SYSTEM tablespace is always turned on.
Oracle checks a block by going through the data on the block, making sure it is self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to true if the performance overhead is acceptable.
(b) DB_BLOCK_CHECKSUM=TRUE
DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read only if this parameter is true and the last write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log.
If this parameter is set to false, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces.
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. Turning on this feature typically causes only an additional 1% to 2% overhead. Therefore, Oracle Corporation recommends that you set DB_BLOCK_CHECKSUM to true.

( c) _DB_BLOCK_CACHE_PROTECT=TRUE
This parameter is not available in all platforms / versions. In those platforms setting it to TRUE may not do anything.

3) Quick checks or things where you can find the corruptions with above methods:-

Normally with the corrupt block messages will give the relative file number. (rfile#).
Relative file number is unique to a particular tablespace, not to the database.
To find the segment associated with this corrupt block we need to pass the absolute file number (file#).

SELECT NAME, FILE#, TS# FROM V$DATAFILE WHERE RFILE#=&FILE_ID;

In the following query pass the absolute file number and block_id to get the details of the object. that you get in alert log or any of the methods above.

SELECT TABLESPACE_NAME, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE
FROM DBA_EXTENTS
WHERE FILE_ID=&FILE_ID AND
& BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

4) Identify the objects or blocks associated with corruptions along with type of that block.

Identify the corrupt segments

The next query can be run to map each block to a segment in the database. It will map each block from v$database_block_corruption to either a segment or if the block is free.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

Hope this helps

-Thanks

Geek DBA