Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:oracle_sqlfehler_protokoll

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

Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
dba/oracle_sqlfehler_protokoll.txt · Zuletzt geändert: 2018/02/07 14:16 von Gunther Pippèrr