Flashback ist eine coole Sache. Wenn man auf einer Enterprise Edition (EE) arbeiten kann, schöpft man natürlich aus dem
Vollen:
- Flashback Query, Flashback expdp/exp
- Flashback Version Query
- Flashback Drop
- Flashback Transaction Query (wirklich nur EE?)
- Flashback Table (nur EE)
- Flashback Database (nur EE)
- Flashback Transaction (nur EE)
- Total Recall: Flashback Data Archive (ab 11g, natürlich nur EE und das auch nur als Option)
In der Standard Edition (SE) kann man leider nur die ersten 3 Features nutzen. Aber bereits diese können sehr wertvoll sein und sind in vielen Fällen völlig ausreichend.
Wie also nutzt man Flashback in der SE?
Bevor man überhaupt daran denken kann, Flashback zu nutzen, sollte man sicher gehen, dass das dafür Nötige konfiguriert ist.
Die Parameter UNDO RETENTION und RECYCLEBIN müssen entsprechend gesetzt sein und der UNDO-Tablespace muss über ausreichend Platz verfügen.
Will man Flashback Transaction Query nutzen, muss außerdem Supplemental Logging eingeschaltet sein. Aber dazu unten mehr.
UNDO RETENTION
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------
undo_retention integer 900
900 ist der Standardwert in Sekunden. Diese 15min sind kein wirklich beruhigendes Zeitpolster. Wir setzen den Wert also etwas etwas höher - z.B. auf 7200 (2 Stunden).
SQL> ALTER SYSTEM SET UNDO_RETENTION=7200;
Problem: Der UNDO_RETENTION-Wert ist nur das Ziel der DB und wird nicht garantiert. Es ist möglich bzw. in einer produktiven DB wahrscheinlich, dass durch DML zu viel UNDO benötigt wird und damit das tatsächlich nutzbare Zeitpolster schrumpft. In diesem Fall bekommt ein FLASHBACK QUERY möglicherweise einen "ORA-01555 snapshot too old...".
Wenn man das verhindern möchte, kann man zusätzlich die RETENTION GUARANTEE auf dem UNDO-Tablespace setzen.
Das wiederum zieht dann möglicherweise ein Folgeproblem nach sich:
Ist der UNDO aufgebraucht und weitere DML-Operationen laufen auf, werden diese Operationen mangels UNDO fehlschlagen.
Dagegen hilft dann natürlich nur ein ausreichend großer UNDO-Tablespace.
Wir sehen nach, ob die Garantie konfiguriert ist:
SQL> SELECT TABLESPACE_NAME, RETENTION FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO';
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS1 NOGUARANTEE
Und so verschafft man sich diese Garantie:
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
PAPIERKORB
Den Papierkorb/Recyclebin benötigt man für die Wiederherstellung versehentlich gelöschter Tabellen.
Ist der Papierkorb an?
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- -----
recyclebin string on
Wenn nicht, dann:
SQL> ALTER SYSTEM SET RECYCLEBIN='ON'
Flashback Query
Jemand setzt ein DELETE oder UPDATE mit der der falschen oder gar keiner WHERE-Bedingung ab und schon herrscht Panik. Sofort wird ein DB-Recovery verlangt.
Ein incomplete recovery ist natürlich selten eine gute Lösung für dieses Problem.
Sofern der faux pas nicht zu weit zurückliegt (UNDO RETENTION!), lassen sich die korrekten Daten per FLASHBACK QUERY wieder hervorzaubern.
Da die Uhr tickt und der Ablauf UNDO RETENTION wie ein Damokleschwert über dem unglücklichen User schwebt, sichert man am besten den Datenbestand per CTAS in eine Tabelle.
SQL> CREATE TABLE jobs_rettung AS (SELECT * FROM jobs AS OF TIMESTAMP to_timestamp('10.02.2013 13:00','dd.mm.yyyy hh24:mi:ss'));
Von hier aus kann man die Daten in Ruhe wieder reparieren.
Eine andere Methode, den vergangenen Augenblick zu sichern bieten expdp und das alte exp.
Mit dem Parameter FLASHBACK_TIME/FLASHBACK_SCN weist man expdp/exp an, die Vergangenheit in ein dump-file zu pressen.
expdp system/@orcl dumpfile=jobs_flashback.dmp logfile=jobs_flashback_exp.log directory=expdp schemas=hr flashback_time='2013-02-10-13:00:00'
ODER:
expdp system/@orcl dumpfile=jobs_flashback.dmp logfile=jobs_flashback_exp.log directory=expdp schemas=hr flashback_scn='3416833'
Flashback Versions Query
Der User, der das verhängnisvolle DELETE/UPDATE abgesetzt hat, weiß unter Umständen gar nicht, wann genau das war. Er weiß vielleicht auch nur die ungefähre Zeit. Das ist uns alles zu ungenau. Wir hätten gern den genauen Zeitpunkt - besser noch die SCN. Um das zu erfahren, hat Oracle Flashback Versions Query und ein paar nützliche
Pseudospalten spendiert.
Wenn man den genauen Zeitpunkt erfahren möchte, zu dem Werte geändert wurden, stürzt man sich auf die Spalten VERSIONS_STARTSCN und VERSIONS_STARTTIME der FLASHBACK VERSIONS QUERY.
Wir ändern als Beispiel einfach mal das Gehalt in der Tabelle jobs:
SQL> update jobs set min_salary=7777 where job_title='Accountant';
1 Zeile wurde aktualisiert.
Abgelaufen: 00:00:00.00
SQL> commit;
Transaktion mit COMMIT abgeschlossen.
Abgelaufen: 00:00:00.00
SQL> update jobs set max_salary=11111 where job_title='Accountant';
1 Zeile wurde aktualisiert.
Abgelaufen: 00:00:00.01
SQL> commit;
Transaktion mit COMMIT abgeschlossen.
Abgelaufen: 00:00:00.00
Und jetzt sehen wir uns den Änderungsverlauf an:
SQL> COL VERSIONS_XID FORMAT A17
SQL> COL VERSIONS_STARTTIME FORMAT A18
SQL> COL JOB_TITLE FORMAT A10
SQL> SELECT VERSIONS_XID, VERSIONS_OPERATION,
2 VERSIONS_STARTSCN , VERSIONS_STARTTIME,
3 --VERSIONS_ENDSCN, VERSIONS_ENDTIME,
4 JOB_TITLE, MIN_SALARY, MAX_SALARY
5 FROM JOBS VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
6 WHERE JOB_TITLE='Accountant'
7 ORDER BY VERSIONS_STARTSCN;
VERSIONS_XID V VERSIONS_STARTSCN VERSIONS_STARTTIME JOB_TITLE MIN_SALARY MAX_SALARY
----------------- - ----------------- ------------------ ---------- ---------- ----------
02002100270B0000 U 3538077 10.02.13 13:00:46 Accountant 7777 9000
010020007D0A0000 U 3538081 10.02.13 13:00:48 Accountant 7777 11111
Accountant 4200 9000
3 Zeilen ausgewählt.
Die letzte Zeile hat keine Werte in den Spalten VERSIONS_XID, V, VERSIONS_STARTSCN und VERSIONS_STARTTIME. Das ist der Ursprungszustand. In Spalte V wird über die Kürzel I(nsert), U(pdate) und D(elete) die Änderungsoperation angegeben. Mit diesen Angaben kann man dann FLASHBACK QUERY wie oben nutzen, um die alten Werte zu ziehen und weiter zu verarbeiten.
Flashback Drop
Per FLASHBACK DROP kann ein DROP TABLE rückgängig gemacht werden. Dabei wird sowohl die Tabelle, als auch deren Indizes, Constraints usw. gerettet.
Eine gelöschte Tabelle kann solange wieder hergestellt werden, wie der von ihr genutzte Platz im Tablespace nicht benötigt wird. Die UNDO RETENTION spielt hier ausnahmsweise keine Rolle.
Dieses Flashback-Feature ist meiner Meinung nach das langweiligste. Das liegt aber vielleicht daran, dass ich noch nie aus Versehen eine Tabelle gelöscht habe. :)
SQL> DROP TABLE jobs;
Tabelle wurde gelöscht.
Abgelaufen: 00:00:00.18
SQL> SELECT object_name, original_name, type, droptime
2 FROM user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
DROPTIME
------------------------------ -------------------------------- ---------
BIN$/KCe7FP4T/6HZfUeTGb53A==$0 JOBS TABLE
2013-02-10:13:00:46
SYS_LOB0000830301C00003$$ SYS_LOB0000830301C00003$$ LOB
2013-02-10:12:37:46
SYS_IL0000830301C00003$$ SYS_IL0000830301C00003$$ LOB INDEX
2013-02-10:12:37:46
3 Zeilen ausgewählt.
Abgelaufen: 00:00:00.02
SQL> FLASHBACK TABLE "BIN$/KCe7FP4T/6HZfUeTGb53A==$0" TO BEFORE DROP RENAME TO jobs;
Flashback abgeschlossen.
Abgelaufen: 00:00:00.04
Flashback Transaction Query
Eigentlich ist hier Schluss mit den SE-Flashback-Features, denn Flashback Transaction Query ist laut
Oracle ein EE-Feature.
Aber beim Testen ist mir aufgefallen, dass man auch in der SE die Möglichkeit besitzt, Flashback Transaction Query zu nutzen.
Einzige Voraussetzung: Man muss SUPPLEMENTAL LOGGING aktivieren.
Ich bin davon ausgegangen, dass das in der SE gar nicht funktioniert, aber naja...
Also: Ist das Logging eingeschaltet?
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
1 Zeile wurde ausgewõhlt.
Supplemental Logging ist standardmäßig abgeschaltet. Und so schaltet man es ein:
SQL> alter database add supplemental log data;
Dieses Logging schreibt zusätzliche Informationen in die DB. Per Flashback Transaction Query kann man sich damit DML ausgeben lassen, welche die geloggten Operationen rückgängig machen. Aus einem DELETE wird ein INSERT und umgekehrt, ein UPDATE-Befehl findet seine entsprechende Korrektur usw.
Logging bedeutet auch zusätzliche Last auf der DB. Das muss man sich natürlich vor Augen führen, bevor man die Entscheidung zur Aktivierung trifft.
Und so holt man sich das UNDO_SQL:
SQL> conn / as sysdba
Connect durchgeführt.
Abgelaufen: 00:00:00.00
GLOBAL_NAME
--------------------------------------------------
sys@ORCL 10.02.2013 13:30:34
Abgelaufen: 00:00:00.00
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '02002100270B0000';
UNDO_SQL
--------------------------------------------------------------------------------
update "HR"."JOBS" set "MIN_SALARY" = '4200' where ROWID = 'AAASbGAAFAAAAC/AAE';
2 Zeilen ausgewählt.
Abgelaufen: 00:00:06.83
SQL> update "HR"."JOBS" set "MIN_SALARY" = '4200' where ROWID = 'AAASbGAAFAAAAC/AAE';
1 Zeile wurde aktualisiert.
Abgelaufen: 00:00:00.00
Wie gesagt, handelt es sich hier um ein EE-Feature. In der SE sollte man es keinesfalls nutzen. Wahrscheinlich ist bereits das Einschalten des SUPPLEMENTAL LOGGING lizenzpflichtig.
Fazit
Flashback ist ein tolles Feature der Oracle-DB. Bereits mit den Möglichkeiten, die die SE bietet, kommt man sehr weit:
Es ist recht einfach möglich, den genauen Fehlerzeitpunkt zu bestimmen (Flashback Versions Query) und die Daten lassen sich dann damit einfach aus der Vergangenheit in die Gegenwart holen.
