Haben Sie nicht auch schon einmal einen SQL-Befehl abgesetzt (z.B. DROP TABLE oder DROP INDEX) und sich anschließend gewünscht, Sie könnten die Zeit wieder zurückdrehen?
Bislang war dies entweder nur mit dem Einspielen eines funktionierenden Backups und einem anschließenden unvollständigem Recovery (was in der Regel sehr zeitaufwändig war) oder mit dem Import einer zuvor angelegten Dump-Datei möglich. Bereits in Version 9i wurde das Feature FLASHBACK eingeführt, mit dessen Hilfe es möglich ist, Daten zu einem in der Vergangenheit liegenden Zeitpunkt auf Session- bzw. Statementebene zur Verfügung zu stellen. Es konnten jedoch keine DDL-Anweisungen rückgängig gemacht werden. Seit Version 10g wird dieses Feature nun um zusätzliche Konzepte erweitert, durch die sich u.a. auch schwerwiegende Anwenderfehler schnell und einfach beheben lassen.
Das FLASHBACK DATABASE-Konzept
Das FLASHBACK DATABASE-Konzept bietet eine schnelle und unkomplizierte Möglichkeit, ein unvollständiges Recovery durchzuführen und damit einen irrtümlich abgesetzten DROP TABLE oder DROP USER-Befehl zurück zu setzen. Dafür gibt es einen neuen Bereich in der Datenbank, die sog. FLASH RECOVERY AREA, in der spezielle FLASHBACK Log-Dateien abgelegt werden. Die Konfiguration dieses Bereichs wird durch verschiedene Initialisierungsparameter gesteuert:
DB_RECOVERY_FILE_DEST: Datei-Verzeichnis der FLASH RECOVERY AREA
DB_RECOVERY_FILE_DEST_SIZE: Größe der FLASH RECOVERY AREA (Default:3G in 11g)
DB_FLASHBACK_RETENTION_TARGET: Zeitspanne in Minuten, um die die DB zurückversetzt werden kann (Default: 1440 = 1 Tag)
Damit die FLASHBACK DATABASE-Option genutzt werden kann, muss FLASHBACK (bis Oracle 10.x) in der MOUNT-Phase der DB eingeschaltet werden (und sich die DB im Archivierungs-Modus befinden). Ab Version 11.x können Sie den Flashback Modus auch im laufenden Betrieb ein- und ausschalten.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG; -- Archivierung muss eingeschaltet sein
SQL> ALTER DATABASE FLASHBACK ON;
-- ALTER DATABASE FLASHBACK OFF schaltet FLASHBACK wieder aus.
SQL> ALTER DATABASE OPEN;
Informationen zu Speicherplatz und Rücksprungzeit liefert Ihnen die View v$flashback_database_log:
SQL> SELECT * FROM v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET
---------------------------------------- ------------------------------ ---------------
937657 17.03.19 13:41:56 1440
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------------- -----------------------
12288000 122634240
Das Zurücksetzen der Datenbank im Fehlerfall erfolgt entweder anhand einer SCN oder eines Zeitstempels. Die aktuelle SCN kann über folgenden SELECT ermittelt werden:
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
----------
935620
Beispielszenario:
Vor einer Stunde ist ein wichtiger Benutzer mitsamt seinen Objekten gelöscht worden. Das gesamte Benutzerschema muss unbedingt wieder hergestellt werden. Flashback ist bereits eingeschaltet worden.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO TIMESTAMP (sysdate-1/24); -- 1 Stunde zurück
-- oder SQL> FLASHBACK DATABASE TO SCN 935620;
Die Datenbank ist nun physikalisch in einen früheren, konsistenten Zustand versetzt worden und lässt sich nur noch mit OPEN RESETLOGS öffnen:
SQL> ALTER DATABASE OPEN RESETLOGS;
Das FLASHBACK TABLE-Konzept
Muss nicht die gesamte Datenbank zurückgesetzt werden, sondern lediglich einzelne Tabellen, wird dies nun mit Hilfe des FLASHBACK TABLE-Kommandos erreicht:
Beispiel:
Tabelle um 12 Minuten zurücksetzen
SQL> FLASHBACK TABLE scott.emp, scott.dept TO TIMESTAMP (SYSTIMESTAMP -INTERVAL '12' MINUTE);
Zurücksetzen auf SCN 1234567:
SQL> FLASHBACK TABLE scott.emp TO SCN 1234567;
Zurücksetzen auf den 8.8.2019 11 Uhr 09
FLASHBACK TABLE scott.emp TO TIMESTAMP '2019-08-08 11:09:51';
Weitere Interval-Beispiele:
3 Stunden, 4 Minuten und 11 Sekunden:
INTERVAL '03:04:11.000' HOUR TO SECOND
1 Stunde, 52 Minuten:
INTERVAL '01:52' HOUR TO MINUTE
Mit folgenden SQL Befehl können alte Versionen von Tabellendaten ermittelt werden.
SELECT versions_xid XID,
versions_startscn START_SCN,
versions_endscn END_SCN,
versions_operation OP,
empno, ename, sal
FROM scott.emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno = 7934;
XID START_SCN END_SCN O ENAME SAL
---------------- --------- ------- - ----- ----
0004000700000058 115855 I King1 5000
000200030000002D 115564 D King1 5000
000200030000002E 114670 113564 I King2 4000
Hinweise:
Auf diese Weise können nur DML-Anweisungen zurückgesetzt werden, keine DDL-Anweisungen. Constraints und Indizes werden mitberücksichtigt. Trigger sind standardmäßig ausgeschaltet, können aber mittels ENABLE TRIGGERS während des FLASHBACKs aktiviert bleiben. Da es während des FLASHBACKs u.U. zu Änderungen der ROWIDs kommen kann, muss vorher die Option ROW MOVEMENT eingeschaltet werden:
SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
SQL> ALTER TABLE scott.dept ENABLE ROW MOVEMENT;
Hinweis:
Beim Zurückspielen einer Tabelle aus dem Mülleimer, behalten alle mit der Tabelle verbundenen Objekte wie Constraints oder Indizes, den Namen, den Sie im Mülleimer bekommen hatten (laut Oracle ist das ein Feature und kein Bug:-) ).
Sie heißen dann z.B. BIN$fTZdwdToK3rgVQAAAAAAAQ==$0. Der fettmarkierte Teil ist bei allen gelöschten Objekten gleich.
Das RECYCLEBIN-Konzept
Seit Version 10g gibt es bei Oracle eine Art Papierkorb, den sog. RECYCLEBIN. Das normale Löschen einer Tabelle verschiebt die Tabelle lediglich in den RECYCLEBIN und der Speicher wird nicht freigegeben. Den Inhalt des Papierkorbs kann man sich anzeigen lassen:
SQL> SELECT * FROM recyclebin;
oder: SQL> SELECT * FROM user_recyclebin;
oder: SQL> SHOW RECYCLEBIN
SQL> SELECT * FROM dba_recyclebin;
Eine gelöschte Tabelle kann wiederhergestellt werden über:
SQL> FLASHBACK TABLE [<owner>.]<tab_name> TO BEFORE DROP [RENAME TO <tab_neu>];
Hinweis: Es dürfen seit dem Zeitpunkt, auf dem zurückgesetzt werden soll, keine DDL Befehle (ALTER, TRUNCATE,...) auf dem Objekt abgesetzt worden sein.
Endgültig gelöscht wird eine Tabelle nun über folgende Befehle:
SQL> DROP TABLE <tab_name> PURGE; /* löscht die Tabelle sofort und gibt den Speicherplatz frei */
SQL> PURGE TABLE <tab_name>; /* löscht die Tabelle endgültig aus dem Papierkorb, wenn zuvor ein DROP auf sie abgesetzt wurde */
SQL> PURGE TABLESPACE <tbs_name>; /* alle bereits gelöschten Objekte aus dem TBS <tbs_name> werden nun endgültig gelöscht */
SQL> PURGE RECYCLEBIN; /* alle Objekte aus dem Benutzer-Papierkorb werden nun endgültig gelöscht */
Sie können jedoch auch den Mülleimer der Datenbank abschalten:
ALTER SYSTEM SET recyclebin=off scope=spfile;
Jedoch muss für einen Effekt danach die Datenbank neu durchgestartet werden. Witzigerweise konnten wir auch noch gedropte Tabellen aus dem Mülleimer in unserer Oracle 19c SE Datenbank retten, obwohl der Mülleimer abgeschalten war.
Transaction Flashback