Einsatz von After Servererror-Triggern beim Troubleshooting von Datenbankfehlern

02.
Februar
2015
Veröffentlicht von: Elke Fritsch

Ein Kollege wollte kürzlich wissen, wie man das Statement eruieren könnte, das nachts für den folgenden Eintrag im Alert.log der Datenbank sorgte: ORA-1652...

Ein Kollege wollte kürzlich wissen, wie man das Statement eruieren könnte, das nachts für den folgenden Eintrag im Alert.log der Datenbank sorgte:

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Mir fiel dazu der Datenbanktrigger AFTER SERVERERROR ON DATABASE ein, der mit Oracle 8i eingeführt wurde. Eine Suche nach dieser Syntax in Verbindung mit ORA-1652 führte schnell zu einem Opens external link in new windowCode, der auf den ersten Blick nichts zu wünschen übrig ließ und außerdem von Tom Kyte stammte.
Hier ist der Trigger in leicht veränderter Form. Um Database-Trigger zu erstellen, benötigt man das Recht ADMINISTER DATABASE TRIGGER.

conn system/sys
CREATE TABLE temp_ts_problems (errmsg VARCHAR2 (4000));
CREATE OR REPLACE TRIGGER ora_1652_trig
   AFTER SERVERERROR ON DATABASE
DECLARE
   v_sql_text   ora_name_list_t;
   v_cnt        NUMBER;
   v_errmsg     VARCHAR2(4000);
BEGIN
   IF is_servererror(1652) THEN
       -- Damit kann man das Statement abfangen
       v_cnt := ora_sql_txt(v_sql_text);
       FOR i IN 1 .. v_cnt  LOOP
         v_errmsg := v_errmsg||v_sql_text(i);
       END LOOP;
       INSERT INTO temp_ts_problems
       VALUES('Benutzer: ' || ora_login_user||
              ', Datum: '||to_char(sysdate, 'dd.mm.rr hh24:mi:ss')||
              ', SQL-Statement: '||v_errmsg);
   END IF;
END;
/

 Ein paar Erklärungen zur Syntax

  • ora_name_list_t ist eine Nested Table aus dem Package DBMS_STANDARD: TYPE ora_name_list_t IS TABLE OF VARCHAR2(64)
  • is_servererror, ora_login_user und ora_sql_txt sind sogenannte Attributfunktionen von System-Events, die nur in Datenbank-Triggern verwendet werden.
  • is_servererror übernimmt den SQLCODE eines Fehlers und gibt TRUE zurück, falls dieser unter den 5 ersten Fehlern im Errorstack steht, ansonsten false.
  • Über ora_login_user bekommt man den Verursacher des Fehlers
  • ora_sql_text liefert das Statement, das den Trigger ausgelöst hat, als Zeilen der Nested Table ora_name_list_t über einen OUT-Parameter sowie die Zahl der Zeilen über den RETURN- Wert zurück. Deshalb muss man den Text in einer Schleife aufsammeln.

Weitere z. T. nützliche Funktionen:

  • ora_server_error(1) würde den ersten Fehler im Errorstack liefern. Das braucht man nur, wenn man den Trigger generell für das Fehlerlogging nutzen will.
  • ora_sysevent informiert über das triggernde Event, hier SERVERERROR.

Folgende Fehler kann der Trigger laut Opens external link in new windowOracle-Dokumentation nicht abfangen

  • ORA-01403: no data found
  • ORA-01422: exact fetch returns more than requested number of rows
  • ORA-01423: error encountered while checking for extra rows in exact fetch
  • ORA-01034: ORACLE not available
  • ORA-04030: out of process memory when trying to allocate string bytes (string, string)

Tests

Erst den Temp-Tablespace drastisch verkleinern

ALTER DATABASE TEMPFILE 'e:\oracle\oradata\o11g\temp01.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE 50M;

als Applikations-User eine aufwendige Sortierung durchführen

conn scott/tiger
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c ORDER BY 1;
=>
FEHLER in Zeile 2:
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden

nd als User System die Fehlertabelle abfragen

conn system/sys
SELECT * FROM temp_ts_problems;
=>
ERRMSG
--------------------------------------------------------------------------------
Benutzer: SCOTT, Datum: 30.01.2015 09:10:24', SQL-Statement: SELECT a.object_name
FROM all_objects a, all_objects b, all_objects c ORDER BY 1

Was will man mehr ?

Es gibt aber leider noch andere Szenarios, die den Fehler ORA-1652 auslösen können, z. B. eine Index-Reorganisation oder ein Index-Aufbau.
Dazu erzeugen wir als System im Userschema eine Tabelle mit 2 Mio Zeilen (Skript Initiates file downloadbig_tab.sql, Copyright wieder Tom Kyte. Der ausführende User braucht Select-Rechte auf dba_objects) und darauf einen mehrspaltigen Index

CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id);
=>
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-06502: PL/SQL: numerischer oder Wertefehler
ORA-06512: in Zeile 8
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden

Das Create-Index-Statement ist aber leider nicht in der Fehlertabelle gelandet.

conn system/sys
SELECT * FROM temp_ts_problems;
=>
ERRMSG
----------------------------------------------------
Benutzer: SCOTT, Datum: 30.01.2015 09:10:24', SQL-Statement: SELECT a.object_name
FROM all_objects a, all_objects b, all_objects c ORDER BY 1

Weitere Tests ohne Einschränkung auf ORA-1652 ergaben, dass das Problem bei allen getesteten DDL-Statements auftauchte, aber nur in Version 11g.
In den Oracle Versionen 9i und 10g macht der Trigger genau das, was er soll, unabhängig von der Art des Statements.

Um dem Fehler auf die Spur zu kommen, muss man nur den Trigger etwas umschreiben.

conn system/sys
CREATE OR REPLACE TRIGGER ora_1652_trig
   AFTER SERVERERROR ON DATABASE
DECLARE
   v_sql_text   ora_name_list_t;
   v_cnt        NUMBER;
   v_errmsg     VARCHAR2(4000);
BEGIN
   IF is_servererror(1652) THEN
       DBMS_OUTPUT.PUT_LINE('Event: '||ora_sysevent||
                           ', Fehlernummer: '||ora_server_error(1));

       v_cnt := ora_sql_txt(v_sql_text);
       DBMS_OUTPUT.PUT_LINE('Anzahl der Zeilen in der nested Table: '||NVL(v_cnt, 0));
       DBMS_OUTPUT.NEW_LINE;
       FOR i IN 1 .. v_cnt  LOOP
         v_errmsg := v_errmsg||v_sql_text(i);
         DBMS_OUTPUT.PUT_LINE(v_errmsg);
       END LOOP;
   END IF;
END;
/

conn scott/tiger
set serveroutput on
CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id);
Event: SERVERERROR, Fehlernummer: 1652
Anzahl der Zeilen in der nested Table: 0

 ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id)
          *
FEHLER in Zeile 2:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-06502: PL/SQL: numerischer oder Wertefehler
ORA-06512: in Zeile 11
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden

Der Fehler wird also durchaus abgefangen, aber bei der Füllung der nested table über ora_sql_text geht etwas schief. Weil die obere Grenze der Schleife NULL ist, bekommt man dann den Fehler ORA-06502: PL/SQL: numerischer oder Wertefehler und als interner Fehler löst dies die Meldung ORA-00604: Fehler auf rekursiver SQL-Ebene 1 aus

Lösung

Wenn Oracle das auslösende SQL-Statement nicht liefert, muss man halt selber in v$sqlarea nachsehen.
Für diesen Trigger braucht man SELECT-Rechte an v_$sqlarea.

DELETE temp_ts_problems;
CREATE OR REPLACE TRIGGER ora_1652_trig
 AFTER SERVERERROR ON DATABASE
DECLARE
    v_sql_text VARCHAR2(1000);
    v_user     VARCHAR2(30);
BEGIN
 IF is_servererror(1652) then
    v_user := ora_login_user;
    INSERT into temp_ts_problems
    SELECT ora_login_user||', '||
          TO_CHAR(systimestamp, 'dd.mm.rr hh24:mi:ssxff')||CHR(10)||sqltext
    FROM (SELECT SUBSTR(sql_text, 1,200) sqltext
          FROM   v$sqlarea
          WHERE  parsing_schema_name = v_user
          ORDER BY last_load_time DESC)
    WHERE rownum = 1;
  END IF;
END;
/

Wenn sich viele Benutzer unter dem gleichen Usernamen auf der Datenbank tummeln, muss man natürlich die Anzahl der Zeilen erhöhen (z. B. WHERE rownum < 20) und mit WHERE-Bedingungen arbeiten, um das problematische Statement aus dem Wust von recursive calls und ähnlichem herauszufiltern. Hier soll nur die Basisfunktionalität gezeigt werden.

Der User Scott reizt wieder den Temp Tablespace aus (die Fehlermeldungen spar ich mir jetzt).

conn scott/tiger
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c ORDER BY 1;
CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id);

Diesmal finden sich beide Statements in der Logging-Tabelle.

conn system/sys
SELECT * FROM temp_ts_problems;
ERRMSG
----------------------------------------------------------------------------------------
SCOTT, 02.02.15 00:36:02,417000
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c  ORDER BY 1
SCOTT, 02.02.15 00:36:30,887000
CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id)

Statt eine Tabelle mit den Fehlermeldungen zu füllen, kann man natürlich auch Textfiles mit UTL_FILE erzeugen, sich über UTL_MAIL eine E-mail schicken lassen oder die Fehlermeldungen über DBMS_SYSTEM.KSDWRT gleich ins Alertlog schreiben.

Alternativlösung

Für alle, die Trigger hassen, und viel Platz haben, gibt es noch eine Alternative: Tracing.

conn sys/<pwd> as sysdba
ALTER SYSTEM SET EVENTS '1652 trace name errorstack level 12';

conn scott/tiger
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c ORDER BY 1;

Wo das Tracefile steckt, bekommt man über die View v$diag_alert_ext am schnellsten heraus.

conn / as sysdba
-- Die 0 ist hier wichtig, sonst bekommt man nicht die Zeilen mit dem Tracefile
col host_id for a10
col datum for a18
col module_id for a15
col message_text for a100
SELECT TO_CHAR(originating_timestamp, 'dd.mm.rr hh24:mi:ss') datum, host_id, module_id, message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-01652%';
DATUM              HOST_ID    MODULE_ID     MESSAGE_TEXT
------------------ ---------- ------------- -----------------------------------------------------------------------------
02.02.15 14:42:05  BLECHDEPP  SQL Developer Errors in file E:\ORACLE\diag\rdbms\o11g\o11g\trace\o11g_ora_4436.trc:
                                            ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden

Im riesigen Tracefile (> 10 MB) steht dann (Gott sei Dank ziemlich weit oben)

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)
----- Error Stack Dump -----
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden
----- Current SQL Statement for this session (sql_id=8agc1pg0us4mf) -----
select a.object_name
from all_objects a, all_objects b, all_objects c
order by object_name;

Zusätzlich wird noch ein Diagnostic Dump gestartet, der zusätzlich ca 1,2 MB belegt, insofern bevorzuge ich persönlich die Platz sparendere und flexiblere Lösung mit dem Trigger.

Fazit

AFTER SERVERERROR ON DATABASE-Trigger können wertvolle Dienste beim Troubleshooting leisten, wenn man sich nicht darauf verlässt, dass ein Code, die unter 10g noch funktioniert hat, auch unter 11g problemlos läuft und die Trigger vorsichtig und gezielt für einzelne Datenbankfehler einsetzt.

Falls Sie Fragen zur Umsetzung haben, hilft unser Opens window for sending emailConsulting Team Ihnen gerne weiter.

 

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.