28 February, 2010

Some Common Errors - 5 - Not reviewing the alert.log and trace files

It seems that there are DBAs who do not look at the alert.log and/or trace files generated on the server. Neither do they manually login and look at the logs/traces nor do they rely on an automated tool to do so -- or the automated tool is "scripted" or "hardcoded" to look for only a very short list of possible messages.

Queries like
a. The XXX MV Refresh Job is failing. Why is it failing ?
b. A User calls me and tells me that Oracle is reporting that a particular datafile 'N' is offline. When did the file go offline, for what reason ?
c. A particular datablock is corrupt. Since when has it been corrupt ?
d. Are any ORA-1555 errors occuring ?
e. When did Tablespace USERS hit 100% and stop auto-extending ?

can be answered simply by the DBA looking at the alert.log and/or trace files on occassion if not religiously and frequently. Oracle did introduce configurable Alerts in OEM and the views DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY but I am not sure if these are being configured and used.

.
.
.

10 comments:

Kamran Agayev said...

Totally agree with you Hemant. It should be daily job of any DBA to check trace logs and alert.log file for any errors

Moreover, OEM should be used and e-mail should be configured to get the errors automatically to e-mail

Coskan Gundogar said...

Hemant,

Good point and it is very easy thing to send a mail or to monitor ORA- errors warnings in alert log. I really don't understand why a guy who calls himself a DBA does not monitor them.

There is one thing though, afaik both DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY
need diagnostics pack license, am I wrong?

Hemant K Chitale said...

Coskan,

In my opinion, these are not part of the Diagnostic Pack (or any of the "additional licence" EM Packs).

See http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#CIHGFIAF

Hemant K Chitale

Coskan Gundogar said...

Hemant,

I was thinking like that because in OEM if you do not have diagnostics pack license it does not let you go into the details of Alerts section on the main page.

I tried to trace the session to see what sql it is actually running but interestingly OEM does not work as I was thinking. It looks like my login is not running the commands but DBSNMP or SYSMAN which is something new and a research subject to me.

Anyways, Do you have any idea where is that Alerts section getting the information from ?

Hemant K Chitale said...

Coskan,
"f you do not have diagnostics pack license it does not let you go into the details of Alerts section on the main page". Are you sure ? I did not think that it would work that way. Oracle doesn't record what licence you have purchased -- licences aren't installed. So, by itself, the software wouldn't prevent you from using a feature -- except that Oracle trusts you to purchase the licence.

Hemant K Chitale

Coskan Gundogar said...

Let me clarify the way we work without license.

During the very first OEM login it asks you 3 radio button type question about what licenses you have. If you do not select the ones you don't have it does not let you go in to those options. Eg Performance tab is disabled after you select that you don't have the option.

For alerts section you can see the alerts but not alert details. Once you select them you get the warning below

"The page requested is part of the Database Diagnostics Pack.

You are not yet licensed to use the Database Diagnostics Pack. If you like this functionality, please see your super administrator about obtaining a license.Database Diagnostics Pack
Oracle Diagnostics Pack 10g for database offers a complete, cost-effective, and easy to use solution for managing the performance of Oracle Database environments by providing unique functionality such as automatic identification of performance bottlenecks, guided problem resolution, and comprehensive system monitoring.
"

This is from an Enterprise manager DB by the way.

Hemant K Chitale said...

Coskan,

Aah ! I see.
It is still on "trust" basis. The software doesn't detect what is installed but trusts you to be honest and then configures itself accordingly.

Hemant K Chitale

Hemant K Chitale said...

Coskan,

I use Database Control on 10gR2.
I think that the alerts in Database Control come from MGMT_% tables for Metric and not DBA_OUTSTANDING_ALERTS or DBA_ALERTS_HISTORY (tracing my Database Control shows no access to these to ALERT views). Therefore it does seem that they are covered by the Diagnostic Pack Licence component "Event history and metric history (database and host)".

It would be a shame if such basic alerts require the Diagnostic Pack !
Furthermore the Diagnostic Pack is available only on the Enterprise Edition. How are Standard Edition DBAs to access these alerts, then ?

Hemant K Chitale

Coskan Gundogar said...

I think you are true about the source of OEM alert section so I need to step back about my initial question.

I think Oracle needs to clarify tables with their actual names instead of DBA_HIST_* etc.

For the standart edition issue I think it is all mess about Diagnostics Pack. Tables are there but they are not available in theory. Interesting

By the way thank you very much for staying with me on the subject.

Hemant K Chitale said...

Coskan,
I only saw your last comment yesterday.
I haven't worked on SE but from your description, it seems as if SE is a "EE without having properly / cleanly removed certain views and objects".

Hemant