It seems I probably did misread the doc's. I had interpreted that particular passage to mean that RMAN (with CHECK LOGICAL) was in fact cross-checking Index blocks with Table blocks. In fact, I now have to wonder what the heck it's doing if I *don't* specify that option.
Still, it's hard to argue with actual testing...
By the way, logical corruption can (and will) occur in cases of "hardware" failures that cause cached writes not to take place. (For example, a firmware failure in your disk array that causes the contents of the "non-volatile" cache to be erased. I've seen that happen much more recently than I've seen an Oracle7 database...)
Some logical corruptions cannot be detected by any means -- exept possibly by a (very prompt) full recovery from backup into a "spare" database, and then a complete byte-by-byte comparison of the two database images. When writes are "lost" (e.g. through the failure of NVRAM caches), you are likely to find that every block in your database has a completely valid checksum. Some blocks, however, will be "older" than they ought to be. If you have the extraordinary bad luck of having such outdated blocks occur (for example) in a table with no indexes (you'd want to talk to your data modeler about that anyway) then you would have no (useful) way of detecting this corruption.
Just a thought...
> CHECK LOGICAL > ================== > Tests data and index blocks that pass physical corruption checks for > logical corruption, for example, corruption of a row piece or index > entry.
This part is interesting. In fact it is explicitly written that RMAN detects corruptions in a row piece OR index entry. IMHO this simply means that they detect logical corruptions at block level, i.e. they don't do crosschecks between blocks. Basically no more than what block checking (DB_BLOCK_CHECKING=TRUE, not block checksum) do.
> If the initialization parameter DB_BLOCK_CHECKSUM=TRUE, and if > MAXCORRUPT and NOCHECKSUM are not set, then specifying CHECK LOGICAL > detects all types of corruption that are possible to detect.
I love the part "all types of corruption that are possible to detect". This also means that some corruptions cannot be detected.
> It is my understanding that an RMAN "BACKUP VALIDATE" with "CHECK > LOGICAL" can, indeed, detect the sorts of corruption you are > referring to. > I've even used it for such purposes on a couple occasions -- but > since the results were "negative" I can't say for certain that it > really works... ;-)
I tested it explicitly. To do the test it is sufficient to delete an index entry from an index block or delete a row piece from a data block. Of course it is also possible to fake the ROWID in the index entry... Result is that only ANALYZE detects such situations.
That said, the last time I had to fight with logical corruptions was in, IIRC, 7.0.15. Since then only physical corruptions...
Best regards, Chris
-- Cheers, -- Mark Brinsmead Senior DBA, The Pythian Group http://www.pythian.com/blogs