Oracle Consistency Check
SAP Notes
a. 540463 – FAQ: Consistency Checks + Block Corruptions
b. 23345 – Consistency check of ORACLE database
1. Analyze table validate structure cascade
Runtime: long – locks the table examined currently against change accesses
Only feasible in times of minimal work load or – even better – when the SAP system has been stopped
starting with Oracle 9 the extension ‘analyze table validate structure cascade ONLINE’; does not lock the table anymore checks tables and indexes if an analyze (online or not online) is running on a table and you try to alter storage parameters, parallel degree etc from this table then all further sessions accesing the table with write or read access are locked until the analyze is finished. Further details can be found in note 619188. reads the blocks to be checked in the SGA, causing possible temporary deterioration in the buffer quality of the DB block buffer does NOT check data stored in lob columns
2.Export
Runtime: medium – feasible in a running system. Performance loss as a result of the export processes. If you access extensive tables in write mode during export, there is a danger of ORA-1555 “snapshot too old”. However, you can prevent this by sufficiently dimensioned rollback segments only checks tables certain kinds of corruptions are exported without an error occuring. If you later try to reorganize this type of table (export/import), you will have problems during import. reads the blocks to be checked into the SGA which is why the buffer quality of the DB block buffer is adversely affected for a short while.
3. dbverify
Runtime: short – feasible in running operation (UNIX: all oracle releases; Windows Oracle9 or newer)
not only checks tables and indexes but also blank DB blocks does not check cross references between tables and indexes . Reads the blocks WITHOUT loading them in the SGA, which is why the buffer quality of the DB block buffer is not affected the only procedure that is also possible on data files restored from a backup (without these files having to belong to a DB) does together with blockchecksums according note 923919 an extended check on blocks and can then find special kinds of cross reference corruptions because those may cause wrong checksums. Wrong cross references caused by lost writes do NOT lead to wrong checksums and therefore cannot be detected.
Also cross reference failures already occuring in main memory BEFORE the checksum for the table and index blocks are calculated cannot be detected because the checksums base already on bad data.
Anyway dbverify together with blockchecksums are a considerable alternative if an analyze table validate structure cascade on the complete DB lasts to long.
