Ein Arbeitskollege hat mich auf das Flashback-SQL-Query bei Oracle Datenbanken aufmerksam gemacht. Mittels einer einfachen SQL Abfrage ist es möglich, auf alte Tabellenzustände außerhalb von Transaktionen zuzugreifen. So ist es zum Beispiel möglich, nach einer fehlerhaft formulierten UPDATE-Operation trotz Commit auf den vorherigen Tabellenzustand zurückwechseln zu können. Die Lösung ist die Verwendung des Flashback-Querys:

SELECT * FROM timemachine AS OF timestamp systimestamp - '5' minute;

Beispiel Tutorial

Ich habe beispielhaft die Tabelle timemachine angelegt:

CREATE TABLE timemachine (name VARCHAR(255), aenderung DATE);

INSERT INTO timemachine (name,aenderung) VALUES ('Eintrag 1', sysdate);
INSERT INTO timemachine (name,aenderung) VALUES ('Eintrag 2', sysdate);

SELECT * FROM timemachine;

Weitere Änderungen I

Diese beiden Einträge habe ich nach einigen Minuten geändert:

UPDATE timemachine SET name = 'Eintrag 1A', aenderung = SYSDATE WHERE name = 'Eintrag 1';
UPDATE timemachine SET name = 'Eintrag 2A', aenderung = SYSDATE WHERE name = 'Eintrag 2';

Ergebnis:

Weitere Änderungen II

Das habe ich nochmal fortgeführt:

UPDATE timemachine SET name = 'Eintrag 1B', aenderung = SYSDATE WHERE name = 'Eintrag 1A';
UPDATE timemachine SET name = 'Eintrag 2B', aenderung = SYSDATE WHERE name = 'Eintrag 2A';

Nun sind in der Tabelle timemachine die vorhandenen Einträge mehrfach überschrieben. Da nach dem Commit hier kein Rollback die alten Daten zurückholen kann, bleibt nur noch die Möglichkeit über das Flashback-Query. Wie bereits oben erwähnt, kommen Sie an die alten Daten mit folgender Abfrage dran:

SELECT * FROM timemachine AS OF timestamp systimestamp - '5' minute;

Sie können die genannte Anzahl Minuten in die Vergangenheit springen und den alten Tabellenzustand lesen.

Gesamte History lesen

Wie oben beschrieben können Sie mittels dem Flashback-Query im Minutenbereich in die Vergangenheit zu einem speziellen Zustand zurückspringen.  Es ist auch möglich, alle Änderungen der näheren Vergangenheit auflisten zu lassen. Dies klappt mit folgendem SQL:

select versions_xid, versions_starttime, aenderung, name
from timemachine versions between scn minvalue and maxvalue
where name LIKE 'Eintrag 1%'

Das SQL liest die Änderungsversion und das Versionsdatum zu jedem Datensatz aus versions aus. Das Ergebnis ist wie folgt:

Nun kann für bestimmte Datensätze eine alte Version mittels neuem SQL-Query geladen und der aktuelle Satz überschrieben werden. Müssen alle Datensätze auf eine bestimmte Version zurückgesetzt werden, lohnt es sich, die gesamte Tabelle in die Vergangenheit zu versetzen.

Ganze Tabelle zurücksetzen

Um ganze Tabellen in die Vergangenheit zu versetzen, muss das Flag ROW MOVEMENT aktiv sein:

ALTER TABLE timemachine ENABLE ROW MOVEMENT;

Um zu einem beliebigen Zeitpunkt in die Vergangenheit zu wechseln, müssen Sie folgendes SQL ausführen:

FLASHBACK TABLE timemachine TO timestamp to_timestamp ('24.07.11 11:17:00', 'dd.mm.yy hh24:mi:ss'); 

Es werden nicht nur Daten, sondern auch die Indizies zurückgesetzt. Das zurückholen von gelöschter Tabellen (über DROP ohne PURGE) ist so auch möglich.

Einschränkung Undo-Tablespace

Leider kann nicht unendlich lange in die Vergangenheit zurückgesprungen werden. Wenn Sie das Versions-SQL im 5-Minutentakt ausführen, werden Sie merken, dass die ältesten Einträge irgendwann nicht mehr auftauchen. Standardgemäß kann man bei Oracle 10g 900 Sekunden (15 Minuten) in die Vergangenheit zurückschauen, sofern der Undo-Tablespace dies zulässt.  Dies kann über den Startparameter UNDO_RETENTION geändert werden. Jedoch ist es bei einer Datenbank im Volllastbetrieb nicht sinnvoll, alle historischen Änderungen ewig zu speichern, da auch der größte Undo-Tablespace irgendwann voll läuft. Eine optimale Berechnung der Größe des Tablespaces finden Sie hier.

Fazit

Die Funktion des Flashback-Querys ist sehr gut, um schnell Fehler bei Datenmanipulation zu korrigieren. Jedoch ist dies m.M. kein Mittel für einen produktiven Einsatz, sondern nur für Entwicklungsdatenbanken und dort gemachte Fehler sinnvoll einsetzbar. Aufgrund der Beschränkung des Undo-Tablespaces kann eine Produktionsdatenbank mit hoher Auslastung nicht sicher Daten aus der Vergangenheit zurückholen.

Oracle Flashback
Markiert in: