Inhaltsverzeichnis
Erstellen eines Protokolls zur Überwachung von fehlerhaften SQL Statements in der Datenbank - AFTER SERVERERROR Trigger verwenden
Erstellt November 2014
Oracle 10g/11i/12c
Nutzen:
Oft ergibt sich das Problem, das die original SQL Fehlermeldung nicht in den Applikation Layer einer Applikation wirklich sichtbar werden. Läuft eine Anwendung als Hintergrund Job werden oft die SQL Fehler gar nicht mehr wahrgenommen oder im Log File übersehen.
Mit dem Einsatz des Error Trigger können die meisten SQL Fehler aufgezeichnet werden, in vielen Fällen auch mit den exakten SQL Statement, dass den Fehler verursacht hat.
Ziel:
- Erkennen von Applikationsfehlern
- Erkennen von Angriffsversuchen
Mit dem Database Event Trigger „AFTER SERVERERROR“ kann fehlerhaftes SQL in der DB aufgezeichnet werden.
Über eine autonome Transaktion „pragma autonomous_transaction“ kann diese Information in einer Error Tabelle gespeichert werden.
Da der SYS/SYSMAN/DBSMNP User sehr viele interne Fehler verursachen, werden diese User zur Zeit in aber „ausgeblendet“ um nicht zu viele Log Einträge in der Datenbank zu erzeugen.
Damit die Tabelle nicht „überläuft“ wird eine kleine Procedure und ein einfacher Job angelegt, der regelmäßig die alten Daten löscht.
Risko:
Der Trigger muss natürlich immer gültig sein! D.h. nach dem Anlegen als SYSTEM prüfen, das der Trigger auch valid ist. Ist der Trigger invalid, kann es zu Fehlermeldungen mit Fehlern in der Applikation kommen. Test im Training daher zuvor notwendig!
Beispiel Script um diesen Error Trigger anzulegen ( muss als SYS lokal auf der Maschine aufgerufen werden:
Installation:
- Script auf lokalen DB Server kopieren
- An der DB mit dem User SYS anmelden
- Script mit @create_global_errorlog.sql starten
Was wird angelegt:
- Tabelle SYSTEM.ora_error wird auf dem Tablespace SYSAUX angelegt
- Primary Key wird mit passenden Index SYSTEM.idx_ora_error_pk auf der Tabelle erzeugt
- Damit jeder die Error Tabelle auswerten kann, wird das lese Recht auf die Tabelle an Public gegranted ( kann optional bei Bedarf ausgeblendet werden)
- Eine Sequence für eine fortlaufende ID der Tabelle SYSTEM.ora_error_seq wird angelegt
- Der Logon Trigger wird erzeugt
- Der Trigger feuert zwar bei jeden Fehler, aber nur die Fehler von „normalen“ Anwender werden aufgezeigt.
- Sollen auch andere User in der DB von der Aufzeichnung ausgeschlossen werden, ist die Liste „IF ora_login_user in ('SYS','DBSNMP','SYSMAN')“ zu erweitern
- Für das Löschen der Einträge nach einer gewissen Zeit wird die Procedure SYSTEM.deleteOraErrorTrigTab erzeugt
- Über den Parameter p_keepdays kann beim Aufruf der Procedure eingestellt, wie viel Tage die Daten aufgehoben werden sollen
- Ein Job wird angelegt um regelmäßig die Löschroutine jeden Tag um 4:00 aufzurufen
- Über den Parameter 15 (p_keepdays ⇒ 15) wird definiert das die letzen 15 Tage in der DB verbleiben sollen
- Das letzte Statement in der Analyse zeigt ein Beispiel für eine regelmäßige Auswertung der Error Log Tabelle
Test:
- Anmelden mit einen normalen Standard User wie scott
- SQL Fehler erzeugen mit „select * from TABLE_NOT_EXITS“
- Auswerten des Errorlogs mit „select * from SYSTEM.ora_error “
- Fehler muss in der Tabelle auftauchen
Tabelle:
- create_error_trigger.sql
SET serveroutput ON SIZE 1000000 prompt DOC ------------------------------------------------------------------------------- Creating Error Log Tab FOR SQL Errors OVER the complete DB ------------------------------------------------------------------------------- # prompt DOC ------------------------------------------------------------------------------- Errorlog TABLE / SEQUENCE AND TRIGGER will be created ------------------------------------------------------------------------------- # prompt prompt '-------------------------------------------------------------------------------' prompt SET serveroutput ON SIZE 1000000 EXEC DBMS_OUTPUT.put_line('start create_global_errorlog.sql'); prompt "Create Table SYSTEM.ora_error and SEQUENCE SYSTEM.ora_error_seq" CREATE TABLE SYSTEM.ora_errors ( id NUMBER ,log_date DATE ,log_usr VARCHAR2 (30) ,terminal VARCHAR2 (50) ,err_nr NUMBER (10) ,err_msg VARCHAR2 (4000) ,stmt CLOB ,inst_id NUMBER(2) ) tablespace sysaux / CREATE UNIQUE INDEX system.idx_ora_error_pk ON system.ora_errors(id) tablespace sysaux; ALTER TABLE system.ora_errors ADD CONSTRAINT pk_ora_errpr PRIMARY KEY (id) enable validate; CREATE INDEX system.idx_ora_error_date ON system.ora_errors(log_date) tablespace sysaux; GRANT SELECT ON system.ora_error TO public; ----------- CREATE SEQUENCE SYSTEM.ora_errors_seq / ----------- prompt "Create the trigger log_error" CREATE OR REPLACE TRIGGER log_error AFTER SERVERERROR ON DATABASE DECLARE PRAGMA AUTONOMOUS_TRANSACTION; v_id NUMBER; v_sql_text ORA_NAME_LIST_T; v_stmt CLOB; v_count NUMBER; BEGIN v_count := ora_sql_txt (v_sql_text); IF v_count >= 1 THEN FOR i IN 1 .. v_count LOOP v_stmt := v_stmt || v_sql_text (i); END LOOP; END IF; FOR n IN 1 .. ora_server_error_depth LOOP IF ora_login_user IN ('SYS','DBSNMP','SYSMAN') THEN -- do nothing NULL; ELSE SELECT SYSTEM.ora_errors_seq.NEXTVAL INTO v_id FROM DUAL; INSERT INTO SYSTEM.ora_errors (id ,log_date ,log_usr ,terminal ,err_nr ,err_msg ,stmt ,inst_id) VALUES (v_id ,SYSDATE ,ora_login_user ,ora_client_ip_address ,ora_server_error (n) ,ora_server_error_msg (n) ,v_stmt ,ora_instance_num); END IF; COMMIT; END LOOP; END log_error; / ------ Clean procedure CREATE OR REPLACE PROCEDURE system.deleteOraErrorTrigTab (p_keepdays NUMBER) IS BEGIN DELETE FROM SYSTEM.ora_errors WHERE log_date+p_keepdays < sysdate; COMMIT; END; / ------ Clean procedure job DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'begin system.deleteOraErrorTrigTab (p_keepdays => 15); end;' ,next_date => sysdate ,INTERVAL => 'to_date(to_char(sysdate+1,''mm/dd/yyyy'')||'' 04:00:00'',''mm/dd/yyyy hh24:mi:ss'')' ,no_parse => FALSE ,instance => 0 ,force => TRUE ); END; / commit; SHOW errors ------ Analyse example: COLUMN anzahl format 9999999999 COLUMN HOUR format A9 COLUMN LOG_USR format A10 COLUMN ERR_NR format 999999999 COLUMN mesg format A30 SELECT COUNT (*) AS anzahl ,TO_CHAR (log_date, 'dd/mm hh24')||'h' AS HOUR ,nvl(LOG_USR,'n/a') AS LOG_USR ,ERR_NR ,substr(ERR_MSG,1,200) mesg FROM SYSTEM.ora_errors WHERE nvl(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP') GROUP BY TO_CHAR (log_date, 'dd/mm hh24')||'h' ,nvl(LOG_USR,'n/a') ,ERR_NR ,substr(ERR_MSG,1,200) ORDER BY 2,1 ; ------------------------------------------------------ prompt EXEC DBMS_OUTPUT.put_line('end create_global_errorlog.sql'); prompt
Create Trigger File from github
Auswerten
- report_global_errorlog.sql
--============================================================================== -- GPI - Gunther Pippèrr -- Desc: HTML Report for the entries in the audit log -- see : https://www.pipperr.de/dokuwiki/doku.php?id=dba:oracle_sqlfehler_protokoll -- Date: September 2019 -- --============================================================================== col SPOOL_NAME_COL new_val SPOOL_NAME SELECT REPLACE(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||TO_CHAR(SYSDATE,'dd_mm_yyyy_hh24_mi')||'_sql_error_log.html','\','_') --' resolve syntax highlight bug FROM my editer .-( AS SPOOL_NAME_COL FROM dual / SET verify off SET linesize 450 pagesize 2000 column anzahl format 999G999 heading "Count" column first_log_entry format A18 heading "First Entry" column last_log_entry format A18 heading "Last Entry" column LOG_USR format A20 heading "DB Schema" column ERR_NR format 99999 heading "Ora Err | Number" column mesg format A100 heading "Ora Err | Message" column HOUR format A16 heading "Hour" column stmt format A250 heading "SQL Statemment" spool &&SPOOL_NAME SET markup html ON ttitle left "SQL Error Total Log Summary" skip 2 SELECT COUNT (*) AS anzahl ,TO_CHAR(MIN(log_date),'dd.mm.yyyy hh24:mi') first_log_entry ,TO_CHAR(MAX(log_date),'dd.mm.yyyy hh24:mi') last_log_entry ,NVL(LOG_USR,'n/a') AS LOG_USR ,ERR_NR ,SUBSTR(ERR_MSG,1,300) mesg FROM SYSTEM.ora_errors WHERE NVL(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP') GROUP BY NVL(LOG_USR,'n/a') ,ERR_NR ,SUBSTR(ERR_MSG,1,300) ORDER BY 2,1 / ttitle left "SQL Error Hour Report" skip 2 SELECT COUNT (*) AS anzahl ,TO_CHAR (log_date, 'dd.mm.yyyy hh24')||'h' AS HOUR ,NVL(LOG_USR,'n/a') AS LOG_USR ,ERR_NR ,SUBSTR(ERR_MSG,1,300) mesg FROM SYSTEM.ora_errors WHERE NVL(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP') GROUP BY TO_CHAR (log_date, 'dd.mm.yyyy hh24')||'h' ,NVL(LOG_USR,'n/a') ,ERR_NR ,SUBSTR(ERR_MSG,1,300) ORDER BY 2 / ttitle left "SQL Error Log All Entries " skip 2 SET LONG 64000 WITH ErrorLog AS ( SELECT stmt , log_date , LOG_USR , ERR_NR , SUBSTR(ERR_MSG,1,300) mesg , DBMS_LOB.getlength(STMT) len FROM SYSTEM.ora_errors WHERE NVL(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP') ) SELECT COUNT (*) AS anzahl , TO_CHAR(MIN(log_date),'dd.mm.yyyy hh24:mi') first_log_entry , TO_CHAR(MAX(log_date),'dd.mm.yyyy hh24:mi') last_log_entry , LOG_USR , ERR_NR , mesg , DBMS_LOB.SUBSTR(stmt,4000,1) sql_part1 , CASE WHEN len > 4000 THEN DBMS_LOB.SUBSTR(stmt,4000,4001) END sql_part2 , CASE WHEN len > 8000 THEN DBMS_LOB.SUBSTR(stmt,4000,8001) END sql_part3 , CASE WHEN len > 12000 THEN DBMS_LOB.SUBSTR(stmt,4000,12001) END sql_part4 , CASE WHEN len > 16000 THEN DBMS_LOB.SUBSTR(stmt,4000,165001) END sql_part5 FROM ErrorLog GROUP BY LOG_USR , ERR_NR , mesg , DBMS_LOB.SUBSTR(stmt,4000,1) , CASE WHEN len > 4000 THEN DBMS_LOB.SUBSTR(stmt,4000,4001) END , CASE WHEN len > 8000 THEN DBMS_LOB.SUBSTR(stmt,4000,8001) END , CASE WHEN len > 12000 THEN DBMS_LOB.SUBSTR(stmt,4000,12001) END , CASE WHEN len > 16000 THEN DBMS_LOB.SUBSTR(stmt,4000,165001) END ORDER BY 1 / SET markup html off spool off ttitle off -- works only in a ms windows environment -- auto start of the result in a browser window host &&SPOOL_NAME