====== 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:**

<code sql 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

</code>

----

==== Create Trigger File from github ====

[gistgit project=gpipperr/OraPowerShell&file=sql/01-db-setup/create_global_errorlog.sql ]

See => https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/01-db-setup/create_audit_log_database.sql

----

==== Auswerten ====

Siehe auch https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/01-db-setup/report_global_errorlog.sql


<code plsql 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


</code>
===== Quellen =====

Oracle:

  * http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#CHDHFBBG

Web:

  * http://www.red-database-security.com/scripts/oracle_error_trigger.html


Bzgl. Oracle System Events siehe http://www.morganslibrary.org/reference/system_events.html