Oracle Alert Log

The other day, our customer reported that some functions in our system were slow. Others were just plain hung. This issue went to our DBA Team. They asked me about some of the functionality that was involved. I provided them the overall flow, as well as the major database tables involved. Then I went back to my normal work.

Our manager held a conference call to work the issue. I share a room with the manager, so I overheard some of the talk. My manager was interested in the DBAs looking at the Oracle Alert Log. Now I have heard this term before. But I did not know exactly what it was. Time for a little research.

The alert log is a text file containing messages and errors. Specifically it will contain ORA-00600 deadlock errors. It also contains database startup and shutdown events. The filename itself is alert_.log. The location of the file is governed by parameter background_dump_test. Go figure.

Apparently the alert log is the first place a DBA should look when there are database problems. In fact, a proactive DBA will be monitoring this file a couple times a day. One interesting fact is that you can write your own messages to the alert log using the ksdwrt procedure in the dbms_system package.

If you have Oracle 11g, the alert log can be directly queried using the X$DBGALERTTEXT fixed table. However you must connect as SYSDBA to access this table like you would any other fixed table. Finally I should mention that Oracle itself computes a number of metrics based on the contents of the alert log. Now I am at the beginning of my journey to becoming a DBA. I just don’t know if that is where I want to go.