Re: howto detect which index is corrupted

dominica_l_at_yahoo.com (Dominica Leung) wrote

> From the argument of ORA-600, the DBA go and look up
> and find out "SQL can't be execute due to index corrupted".

If the error message says that, it will also contain the index name. Thus no need to hunt the index.

However, an ORA-0600 will NOT say it.

Here's the format:
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"

The corrupted file id and block number are likely specified in []'s in the ORA-600 - not the object name). Thus the premise that it must be an index that is corrupted is a bs thumb suck. Or that it must be one of the 10 tables or 15 indexes. It could as well be the system tablespace or even file 0.

(now there's a question - when you get db block corruption on file 0, what file is it? File numbering in Oracle starts with file 1 as the SYSTEM tablespace)

> We know this SQL use 10 tables and
> 15 indexes.
> We have a UDUMP trace file. But look encrypted.
>
> How would you find out which indexes is corrupted?

Hmmm... is a udump trace ever encrypted? I have never seen that. Unless he was refering to a core dump? Or he gets scared reading a text file that contains a bunch of technical stuff without pretty pictures acompanying it.

The udump trace should/could contain the file id and block number that caused the problem.

Something like this:
***
Corrupt block relative dba: 0x00409c57 (file 0, block 40023) Bad header found during preparing block for write ***

or this:
***
Corrupt block relative dba: 0x074e612c (file 29, block 942380) Fractured block found during buffer read ***

File 29's details can be found in DBA_DATA_FILES. The owner of the block can be found using DBA_SEGMENTS.

Okay, once identified, you need to decide how to fix.

As for Mr. Interviewer NOT to allow test SQLs to be run and so on - that is a bunch of bs. My re-action would have been that only an idiot restricts the DBA to specific methods only in order to find, diagnose and fix a problem.

As for Mr. Interviewer's question.. that shows a very clear case of someone that know very little about Oracle and thinks otherwise.

So, I take it Dilbert was not allowed to run the technical side of interview and his boss did it instead?

--
Billy