BNFD - Your DBA NoteBook On The Net!!!

Sunday, January 27, 2008

How to check a physical corruption?

There are various methods to check / understand the physical corruption:
(a) To check the corruption we shall run DBV on the datafiles, which will explain the corruption.
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).
LIMITATIONS WITH DBV:
o DBV cannot understand any table / index rowcount mismatch.
o DBV cannot understand any complex corruptions, especially corruptions below cache layer of a datablock.
o 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.
o Logical corruptions with undo / redo records cannot be identified.
o Some types of corruptions that cannot be explored while we do an OS level read.

(b) RMAN to validate the database files.
The following command will validate all the datafiles in the database.

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:
o 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:
o It fails immediately after encountering the first corruption., so complete picture will be missed-out.
o 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.
o 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) 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 VALIDATE STRUCTURE;

To check the corruption with an index do the following:
ANALYZE INDEX VALIDATE STRUCTURE;

To check the corruption with the table and its index(s) to perform the cross reference checkings do the following:
ANALYZE TABLE VALIDATE STRUCTURE CASCADE;

For partition tables, we need to use ANALYZE command with INTO INVALID_ROWS option, similar to the following:
ANALYZE TABLE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
ANALYZE PARTITON TABLE VALIDATE STRUCTURE CASCADE

LIMITATIONS WITH ANALYZE:
o 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.
o Again this fails when it encounters the first corruption, it will not proceed after that.

No comments: