ON SERVER ERROR - SHOULD WE TRAP TEXT?

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Wed, 21 Apr 2004 23:43:08 -0500
Message-ID: <A366E86AF2FBD611AB920002B31E58B0096DC79B@conmsx07.corp.acxiom.net>

We took a trigger from Nico Booyse made some minor changes and with some shell scripts we put out an error report on all our production databases daily (see below trigger)

        It was really to ascertain if we really got errors on the DBs people said they did, so we wouldn't be looking for problems in the wrong DB.

We wanted to get the text for some of the messages like 900 etc. since we were seeing 1000s of some of these occurring. I got the answer from Connor McDonald's latest book and one of the other DBAs (Rose Fidanzo) coded it up. It works but she found METALINK saying there was a Bug No. 3124081 that could bring the database down. Their workaround was to not accept the ones that caused a problem. Instead, we elected to have an IN list for what we were interested in.

        But I'm really nervous about putting new version in production systems. Especially as we're supposed to save them money not crashing their DBs.

        So I was wondering if we're pretty safe in using the IN list or should we just forget about it till the (Fixed in Product Version 10.0) patch?

	Thanks
	Larry W.

Also Bug No. 3264549

SET ECHO ON
SET TIMING ON

rem -----------------------------------------------------------------------

rem Filename: db-error.sql
rem Purpose: Log all database errors to a table rem Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and remGRANT SELECT ON SYS.V_$SESSION required

rem Date:       21-Mar-2000
rem Author:     Nico Booyse (booysen_at_saps.org.za)
rem -----------------------------------------------------------------------
drop trigger log_errors_trig;

drop table log_errors_tab;
create table log_errors_tab

    (error varchar2(30)

    ,timestamp date
    ,username  varchar2(30)
    ,osuser    varchar2(30)
    ,machine   varchar2(64)
    ,process   varchar2(09)
    ,program   varchar2(48)
    ,sqltext   varchar2(1000)

    )
TABLESPACE TOOLS
STORAGE (INITIAL 1M NEXT 1M MAXEXTENTS 9999 PCTINCREASE 0) ;

create or replace trigger log_errors_trig after servererror on database
declare

var_user     varchar2(30);
var_osuser   varchar2(30);
var_machine  varchar2(64);
var_process  varchar2(09);
var_program  varchar2(48);
var_sqltext  varchar2(1000);
sql_text     ora_name_list_t;
n            number;

begin
select s.username, s.osuser, s.machine, s.process, s.program into var_user, var_osuser, var_machine, var_process, var_program from sys.v_$session s

      ,sys.v_$sqlarea t
where  s.audsid         = userenv('sessionid')

 and s.prev_sql_addr = t.address(+)
 and s.prev_hash_value = t.hash_value(+) ;
if dbms_standard.server_error(1) in (900,942)  then
   n:= ora_sql_txt(sql_text);
   for i in 1..n loop
   var_sqltext:=var_sqltext||sql_text(i);    end loop;
end if;

insert into log_errors_tab
 values(dbms_standard.server_error(1),sysdate,var_user,

     var_osuser,var_machine,var_process,var_program,var_sqltext); end;
/

SHOW ERRORS
SELECT * FROM Oracle_support;
SELECT * FROM log_errors_tab;

Created at 21-APR-2004 14 00 01 for {DATABASE NAME}  

ERROR                            COUNT(*) OSUSER       USERNAME
-----------------------------   --------- ------------ ------------
1                                     291 frmwk        EDRPUSER         
1                                       3 openqaar     EDRPUSER         
1                                     153 servclus     EDRPUSER         
1                                       2 vsweb        VSWEB            
1017                                    1 IKaliapp                      
1400                                    6 frmwk        EDRPUSER         
1401                                    2 frmwk        EDRPUSER         
1722                                    3 XXXXXXXX     EDRPUSER         
1795                                    6 servclus     EDRPUSER         
20101                                  98 frmwk        EDRPUSER         
20101                                  19 servclus     EDRPUSER         
2291                                  122 frmwk        EDRPUSER         
22920                                   2 vsweb        VSWEB            
600                                     1 servclus     EDRPUSER         
900                                  7612 servclus     EDRPUSER         
904                                     9 XXXXXXXX     EDRPUSER         
918                                     1 XXXXXXXX     EDRPUSER         
920                                     5 107599       EDRPUSER         
933                                     3 XXXXXXXX     EDRPUSER         
936                                     1 XXXXXXXX     EDRPUSER         
942                                    12 dsadm        EDMREAD          
942                                   371 dsadm        VANTUSER
 

32 rows selected.  

00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate
key.
// For Trusted Oracle configured in DBMS MAC mode, you may see
// this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.
 

01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
 

01400, 00000, "cannot insert NULL into (%s)"
// *Cause:
// *Action:
 

01401, 00000, "inserted value too large for column"
// *Cause:
// *Action:
 

01722, 00000, "invalid number"
// *Cause:
// *Action:
 

01795, 00000, "maximum number of expressions in a list is 1000"
// *Cause:
// *Action:
   

02291, 00000,"integrity constraint (%s.%s) violated - parent key not found"
// *Cause: A foreign key value has no matching primary key value.
// *Action: Delete the foreign key or add a matching primary key.
 

22920, 00000, "row containing the LOB value is not locked"
// *Cause: The row containing the LOB value must be locked before
// updating the LOB value.
// *Action: Lock the row containing the LOB value before updating the LOB
// value.
 

00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug - the first argument is the internal error
number  

00900, 00000, "invalid SQL statement"
// *Cause:
// *Action:
 

00904, 00000, "invalid column name"
// *Cause:
// *Action:
 

00918, 00000, "column ambiguously defined"
// *Cause:
// *Action:
 

00920, 00000, "invalid relational operator"
// *Cause:
// *Action:
 

00923, 00000, "FROM keyword not found where expected"
// *Cause:
// *Action:
 

00933, 00000, "SQL command not properly ended"
// *Cause:
// *Action:
 

00936, 00000, "missing expression"
// *Cause:
// *Action:
 

00942, 00000, "table or view does not exist"
// *Cause:
// *Action:

---

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Apr 21 2004 - 23:40:33 CDT