The other day our monitoring system had thrown a couple of alerts for a client. It was reporting there were errors in the client’s alertlog. I logged in and checked the alertlog and sure enough there were multiple dreadred ORA-00600 errors being reported in the alertlog.
The ORA-00600 errors are labeled by Oracle as generic internal errors for Oracle program exceptions. They can be very serious errors as they can be related to data corruption. Usually the only way to fix these errors is by submitting a ticket with Oracle Support as it’s an internal process having issues.
Since the client was taking backups via expdp I kicked off an rman backup with the validate option. This will make sure there isn’t any physical corruption.
I continued to research the error. Next I looked for additional errors or other internal errors around the same time the problem occurred. There were no additional errors that were related. I then took a look at the trace files that were generated. In the trace files you can usually see what object is possibly corrupt or having a problem by looking for something similar to the below:
*** 2012-2-22 22:22:22.22 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [12700], [2997], [16778259],... Current SQL statement for this session: select * from users where username='xyz'
Though there were no such errors in the trace files. So I continued to look for a reason for the error. I then looked at the time of the error and the crontab for something that might be kicking off around the same time… and BAM found an export job that was being kicked off right before the errors, but what about the export job was causing the problem? I thought for sure we would have to get Oracle involved and open a TAR.
I verified that it was indeed the export was causing the errors by kicking off the job manually. I tail –f the alertlog and then manually ran the export script. The errors showed up as soon as the export kicked off. There wasn’t anything unusual about the expdp script. It was simply exporting a schema with all the default options of expdp. To find out exactly why the expdp was causing the errors I started to manually run the export with different options until I finally narrowed down the issue.
The following ORA-00600 errors would only happen if I was exporting the views of the schema being exported.
EXP-00008: ORACLE error 600 encountered ORA-00600: internal error code, arguments: [12700], [90], [4236207], [0], [4236712], [25], [], [] . . exporting table TABLE1 53472 rows exported EXP-00008: ORACLE error 600 encountered ORA-00600: internal error code, arguments: [12700], [90], [4236207], [4], [4236916], [25], [], [] . . exporting table TABLE2 11119 rows exported EXP-00008: ORACLE error 600 encountered ORA-00600: internal error code, arguments: [12700], [90], [4236207], [16], [4237199], [25], [], [] . . exporting table TABLE3 0 rows exported EXP-00008: ORACLE error 8103 encountered ORA-08103: object no longer exists . . exporting table TABLE4 626970 rows exported EXP-00008: ORACLE error 8103 encountered ORA-08103: object no longer exists . . exporting table TABLE5 34998 rows exported EXP-00008: ORACLE error 8103 encountered ORA-08103: object no longer exists . . exporting table TABLE6 58629 rows exported EXP-00008: ORACLE error 8103 encountered
So that right away tells me there is something wrong with the views or something associated with them. The errors also only seemed to happen when exporting out certain objects in the database.
Those objects were only tables and are where I started to look for my problem. I first analyzed the tables with the validate and cascade options to see if that would fix the problem, but the tables and indexes were free of error. I was again stuck at what was happening.
I returned to the views since that’s what was causing the problem in the export job. I looked for the views from the schema in dba_views, but for some reason I wasn’t able to query the dba_views table. I could get a count from the table, but I couldn’t actually select data from it. Well of course the dba_views table is just a view on other system tables. When drilling down to those system tables I found the root cause of my problem the sys.objauth$ . This system table holds data that is related to grants made on objects in the database. This is the reason why the export was throwing errors when trying to export tables with grants enabled. A simple fix for corrupt system type objects is to run catalog.sql and catproc.sql. These two scripts will create/recreate internal views/procedures/packages/data dictionary views etc.
I ran the above two scripts and it did indeed fix the issue. We were lucky it was a system table and not user/application data that became corrupt.