Monitor all database errors and get email alerts
                                                 Last update (2008-02-15 12:12:05)
                                                                                                                   Date added (2008-02-15 11:33:25)

Summary
You can create a "monitor procedure" that keeps a history of all database errors to a table and notifies with emails for specific errors.

Explanation
You need to setup.
1. One trigger(LOG_ERRORS) that catches all the database errors
2. One table that the errors stored(HELPDESK.S_ERRORS)
3. One table with only the errors that you want to be notified (HELPDESK.S_ERRORS_SEND_EMAIL)
4. One procedure that sends emails and recipient

2. One table that the errors stored
CREATE TABLE helpdesk.S_ERRORS
(
  DATE_TIME  DATE,
  USERNAME   VARCHAR2(50 BYTE),
  OS_USER    VARCHAR2(30 BYTE),
  MACHINE    VARCHAR2(150 BYTE),
  PROGRAM    VARCHAR2(100 BYTE),
  MODULE     VARCHAR2(100 BYTE),
  ERROR      VARCHAR2(500 BYTE)
)
TABLESPACE HELPDESK
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;

3. One table with only the errors that you want to be notified
CREATE TABLE HELPDESK.S_ERRORS_SEND_EMAIL
(
  ERROR  VARCHAR2(500)
)
TABLESPACE HELPDESK
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;

Some sample data
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01658: unable to create INITIAL extent for segment'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01652 unable to extend temp segment by string in tablespace string'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01650 unable to extend rollback segment string by string in tablespace'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01651 unable to extend save undo segment by string for tablespace string'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01653 unable to extend table string'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01654 unable to extend index string'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01655 unable to extend cluster string.string by string in tablespace string'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01656 max # extents (string) reached in cluster string.string'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01657 invalid SHRINK option value'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01659 unable to allocate MINEXTENTS beyond string in tablespace string'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-20998: MODIFY_TRANSACTION'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01555 snapshot too old (rollback segment too small'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-12034: materialized view log on .. was younger than last refresh'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-04031: unable to allocate '); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-00060: deadlock detected while waiting for resource'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-16014: log 4 sequence# not archived, no available destinations'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-12012:'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-12008:'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-07445'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01632: max # extents'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01691: unable to extend lobsegment'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-01631: max # extents '); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-00202: controlfile'); 
INSERT INTO S_ERRORS_SEND_EMAIL ( ERROR ) VALUES ( 
'ORA-27091: skgfqio: unable to queue I/O'); 
commit;
 
1. One trigger that catches all the database errors.
CREATE OR REPLACE TRIGGER LOG_ERRORS
   AFTER SERVERERROR ON DATABASE
DECLARE

CURSOR cur IS SELECT * FROM helpdesk.s_errors_send_email;

CURSOR cur_recipients IS SELECT * FROM helpdesk.s_recipients;

   V_TERMINAL V$SESSION.TERMINAL%TYPE;
   V_OSUSER   V$SESSION.OSUSER%TYPE;
   V_MACHINE  V$SESSION.MACHINE%TYPE;
   V_IP       VARCHAR2(20);
   V_PROGRAM  V$SESSION.PROGRAM%TYPE;
   V_MODULE   V$SESSION.MODULE%TYPE;
   V_ERROR    VARCHAR2(1000);
   V_DATABASE V$PARAMETER.VALUE%TYPE;
   crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );

BEGIN

	  SELECT OSUSER, MACHINE, PROGRAM, MODULE INTO
	        V_OSUSER, V_MACHINE, V_PROGRAM, V_MODULE
      FROM V$SESSION
	  WHERE AUDSID = USERENV('SESSIONID');

	  V_IP := SYS_CONTEXT('USERENV','IP_ADDRESS');

   DECLARE
      V_ERR   VARCHAR2 (2000);

   BEGIN

   SELECT VALUE INTO V_DATABASE FROM v$parameter WHERE NAME = 'db_name';

               INSERT INTO HELPDESK.S_ERRORS(DATE_TIME, USERNAME, OS_USER, MACHINE, PROGRAM, MODULE, ERROR )
               VALUES (SYSDATE, USER, V_OSUSER, V_IP || ' (' || V_MACHINE || ')',
			   V_PROGRAM, V_MODULE, DBMS_UTILITY.FORMAT_ERROR_STACK);

	V_ERROR:= DBMS_UTILITY.FORMAT_ERROR_STACK;

	FOR i IN cur LOOP
        BEGIN
		
		IF SUBSTR(i.error, 0, 9) = SUBSTR(V_ERROR, 0, 9) THEN

		   FOR  c1 IN cur_recipients 
		   LOOP
		      
		   Send_Mail('skaparelis@ote.gr', c1.recipient,
                      'Subject: ' || 'DB Server:  ' || V_DATABASE || '  ' || 'Error: ' || V_ERROR || ' ' || crlf,
                      'DB Server:  ' || V_DATABASE || '  ' || crlf ||
					  'DATE:       ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')  || crlf ||
                      'USERNAME:   ' || USER  || crlf ||
                      'OSUSER:     ' || V_OSUSER || crlf ||
                      'MACHINE:    ' || V_IP || ' (' || V_MACHINE || ')' || crlf ||
                      'PROGRAM:    ' || V_PROGRAM || crlf ||
                      'MODULE:     ' || V_MODULE || crlf ||
                      'ERROR:      ' || V_ERROR || crlf);
	   		END LOOP;
        END IF;

		END;
      END LOOP;

   END;

EXCEPTION WHEN OTHERS THEN
NULL;

END;
/
Reviews
Filters
Search