RE: Daily Heath report script

From: Marquez, Chris <cmarquez_at_collegeboard.org>
Date: Fri, 4 Nov 2005 14:48:04 -0500
Message-ID: <B30C2483766F9342B6AEF108833CC84E05BD608C@ecogenemld50.Org.Collegeboard.local>

Dave,

Good info thanks.

>> I also loaded all ORA- error descriptions, causes, actions 
>> off oraus.msg into an Oracle table, so that I could join to 
>> it yielding a little help on messages when reporting.

Nice!

Chris Marquez
Oracle DBA

-----Original Message-----
From: Herring Dave - dherri [mailto:Dave.Herring_at_acxiom.com] Sent: Fri 11/4/2005 2:29 PM
To: Marquez, Chris; oracle-l_at_freelists.org Subject: RE: Daily Heath report script

Chris,

It should capture all ORA- errors, but I also assume some are severe enough to complete crap out the process so it doesn't get captured. For example, I did find a few ORA-600 errors that were captured, but no 7445s. I checked all alert logs for the past 90 days (they are weekly renamed/date stamped, gzip'ed, then deleted after 90 days) and found all but the ORA-07445 errors were captured that also appeared in the alert log.

This has worked fine for releases 9.2.0.4, 9.2.0.5, and 9.2.0.6.

For what its worth, I thought I'd add the DDL for the Oracle table used to hold error detail:

CREATE TABLE <audit user>.AUD_ORA_ERROR_TB

( ORA_ERROR NUMBER,

        RUN_DT DATE,
        USERNAME VARCHAR2(30),
        ORA_ERROR_NBR_SEQ NUMBER,
        OSUSER VARCHAR2(30),
        MACHINE VARCHAR2(64),
        PROCESS VARCHAR2(12),
        PROGRAM VARCHAR2(48),
        SQL_TEXT VARCHAR2(4000)

   ) PCTFREE 0 TABLESPACE TOOLS
  PARTITION BY RANGE (RUN_DT)
(PARTITION AUOE_200503_RP VALUES LESS THAN ('01-APR-2005')   PCTFREE 0 TABLESPACE TOOLS,
  PARTITION AUOE_200504_RP ... ) I also loaded all ORA- error descriptions, causes, actions off oraus.msg into an Oracle table, so that I could join to it yielding a little help on messages when reporting.

Dave

> -----Original Message-----
> From: Marquez, Chris [mailto:cmarquez_at_collegeboard.org]
> Sent: Friday, November 04, 2005 12:35 PM
> To: Herring Dave - dherri; oracle-l_at_freelists.org
> Subject: RE: Daily Heath report script
>
> Dave,
>
> This looks really interesting...not sure I'm aware of the
> DBMS_STANDARD.SERVER_ERROR package.
>
> I'm trying to follow the logic of the code...one question.
> Does code/proc track *all* db "server" errors or only "ORA-" errors that
> happen for (failed) executed SQL.
> Meaning catch all of the (same) ORA- errors found in the alert log, plus
> those thrown to individual SQL sessions?
>
> What version of Oracle do you run this on?
>
> Thanks,
>
> Chris Marquez
> Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 04 2005 - 13:50:33 CST