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;
/
