One of my clients has a database job that runs weekly in the early hours of the morning. I have a cron job configured on their server to check the database alert log for errors on a regular basis and email me with any that it finds.
This morning when I checked my emails there was one from that job reporting the following:
Errors in file /u01/app/oracle/10.2/rdbms/log/<SID>_ora_194248.trc:
ORA-00600: internal error code, arguments: [kdsgrp1], , , , , , ,  Wed May 27 03:17:28 2015
ORA-00600 errors are internal Oracle errors and on the Oracle support site they have a tool where you can enter the arguments for the error (in this case kdsgrp1) and see if there is anything on the Oracle support site about it.
I duly did this and it returned a whole range of known bugs and issues. The one that looked likely to be most relevant for my situation was one that said the “error is thrown when a fetch operation fails to find the expected row.”
It also said this could be caused by any of the following:
- Lost writes
- Parallel DML issues
- Index corruption
- Data block corruption
- Consistent read [CR] issues
- Buffer cache corruption
As the client had recently had a disk failure which resulted in us having to recreate some indexes, I immediately suspected index corruption to be the cause.
The first thing to do in this situation is to examine the trace file named in the alert log and determine the SQL that caused the ORA-00600 [kdsgrp1] error.
A good way to do this is to open the trace file and search for the text “Plan Table”. This will show you the Explain Plan for the SQL that was being executed when the error occurred.
In this case, it was a select statement that was doing a “table access by index rowid”. Armed with the index name from this Explain Plan, I could then check the index for corruption.
The way to check for index corruption is to use the
analyze index <index_name> validate structure;
command which I ran using sqlplus and I immediately received the ORA-00600 [kdsgrp1] error, confirming a problem with the index.
I used PL/SQL Developer to generate the SQL statement to create the offending index. Then I dropped the index and recreated it using this SQL statement I’d just generated. I then re-analyzed the index, this time with no problems.