Site Logo

IzzySoft


› › Logminer
Das inoffizielle Android-HandbuchDas inoffizielle Android-Handbuch
Buy at Amazon for 2,94 €
Das inoffizielle Android-System-Handbuch (Professional Series)Das inoffizielle Android-System-Handbuch (Professional Series)
Buy at Amazon for 3,63 €
Die besten Android-Apps (Action)Die besten Android-Apps (Action)
Buy at Amazon for 3,93 €
As of 2025-03-19 16:32
prices & availability might be subject to change.

Oracle Logminer

Einleitung und Zweck

Durch die Verwendung von LogMiner ist es dem Administrator möglich, die Auslastung der Datenbank zu überwachen und fehlerhafte Änderungen rückgängig zu machen. Dies kann z.B. im Falle der versehentlichen Löschung eines Objektes ein unvollständiges Recovery (RECOVER UNTIL …) ersparen und somit helfen, Daten zu “retten”.

Für diesen Zweck wertet LogMiner die ReDo-Log-Dateien aus. Diese Auswertung kann sowohl in der zu analysierenden Datenbank oder, wenn deren Online-Leistung nicht beeinflusst werden soll, auch offline in einer anderen Datenbank erfolgen.

Inhalt

Installation

Die für die Installation notwendigen Dateien werden vom Oracle Installer mitgeliefert. Um LogMiner einsetzen zu können, müssen die dafür notwendigen Prozeduren in der Datenbank erzeugt werden. Dies geschieht durch die Ausführung zweier Scripte (erfolgt im allgemeinen bereits durch catproc.sql bei der Datenbank-Erstellung):

sql> ?/rdbms/admin/dbmslogmnr.sql
sql> ?/rdbms/admin/dbmslogmnrd.sql

Außer den notwendigen Prozeduren (dbms_logmnr und dmbs_logmnr_d) werden folgende vier v$-Tabellen erzeugt:

V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS

Verwendung

Erstellung eines LogMiner Dictionary

Vor der Ausführung muss in der Parameterdatei der ausführenden Instanz (init.ora) der Parameter UTL_FILE_DIR auf das Verzeichnis eingestellt werden, in das LogMiner seine Dictionary-Datei speichern soll, z. B.:

UTL_FILE_DIR=/oracle/logs

Nach Beendigung der Arbeit sollte dieser Parameter wieder auskommentiert werden (Datensicherheit). Jetzt sollte ein LogMiner Dictionary angelegt werden. Dies ist zwar nicht zwingend notwendig – erleichtert jedoch das Verständnis der Ausgaben von LogMiner, wie folgendes aus (2) entnommene Beispiel zeigt:

INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);
INSERT INTO Object#2581(col#1, col#2) VALUES (hextoraw('4a6f686e20446f65'), hextoraw('c306'));

Die erste Zeile zeigt das SQL Statement an, wie es unter Verwendung eines LogMiner Dictionary angezeigt würde. Darauf folgt das gleiche Statement ohne Verwendung desselben.

Um also nun das LogMiner Dictionary anzulegen, sind (nach Definition des UTL_FILE_DIR) folgende SQL Befehle abzusetzen:

sql> ?/rdbms/admin/utlfile.sql
sql> sys.dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/oracle/logs');

Das Dictionary muss logischerweise auf der Instanz angelegt werden, deren Log Dateien analysiert werden sollen: es müssen ja schließlich die Object IDs den jeweiligen Objekt Namen zugeordnet werden, und diese Zuordnung erfolgt über das Data Dictionary der Datenbank, aus der die Daten stammen.

Da die LogMiner Packages dem Schema SYS gehören, sollte sich entweder als SYS angemeldet, oder aber das SYS Prefix jedem Aufruf der Prozeduren vorangestellt werden.

Es ist durchaus sinnvoll, erzeugte Dictionary-Dateien aufzuheben: gelöschte Objekte sind im aktuellen Dictionary nicht mehr vorhanden. Soll also z.B. eine gelöschte Tabelle wieder hergestellt werden, finden sich die Objekt-Informationen nur in einer Dictionary-Datei, die erstellt wurde, als das Objekt noch existierte. In diesem Fall bietet sich selbstredend die Verwendung einer entsprechenden älteren Dictionary-Datei an.

Analyse der ReDo-Logs

ReDo Logs für die Analyse spezifizieren

Dies geschieht mittels der Prozedur DBMS_LOGMNR.ADD_LOGFILE. Folgende Optionen sind bei der Erstellung einer Dateiliste verwendbar:

Option Beschreibung
NEW neue Liste erstellen
ADDFILE Datei der aktuellen Liste hinzufügen
REMOVEFILE Datei aus der Liste entfernen

Im folgenden ein kurzes Beispiel, welches zwei ReDo Log Dateien zur Analyse heran zieht:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/oracle/logs/log1.f',
  OPTIONS => dbms_logmnr.NEW
);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/oracle/logs/log2.f',
  OPTIONS => dbms_logmnr.ADDFILE
);

Soll die zweite Datei nun wieder aus der Liste entfernt werden:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/oracle/logs/log2.f',
  OPTIONS => dbms_logmnr.REMOVEFILE
);

Nun ist alles vorbereitet, und die geladenen Daten können analysiert werden.

Ausführen der Analyse

Der einfachste Analyse-Aufruf analysiert alle Daten der angegebenen ReDo-Log-Dateien:

EXECUTE DBMS_LOGMNR.START_LOGMNR(
  DICTFILENAME => '/oracle/dictionary.ora'
);

Wenn bekannt ist, welcher Zeitraum zu analysieren ist, lässt sich die Datenmenge einschränken. Folgende Optionen stellt die Prozedur DBMS_LOGMNR.START_LOGMNR zur Verfügung:

Option Beschreibung
STARTSCN Systen Change Number, ab der begonnen werden soll
ENDSCN Letzte zu bearbeitende SCN
STARTTIME Startzeit des zu analysierenden Zeitraumes
ENDTIME Ende des zu analysierenden Zeitraums
DICTFILENAME Name der Dictionary Datei

Hier könnte ein Aufruf mit Datums/Zeitangabe folgendermaßen aussehen:

EXECUTE DBMS_LOGMNR.START_LOGMNR(
  DICTFILENAME => '/oracle/dictionary.ora',
  STARTTIME => to_date('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
  ENDTIME => to_date('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS')
);

Alternativ, unter Verwendung der System Change Numbers:

EXECUTE DBMS_LOGMNR.START_LOGMNR(
  DICTFILENAME => '/oracle/dictionary.ora',
  STARTSCN => 100,
  ENDSCN => 150
);

Auswertung der analysierten Daten

Hierzu ist die Tabelle V$LOGMNR_CONTENTS heran zu ziehen. Interessant sind hier insbesondere folgende Spalten:

Spalte Beschreibung
SQL_REDO ausgeführtes SQL (z.B. für Recovery)
SQL_UNDO Rückgängig-machen des ausgeführten SQL
SCN zur Operation (SQL_REDO) zugehörige System Change Number
TIMESTAMP Zeitpunkt der Ausführung des SQL_REDO
TABLE_SPACE TableSpace, auf dem die Operation ausgeführt wurde
SEG_NAME Name des Objektes (z. B. der Tabelle)
OPERATION Art der SQL Anweisung (z.B. DELETE, INSERT; ein DROP TABLE würde hier allerdings als DELETE aufgeführt!)
USERNAME Wer war's?

Für eine Übersicht aller Spalten sei auf (3) verwiesen. Auch ein DESC V$LOGMNR_CONTENTS hilft hier weiter.

Wiederherstellung einer gelöschten Tabelle

ReDo/UnDo lokalisieren

Folgende Informationen sind aus (4) entnommen.

Zur Lokalisierung greifen wir auf die bereits oben beschriebene LogMiner Tabelle V$LOGMNR_CONTENTS zu:

SQL> column seg_name format a15 trunc
SQL> select seg_name, operation, scn, count(*)
 2  from v$logmnr_contents
 2  where operation != 'INTERNAL'
 3  group by seg_name, operation, scn
 4  order by scn;

Die Ausgabe könnte nun wie folgt aussehen:

SEG_NAME        OPERATION                               SCN   COUNT(*)
--------------- -------------------------------- ---------- ----------
                COMMIT                              5012064          1
COL$            DELETE                              5012065          3
OBJ$            DELETE                              5012065          1
TAB$            DELETE                              5012065          1
                START                               5012065          1
SEG$            UPDATE                              5012065          1
                COMMIT                              5012066          1
SEG$            DELETE                              5012067          1
UET$            DELETE                              5012067          1
FET$            INSERT                              5012067          1
                START                               5012067          1
TSQ$            UPDATE                              5012067          1
                COMMIT                              5012068          1

13 rows selected.

Ein DROP TABLE erzeugt DELETE Operationen auf COL$, OBJ$ und TAB$. Da das Lesen der benötigten Daten aus V$LOGMNR_CONTENTS physikalische Datenzugriffe erzeugt, ist es gerade bei umfangreichen eingebundenen Log Dateien sinnvoll, zunächst die benötigten Daten in eine temporäre Tabelle zu importieren:

SQL> create table temp
 2  as select * from v$logmnr_contents
 3  where operation = 'DELETE'
 4  and seg_name in ('COL$','OBJ$','TAB$');

Für alle weiteren Operationen können wir nun auf die Tabelle temp zugreifen - temp ließe sich jederzeit durch V$LOGMNR_CONTENTS ersetzen, was die Syntax betrifft; wir verwenden allerdings die Tabelle, wie oben beschrieben, aus Performance-Gründen.

Jetzt gilt es heraus zu finden, ob unser DROP TABLE in den analysierten ReDo-Log-Dateien enthalten ist. Gibt es keine einzelne SCN mit DELETE Operation auf COL$, OBJ$ und TAB$, müssen ggf. weitere Log-Dateien zur Analyse heran gezogen werden:

SQL> select seg_name, operation, scn, count(*) from temp
 2  where operation != 'INTERNAL'
 3  group by seg_name, operation, scn
 4  order by scn;

SEG_NAME        OPERATION                               SCN  COUNT(*)
--------------- -------------------------------- ---------- ----------
COL$            DELETE                              5012065          3
OBJ$            DELETE                              5012065          1
TAB$            DELETE                              5012065          1

Sollte die Ausgabe der obigen Abfrage zu lang (und damit unübersichtlich) werden, hilft folgende Abfrage weiter:

SQL> select scn from v$logmnr_contents
 2  where operation = 'DELETE' and seg_name = 'COL$'
 3  intersect
 4  select scn from v$logmnr_contents
 5  where operation = 'DELETE' and seg_name = 'OBJ$'
 6  intersect
 7  select scn from v$logmnr_contents
 8  where operation = 'DELETE' and seg_name = 'TAB$';

       SCN
----------
   5012065

Ergibt diese Abfrage kein Ergebnis, benötigen wir weitere Log-Dateien zur Analyse: unser gesuchtes DROP TABLE ist hier nicht enthalten. In unserem Beispiel sind wir allerdings fündig geworden: wir haben die SCN für unsere Löschaktion gefunden. Sollten mehrere Tabellen im analysierten Zeitraum gelöscht worden sein, können hier natürlich auch mehrere SCNs gefunden werden!

Um nun unsere Daten eindeutig wieder finden zu können, müssen wir eine Dictionary Datei verwenden. Diese sollte zu einem Zeitpunkt erstellt worden sein, als unsere Tabelle noch existierte – anderenfalls bleibt uns nur die “Point-in-Time” Recovery, da wir weder die Tabellen- noch die Spaltennamen haben. Fortfahren können wir nun, indem wir die ReDo- bzw. UnDo- Informationen verifizieren:

SQL> select sql_redo from temp
 2  where scn = 5012065 and seg_name = 'TAB$';

SQL_REDO
--------------------------------------------------------------------------------
delete from UNKNOWN.Objn:4 where Col[1] = HEXTORAW('c3021f50') and Col[2] = HEXT
ORAW('c102') and Col[3] = HEXTORAW('c103') and Col[4] = HEXTORAW('c2034e') and C
ol[5] IS NULL and Col[6] IS NULL and Col[7] = HEXTORAW('c104') and Col[8] IS NUL
L and Col[9] = HEXTORAW('c10b') and Col[10] = HEXTORAW('c129') and Col[11] = HEX
TORAW('c102') and Col[12] = HEXTORAW('c20338') and Col[13] = HEXTORAW('c102') an
d Col[14] = HEXTORAW('2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d
2d2d2d') and Col[15] IS NULL and Col[16] IS NULL and Col[17] IS NULL and Col[18]
IS NULL and Col[19] IS NULL and Col[20] IS NULL and Col[21] IS NULL and Col[22]
IS NULL and Col[23] IS NULL and Col[24] IS NULL and Col[25] IS NULL and Col[26]
IS NULL and Col[27] = HEXTORAW('c104') and Col[28] = HEXTORAW('c104') and Col[2
9] = HEXTORAW('80') and Col[30] = HEXTORAW('80') and Col[31] = HEXTORAW('c20441'
) and Col[32] IS NULL and Col[33] = HEXTORAW('80') and ROWID = 'AAAAACAABAAAH1ZA
AA';

In unserem Beispiel haben wir das Pech, dass unsere Dictionary Datei erst nach dem Löschen der Tabelle erzeugt wurde. Für ein “Point-in-Time” Recovery erhalten wir unser TimeStamp wie folgt:

SQL> select distinct timestamp from temp where scn = 5012065;

TIMESTAMP
--------------------
2000-JAN-18 12:36:23

Dies ist der Zeitpunkt, zu dem das DROP TABLE Statement festgeschrieben wurde. Ein “Point-in-Time” Recovery sollte also bis kurz vor diesem Zeitpunkt erfolgen, da wir die Tabelle ja behalten wollen.

Den Namen unserer gelöschten Tabelle können wir dennoch erhalten:

SQL> select sql_redo from temp
 2  where scn = 5012065 and seg_name = 'OBJ$';

SQL_REDO
--------------------------------------------------------------------------------
delete from SYS.OBJ$ where OBJ# = 13079 and DATAOBJ# = 13079 and <b>OWNER# = 38</b> and
 <b>NAME = 'TAB1'</b> and NAMESPACE = 1 and SUBNAME IS NULL and TYPE# = 2 and CTIME = T
O_DATE('18-JAN-2000 12:25:28', 'DD-MON-YYYY HH24:MI:SS') and MTIME = TO_DATE('18
-JAN-2000 12:25:28', 'DD-MON-YYYY HH24:MI:SS') and STIME = TO_DATE('18-JAN-2000
12:25:28', 'DD-MON-YYYY HH24:MI:SS') and STATUS = 1 and REMOTEOWNER IS NULL and
LINKNAME IS NULL and FLAGS = 0 and ROWID = 'AAAAASAABAAAGXkAAA';

Anhand der OWNER# läßt sich auch der Besitzer ermitteln:

SQL> select username from dba_users where user_id = 38;

USERNAME
------------------------------
TEST

UnDo Informationen ausfiltern

Hierzu fand sich leider bei Oracle (MetaLink) keinerlei Information. Es sollte aber eigentlich recht einfach mittels des folgenden Befehles möglich sein – vorausgesetzt, wir verfügen über eine Dictionary Datei, die alle nötigen Informationen enthält (siehe oben):

SELECT sql_undo
  FROM v$logmnr_contents
 WHERE operation = 'DELETE'
   AND seg_type_name='TABLE'
   AND (seg_name = 'TABLE_SPACE_1'
    OR  seg_name = 'TABLE_SPACE_2')
 ORDER BY scn;

Erstellen von Zugriffsstatistiken

Mittels LogMiner lassen sich auch Statistiken über die Benutzung von Objekten erstellen:

SQL> SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits
 2 FROM V$LOGMNR_CONTENTS
 3 WHERE SEG_NAME NOT LIKE '%$'
 4 GROUP BY SEG_OWNER, SEG_NAME;

SEG_OWNER          SEG_NAME          Hits
---------          --------          ----
CUST               ACCOUNT            384
SCOTT              EMP                 12
SYS                DONOR               12
UNIV               DONOR              234
UNIV               EXECDONOR          325
UNIV               MEGADONOR           32

LogMiner Session beenden

Als letztes ist die LogMiner Session zu beenden, um die Ressourcen wieder frei zu geben:

execute dbms_logmnr.end_logmnr();

Anhang

Bugs

In der Version 8.1.7 gibt es einen Bug, der beim Aufbau der Dictionary-Datei zu folgender Fehlermeldung führen kann:

ERROR at line 1:
    ORA-01330: problem loading a required build table
    ORA-06512: at "SYS.DBMS_LOGMNR_D", line 1755
    ORA-06512: at line 1

Die Ursache für diesen Fehler ist ein zu kleines Array für die Speicherung der Spaltenbeschreibung. Der Fehler läßt sich daher, sollte er auftreten, durch die Änderung der Zeile

TYPE col_desc_array IS VARRAY(513) OF col_description;

in

TYPE col_desc_array IS VARRAY(2000) OF col_description;

beheben. (5)

Quellenverzeichnis

(1) Heidi Thorpe: Oracle 8i Tuning und Administration, 2001 Addison-Wesley Verlag, München, ISBN 3-8273-1878-5 (Seiten 183-185)
(2) Oracle MetaLink Document 111886.1: How to Setup *LogMiner*
(3) Oracle MetaLink Document 62508.1: The Oracle8i *LogMiner* Utility
(4) Oracle MetaLink Document 93370.1: How to locate SCN/Time of DROP TABLE using *LogMiner*
(5) Uwe Herrmann, Dierk Lenz, Günter Unbescheid, Johannes Ahrends: Oracle 9i für den DBA, 2002 Addison-Wesley Verlag, München, ISBN 3-8273-1559-X (Seite 455)

Weiterführende Literatur

2020-11-18