Saturday, May 12, 2007

I didn't know that! (or an obscure bug in 9.2.0.5)

My guys at work came across a very obscure bug in one of our Oracle 9.2.0.5 databases the other day. A SQL Loader script that has been working for a long while started failing with an ORA-03114 and the corresponding SQL Loader error was "SQL*Loader-941: Error during describe of table". A quick look at the script to verify that nothing has changed and that everything looks good with the correct username and password being used, etc.

A search of Metalink provided some links describing similar problems but not really related to what we were experiencing. Fortunately looking at the trace files generated in the user dump directory provided some clues Publishto where the error was occurred and searching Metalink on those gave as Metalink Note: 343199.1 (if the database was started up by other means than from SQL*Plus, the bug might occur) which at first glance seems to gives us pause as to "Huh?" and "We didn't do that..." reaction. A further clue in our log directories showed that the script had ran successfully a couple of days prior which provided a "missing link". Oh yeah, we had shutdown the database for a cold backup and restarted it using RMAN. The note indicated that the bug would occur intermittently when the database has been started up using other means other than from SQL*Plus. So we shut down the db and restarted it from SQL*Plus and tried the script again, it worked! Wow! so, what is different in the process of database startup when doing that from SQL*Plus compared to doing that from RMAN? Unless someone can compared the two code, I'm afraid that we will never know. The Note did said that the bug was fixed in 9.2.0.7 so hopefully that "fix" is carried to 10g too.

It was a very good feeling that we were able to "fix" the issue.

No comments: