Logminer_unter_9i_neu

Einführung Logminer 9i
Zielgruppe:
Alle, die schon immer wissen wollten was das ist, und wie man damit umgeht.
(Basisinformationen, nicht alle Möglichkeiten werden aufgezeigt)
Was ist Logminer:
Mit dem Logminer hat man die Möglichkeit DDL und DML Statements, die auf der Datenbank
ausgeführt wurden, aus den Logfiles (Redolog,Archivelog) zu extrahieren, und die Aktion
gegebenenfalls mit den entsprechenden Undo-Statements rückgängig zu machen.
1) Voraussetzungen auf der Datenbank deren Logs analysiert werden sollen
Damit bei der Analyse der Logs auch ein vernünftiges Ergebnis zu erwarten ist, sollte auf der
Datenbank ein „supplemental Logging“ eingeschaltet sein.
Bei 9.0.1.X ist dieses Logging automatisch eingeschaltet.
Bei 9.2.0.X sollte man dies mit folgendem Befehl aktivieren.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Dieses „Basis“ Logging bewirkt keine nennenswerte Performanceeinbuße.
Damit werden zusätzliche Informationen geloggt die notwendig sind für:
o
Support for chained rows, and migrated rows.
o
Support for direct-path inserts (also requires that ARCHIVELOG mode be enabled).
o
Extracting the data dictionary into the redo logs.
o
DDL tracking.
o
LONG
and LOB datatypes are supported only if supplemental logging is enabled. (9.2)
2) Komponenten des Logminers
- Prozeduren
Der Logminer besteht im wesentlichen aus Prozeduren , die in der Datenbank unter dem Schema
SYS liegen.
- Dictionary (Abbild der Strukturen der Quell-Datenbank)
Damit das Ergebnis aussagekräftig ist benötigt der Logminer ein Dictionary. Ohne dieses D. würden
für Objekte nur die Objekt_ID, und für die Daten nur Hexwerte ausgegeben werden.
Bsp:
INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);
LogMiner will display:
insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),
hextoraw('c306'));"
Note:
It is important to understand that the LogMiner internal dictionary is not the same as the
LogMiner dictionary contained in a flat file or in redo logs. LogMiner does update its internal
dictionary, but it does not update the dictionary that is contained in a flat file or in redo logs.
All databases should employ an alternate tablespace for LogMiner tables. By default all
LogMiner tables are created to use the SYSTEM tablespace. Use the
DBMS_LOGMNR_D.SET_TABLESPACE routine to re-create all LogMiner tables in an alternate
tablespace. For example, the following statement will re-create all LogMiner tables to use the
logmnrts$ tablespace:
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');
Unter 9i gibt es 3 Möglichkeiten ein Dictionary (Quell-Datenbank) zu erstellen bzw. zu benutzen.
- Using the Online Catalog
- Extracting the Dictionary to a Flat File
- Extracting a Dictionary to the Redo Logs
Das Dictionary muß natürlich auf der Datenbank erstellt werden auf der die Logs erzeugt wurden.
Mit dem Dictionary als Flat File oder innerhalb Redo Logs hat man die Möglichkeit die Analyse auf
einer anderen Instanz oder Datenbank durchzuführen. Voraussetzung hierfür ist die gleiche Hardware
Plattform sowie der gleiche Database Characterset.
Erstellen des Dictionary als File
Der Pfad unter dem das Dictionary File erstellt werden soll, muss mit UTL_FILE_DIR gesetzt werden.
For example, to set UTL_FILE_DIR to use /oracle/database as the directory where the dictionary file is
placed, enter the following in the init.ora file:
UTL_FILE_DIR = /oracle/database
Remember that for the changes to the init.ora file to take effect, you must stop and restart the
database.
Danach wird das File erstellt:
SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/oracle/database/', OPTIONS =>
DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
Beim Aufruf wird der Prozedur der Filename und der Pfad mitgegeben.
Hinweis:
Während des Erstellens eines Dictionary Files sind DDL Operationen zulässig. Somit besteht die
Möglichkeit dass das Dictionary inkonsistent ist.
Erstellen des Dictionary in Redo Logs
Hierbei wird das Dictionary in die aktuellen Redo Logs gespeichert.
SQL> EXECUTE DBMS_LOGMNR_D.BUILD
(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Wenn man den Logminer ausführt, muß man hierbei außer den zu analysierenden Logs, auch die
Logs mit angeben, die das Dictionary beinhalten. Die findet man mit:
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
Hinweis:
Während des Erstellens eines Dictionarys in Redo Logs, sind keine DDL Operationen möglich. Dies
gewährleistet ein konsistentes Dictionary.
Das Online Dictionary
Das Online Dictionary braucht nicht erstellt werden, denn es ist in Form von Datenbanktabellen im
SYS Schema schon vorhanden. Benutzt der Logminer dieses Dictionary, so geht das nur Lokal (auf
der Datenbank von der die Logs stammen) und die Datenbank muss geöffnet sein.
3) Ablauf einer Logminer Session
Beispiel: Analysieren der Logfiles auf der gleichen Datenbank auf der die Logfiles erzeugt wurden mit
Hilfe eines Dictionary-Files.
Nachdem, wie oben beschrieben, das Dictionary erstellt wurde, wird nun dem Logminer eine Liste der
zu analysierenden Logs mitgeteilt:
Logfileliste
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 2 LOGFILENAME => '/oracle/logs/log1.f', 3 OPTIONS => DBMS_LOGMNR.NEW);
Für jedes weitere Log:
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 2 LOGFILENAME=>'/oracle/logs/log2.f');
Um ein Log aus der Konfiguration zu entfernen geht man so vor:
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 2 LOGFILENAME => '/oracle/logs/log2.f', 3 OPTIONS => DBMS_LOGMNR.REMOVEFILE);
Starten des Logminers
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( 2 DICTFILENAME =>'/oracle/database/dictionary.ora');
Hierbei werden die Logs analysiert und die Ergebnisse in die View V$LOGMNR_CONTENTS
eingetragen.
Das Starten des Logminers kann jederzeit, um die View erneut mit Daten zu füllen, mit andern
Optionen widerholt werden. Die konfigurierte Logfile-Liste bleibt hiervon unberührt.
Abfragen der Ergebnisse
Das Abfragen der View V$LOGMNR_CONTENTS hat ein sequenzielles Lesen der Logfiles zur Folge
So könnte eine Abfrage und das dazugehörige Ergebnis aussehen:
SQL> SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
2 WHERE USERNAME = 'SEPP' AND SEG_NAME = 'salary';
For both the SQL_REDO and SQL_UNDO columns, two rows are returned (the
format of the data display will be different on your screen). You discover
that joedevo requested two operations: he deleted his old salary and then
inserted a new, higher salary. You now have the data necessary to undo this
operation.
SQL_REDO
-------delete * from SALARY
SAL)
where EMPNO = 12345
and ROWID = 'AAABOOAABAAEPCABA';
SQL_UNDO
-------insert into SALARY(NAME, EMPNO,
insert into SALARY(NAME, EMPNO, SAL)
values('JOEDEVO',12345, 2500)
delete * from SALARY
where EMPNO = 12345
and ROWID = 'AAABOOAABAAEPCABA';
values ('JOEDEVO', 12345, 500)
2 rows selected
Ende einer Logminer Session
To properly end a LogMiner session, use the DBMS_LOGMNR.END_LOGMNR procedure, as
follows:
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
This procedure closes all the redo logs and allows all the database and system resources
allocated by LogMiner to be released.
If this procedure is not executed, LogMiner retains all its allocated resources until the end of
the Oracle session in which it was invoked. It is particularly important to use this procedure to
end LogMiner if either the DDL_DICT_TRACKING option or the DICT_FROM_REDO_LOGS
option was used.
4) Optionen und Views
The following list is a summary of LogMiner settings that you can specify with the OPTIONS
parameter.

DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG

DBMS_LOGMNR.DICT_FROM_REDO_LOGS

DBMS_LOGMNR.COMMITTED_DATA_ONLY

DBMS_LOGMNR.SKIP_CORRUPTION

DBMS_LOGMNR.DDL_DICT_TRACKING

DBMS_LOGMNR.NEW, DBMS_LOGMNR.ADDFILE, and

DBMS_LOGMNR.REMOVEFILE

DBMS_LOGMNR.NO_SQL_DELIMITER

DBMS_LOGMNR.PRINT_PRETTY_SQL

DBMS_LOGMNR.CONTINUOUS_MINE
LogMiner information is contained in the following views. You can use SQL to query them as you
would any other view.
V$LOGMNR_CONTENTS
Shows changes made to user and table information.
V$LOGMNR_DICTIONARY
Shows information about the LogMiner dictionary file, provided the dictionary was created using the
STORE_IN_FLAT_FILE option. The information shown includes the database name and status
information.
V$LOGMNR_LOGS
Shows information about specified redo logs. There is one row for each redo log.
V$LOGMNR_PARAMETERS
Shows information about optional LogMiner parameters, including starting and ending
system change numbers (SCNs) and starting and ending times.
5) DDL_ DICT_TRACKING
Bei der Option DDL_ DICT_TRACKING wird jede DDL Operation in das interne Logminer Dictionary
eingepflegt. Somit kennt der Logminer den Zustand des Dictionarys vor und nach der DDL Operation.
- The DDL_DICT_TRACKING option is not valid with the DICT_FROM_ONLINE_CATALOG option.
- The DDL_DICT_TRACKING option requires that the database be open.
6) Beispiele
Beispiel 1: Ohne DDL_DICT_TRACKING
- aktuelles Dictionary
- Die Inhalte des zu analysierende Logs liegen in der Zeit vor dem erstellen des Dictionarys
Start Logminer
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME
=>'/svc/app/oracle/admin/envop1/utl_file_dir/dictionary.ora_3');
Abfrage V$LOGMNR_CONTENTS
SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS where
SQL_REDO like '%TOM%';
OPERATION
-------------------------------SQL_REDO
-----------------------------------------------------------------------------------------------------------------------SQL_UNDO
---------------------------------------------------------------------------------------------------------------------- -INSERT
insert into "TOM"."TEST"("COL 1","COL 2","COL 3","COL 4") values
(HEXTORAW('53746566616e'),HEXTORAW('476c61756d'),HEXTOR
AW('c105'),HEXTORAW('426561'));
delete from "TOM"."TEST" where "COL 1" = HEXTORAW('53746566616e') and "COL 2" =
HEXTORAW('476c61756d') and "COL 3" = HEX
TORAW('c105') and "COL 4" = HEXTORAW('426561') and ROWID = 'AAAH+6AAJAAAAAsAAD';
DDL
ALTER TABLE TOM.TEST
ADD Ort VARCHAR2(20)
;
SELECT_FOR_UPDATE
select * from "TOM"."TEST" where ROWID = 'AAAH+6AAJAAAAAsAAA' for update;
UPDATE
update "TOM"."TEST" set "BERUF" = 'Oracle', "ORT" = 'Woellstadt' where "BERUF" = 'Maurer' and
"ORT" IS NULL and ROWID ='AAAH+6AAJAAAAAsAAA';
update "TOM"."TEST" set "BERUF" = 'Maurer', "ORT" = NULL where "BERUF" = 'Oracle' and "ORT" =
'Woellstadt' and ROWID = 'AAAH+6AAJAAAAAsAAA';
INSERT
insert into "TOM"."TEST"("VORNAME","NACHNAME","NR","BERUF","ORT") values
('Thomas','Tretter','5','Software','Dauborn');
delete from "TOM"."TEST" where "VORNAME" = 'Thomas' and "NACHNAME" = 'Tretter' and "NR" =
'5' and "BERUF" = 'Software' a
nd "ORT" = 'Dauborn' and ROWID = 'AAAH+6AAJAAAAAsAAE';
-------------------------------------------------------------------------------------------------------------------------------------Wie man sieht werden Spaltennamen und Inhalte nur brauchbar ausgegeben wenn das Dictionary
der Tabellenstruktur entspricht. Der Insert vor dem DDL Statement ist nicht ordentlich erkennbar.
Beispiel 2: Ohne DDL_DICT_TRACKING
Hierfür wird ein Dictionary benutzt das vor dem ersten Insert (siehe oben) erzeugt wurde, und den
Zustand der Tabelle vor dem DDL Statement wiederspiegelt.
Start Logminer
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>
'/svc/app/oracle/admin/envop1/utl_file_dir/dictionary.ora_2');
Abfrage V$LOGMNR_CONTENTS
SQL> select username,OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
where SQL_REDO like '%TOM%';
USERNAME
OPERATION
------------------------------ -------------------------------SQL_REDO
-----------------------------------------------------------------------------------------------------------------------SQL_UNDO
-----------------------------------------------------------------------------------------------------------------------INSERT
insert into "TOM"."TEST"("VORNAME","NACHNAME","NR","BERUF") values
('Stefan','Glaum','4','Bea');
delete from "TOM"."TEST" where "VORNAME" = 'Stefan' and "NACHNAME" = 'Glaum' and "NR" = '4'
and "BERUF" = 'Bea' and ROWID = 'AAAH+6AAJAAAAAsAAD';
DDL
ALTER TABLE TOM.TEST
ADD Ort VARCHAR2(20)
;
SELECT_FOR_UPDATE
select * from "TOM"."TEST" where ROWID = 'AAAH+6AAJAAAAAsAAA' for update;
UPDATE
update "TOM"."TEST" set "COL 4" = HEXTORAW('4f7261636c65'), "COL 5" =
HEXTORAW('576f656c6c7374616474') where "COL 4" = H
EXTORAW('4d6175726572') and "COL 5" IS NULL and ROWID = 'AAAH+6AAJAAAAAsAAA';
update "TOM"."TEST" set "COL 4" = HEXTORAW('4d6175726572'), "COL 5" = NULL where "COL 4"
= HEXTORAW('4f7261636c65') and
"COL 5" = HEXTORAW('576f656c6c7374616474') and ROWID = 'AAAH+6AAJAAAAAsAAA';
INSERT
insert into "TOM"."TEST"("COL 1","COL 2","COL 3","COL 4","COL 5") values
(HEXTORAW('54686f6d6173'),HEXTORAW('54726574746
572'),HEXTORAW('c106'),HEXTORAW('536f667477617265'),HEXTORAW('446175626f726e'));
delete from "TOM"."TEST" where "COL 1" = HEXTORAW('54686f6d6173') and "COL 2" =
HEXTORAW('54726574746572') and "COL 3" =
HEXTORAW('c106') and "COL 4" = HEXTORAW('536f667477617265') and "COL 5" =
HEXTORAW('446175626f726e') and ROWID = 'AAAH+6AAJAAAAAsAAE';
---------------------------------------------------------------------------------------------------------------------------------------Hier sieht man dass das Dictionary die Struktur der Tabelle vor dem DDL Statement wiederspiegelt.
Die Redo Statements werden nach der DDL Operation nicht mehr brauchbar dargestellt.
Beispiel 3: Mit DDL_DICT_TRACKING
Hierfür wird ein Dictionary benutzt das vor dem ersten Insert (siehe oben) erzeugt wurde, und den
Zustand der Tabelle vor dem DDL Statement wiederspiegelt.
Start Logminer
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>
'/svc/app/oracle/admin/envop1/utl_file_dir/dictionary.ora_2',options =>
dbms_logmnr.ddl_dict_tracking);
Abfrage V$LOGMNR_CONTENTS
SQL> select username,OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
where SQL_REDO like '%TOM%';
USERNAME
OPERATION
------------------------------ -------------------------------SQL_REDO
-------------------------------------------------------------------------------------------------------------- ---------SQL_UNDO
-----------------------------------------------------------------------------------------------------------------------INSERT
insert into "TOM"."TEST"("VORNAME","NACHNAME","NR","BERUF") values
('Stefan','Glaum','4','Bea');
delete from "TOM"."TEST" where "VORNAME" = 'Stefan' and "NACHNAME" = 'Glaum' and "NR" = '4'
and "BERUF" = 'Bea' and ROWID = 'AAAH+6AAJAAAAAsAAD';
DDL
ALTER TABLE TOM.TEST
ADD Ort VARCHAR2(20)
;
SELECT_FOR_UPDATE
select * from "TOM"."TEST" where ROWID = 'AAAH+6AAJAAAAAsAAA' for update;
UPDATE
update "TOM"."TEST" set "BERUF" = 'Oracle', "ORT" = 'Woellstadt' where "BERUF" = 'Maurer' and
"ORT" IS NULL and ROWID ='AAAH+6AAJAAAAAsAAA';
update "TOM"."TEST" set "BERUF" = 'Maurer', "ORT" = NULL where "BERUF" = 'Oracle' and "ORT" =
'Woellstadt' and ROWID = 'AAAH+6AAJAAAAAsAAA';
INSERT
insert into "TOM"."TEST"("VORNAME","NACHNAME","NR","BERUF","ORT") values
('Thomas','Tretter','5','Software','Dauborn');
delete from "TOM"."TEST" where "VORNAME" = 'Thomas' and "NACHNAME" = 'Tretter' and "NR" =
'5' and "BERUF" = 'Software' a
nd "ORT" = 'Dauborn' and ROWID = 'AAAH+6AAJAAAAAsAAE';
-------------------------------------------------------------------------------------------------------------------------Wie man sieht werden sowohl die Daten vor und nach dem DDL Statement brauchbar
wiedergegeben. Es ist die Kombination des „alten“ Dictionarys mit der Option DDL_DICT_TRACKING
die dafür sorgt dass der Logminer sein eigenes Dictionary (Logminer Tabellen) erstellt und pflegt um
nachfolgende DDL Statements zu berücksichtigen. Somit ist der Logminer in der Lage die Redo
Statements entsprechend auszugeben.
Dabei ist wichtig dass das Dictionary den „Zustand“ des ersten zu analysierenden Logs darstellt.
Aus diesem Grund sollte man regelmäßig Dictionarys erstellen, um im Fehlerfall über ein
entsprechendes Dictionary zu verfügen.