Überblick Einstellungen zur Verwendung von Flashback

Thema
REISE IN DIE VERGANGENHEIT
Autor
Kamel Bouzenad ([email protected])
Art der Info
Infos für Entwickler und DBAs (April 2002)
Quelle
Oracle-Dokumentation sowie beratende Aktivitäten
Überblick
Unter Verwendung des UNDO-Mechanismus und mit Hilfe von Flashback-Abfragen
stellt Oracle 9i eine Lösung für alltägliche Benutzerfehler wie beispielsweise
Datenverlust oder falsche Berechnungen bereit.
Mit Flashback-Abfragen können Sie folgendes tun:
•
•
•
•
•
•
Verlorene Daten wiederherstellen.
Inkorrekte Änderungen rückgängig machen.
Aktuelle Daten mit früheren Versionen vergleichen.
Die Entwicklung der Daten über einen gewissen Zeitraum beobachten.
Bestimmte Anwendungen auf alte Daten anwenden, z. B. Berichte, Grafiken,
usw…
Zu Test- oder Debug-Zwecken zum Anfangszustand zurückkehren.
Der Flashback-Abfragenmechanismus ist mit einer automatischen Verwaltung des
UNDO verknüpft, über die der DBA die Dauer der Datenaufbewahrung festlegt.
Die Zeitdauer einer Suche ist auch abhängig von der Größe des Speicherplatzes.
Einstellungen zur Verwendung von Flashback-Abfragen
Bevor die Vorteile von Flashback-Abfragen genutzt werden können, müssen bestimmte
Operationen von einem DBA ausgeführt werden. Zusammengefasst handelt es sich um die
folgenden Vorgänge:
•
Aufbauen eines automatischen UNDO Managements entweder durch:
a. Initialisieren des Parameters UNDO_RETENTION (Dauer der Datenspeicherung
im Speicher in Sekunden).
b. Festlegen von UNDO_MANAGEMENT=AUTO
c. Erstellen eines UNDO-Tabellenbereichs, wobei dessen Größe in Abhängigkeit
von UNDO_RETENTION und der Häufigkeit der Einfüge-, Lösch- und
Änderungsvorgänge der Daten gewählt werden muss.
•
Vergabe von Rechten zum Ausführen des DBMS_FLASHBACK Packages an die
entsprechenden Benutzer, Rollen und Anwendungen.
Beispiel 1 der Verwendung von Flashback-Abfragen
SQL> select ename , sal from emp
2 where job='SALESMAN';
ENAME
---------ALLEN
WARD
MARTIN
TURNER
SAL
---------1600
1250
1250
1500
SQL> update emp set sal=sal * 1.1 where job='SALESMAN'; -- Increase SALESMAN salaries by 10 percent
4 rows updated.
SQL> Commit; -- Ending the current transaction , because Flashback cannot be enabled in the middle of a transaction
Commit complete.
SQL> select ename , sal from emp
2 where job='SALESMAN';
-- Checking salary increase for SALESMAN
ENAME
SAL
------------------ALLEN
1760
WARD
1375
MARTIN
1375
TURNER
1650
-- Enabling flashback at 15mn in the past
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - (15/(24*60)));
PL/SQL procedure successfully completed.
SQL> select ename , sal from emp -- seeing how salaries was in the past (15mn before)
2 where job='SALESMAN';
ENAME
---------ALLEN
WARD
MARTIN
TURNER
SAL
---------1600
1250
1250
1500
SQL> EXECUTE DBMS_FLASHBACK.DISABLE; -- disabling the Flashback
PL/SQL procedure successfully completed.
SQL> select ename , sal from emp
2 where job='SALESMAN';
ENAME
---------ALLEN
WARD
MARTIN
TURNER
SAL
---------1760
1375
1375
1650
So werden Flashback-Abfragen angewendet
Historische Daten stehen nur nach der Aktivierung und vor der anschließenden Deaktivierung
der Flashback-Abfragen zur Verfügung.
Im Folgenden sind einige Anwendungstechniken aufgeführt:
1. Vor der Anwendung von SELECT-Anweisungen auf frühere Datenbestände
DBMS_FLASHBACK.ENABLE_AT_TIME oder
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER ausführen.
2. Nach einer SELECT Anweisung DBMS_FLASHBACK.DISABLE ausführen.
3. Zwischen den beiden Aufrufen können nur die SELECT-Anweisungen
ausgeführt werden, andere DML- oder DDL-Befehle werden nicht akzeptiert.
•
Um die Ergebnisse einer Flashback-Abfrage in einer DDL oder DML auf den
momentanen Zustand der Datenbank anzuwenden, müsste vor
DBMS_FLASHBACK.DISABLE ein Cursor geöffnet und danach unter Verwendung der
Anweisungen INSERT, UPDATE, usw... die Daten des Cursors auf den aktuellen Zustand der
Datenbank angewendet werden.
•
•
Um aktuelle Daten mit früheren Daten zu vergleichen, muss ein Cursor mit
aktiviertem Flashback geöffnet werden, dieser anschließend deaktiviert und die
früheren Daten in einer temporären Tabelle abgelegt werden. Unter Verwendung
von Operatoren, wie z. B. den Vergleichsoperatoren MINUS, ODER, usw., können
Sie dann die Daten vergleichen.
Bei Verwendung von DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER kann
die Anzahl der Änderungen gespeichert werden. Diese Angaben können später in
einer Flashback-Abfrage verwendet werden; dabei sollte darauf geachtet werden, vor
dieser Funktion einen COMMIT-Befehl auszuführen, damit die Datenbankkonformität
gewahrt ist (Consistent State).
Anwendungsbeispiel für Flashback-Abfragen
1. Ermitteln des Gehalts eines Angestellten vor der Gehaltsänderung.
SQL> select sal from emp where empno=7654;
SAL
---------1375
-- here because we know that salaries was updated one day before.
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate -1.5);
PL/SQL procedure successfully completed.
SQL> select sal from emp where empno=7654;
SAL
---------1250
SQL> EXECUTE DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
SQL> select sal from emp where empno=7654;
SAL
---------1375
SQL>
2.
Zurücksetzen der Gehälter der SALESMAN auf den Stand vor der letzten
Erhöhung.
-- Starting script execution.
SQL> set serverout on;
SQL> select ename, sal from emp
2 where job='SALESMAN'
3 /
ENAME
---------ALLEN
WARD
MARTIN
TURNER
-- Current salaries (after salary increase )
SAL
---------1760
1375
1375
1650
SQL> execute DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate -1.5); -- Enable Flashback at one and half day before
PL/SQL procedure successfully completed.
SQL> select ename, sal from emp
2 where job='SALESMAN'
3 /
ENAME
---------ALLEN
SAL
---------1600
-- Salaries before increase for SALESMAN employees
WARD
MARTIN
TURNER
1250
1250
1500
SQL> execute DBMS_FLASHBACK.DISABLE; -- Disable the Flashback
PL/SQL procedure successfully completed.
SQL> declare
-- Anonymos PL/SQL bloc that recover the old salaries for SALESMANS.
2
3 cursor curSal is
4 select ename, sal from emp
5 where job='SALESMAN';
6 recSal curSal%rowtype;
7 begin
8 DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate -1.5); -- Enabling FlashBack
9 OPEN curSal; -- opening the cursor to get old salaries in memory (buffer cache)
10 DBMS_FLASHBACK.DISABLE; -- Disabling FlashBack
11 fetch curSal into recSal;
12 loop
13 exit when curSal%notfound;
14 update emp set sal=recSal.sal where ename=recSal.ename -- Recovering the old salaries
15 and job='SALESMAN';
16 fetch curSal into recSal;
17 end loop;
18 commit;
19 exception
20 when others then
21 dbms_output.put_line('ERROR :' ||substr(SQLERRM,1,150));
22 end;
23 /
PL/SQL procedure successfully completed.
SQL> select ename, sal from emp
2 where job='SALESMAN'
3 /
ENAME
---------ALLEN
WARD
MARTIN
TURNER
SAL
---------1600
1250
1250
1500
SQL>
Grenzen von Flashback-Abfragen
•
Wenn durch die Verwendung von DDLs die Struktur von Tabellen, beispielsweise über
Löschvorgänge, das Verändern von Spalten, das Löschen einer Partition oder das
Verkürzen einer Tabelle, verändert wird, werden die UNDO-Daten für die betroffene
Tabelle ungültig. Diese UNDO-Daten können nicht aus der Ausführungsumgebung
•
•
•
•
dieser DDLs extrahiert werden. In diesen Fällen wird bei einer Abfrage auf die
betreffende Datenbank der Fehler ORA-1466 ausgegeben. DDLs, die die
Speicherattribute einer Tabelle verändern oder Erweiterungen, einschränkende
Bedingungen oder Partitionen hinzufügen, sind von diesen Einschränkungen nicht
betroffen.
Die
Verwendung
von
DBMS_FLASHBACK.ENABLE_AT_TIME
kann
zu
Präzisionseinbußen führen, da für die angegebene Zeit immer ein SCN-Mapping
durchgeführt wird. Angenommen die SCNs werden periodisch alle 5 Min. festgehalten,
SCN=1000 entspricht 08H00 AM und dementsprechend SCN=1005 8H05 AM, dann
wird ein um 8H03 AM aktivierter Flashback auf SCN=1000 gemappt. Dies ist nicht
genau genug und führt dazu, dass eine um 08H01 AM durchgeführte DDL den Fehler
ORA-1466 hervorruft. Ein auf SCN basierter Flashback führt zu genaueren
Ergebnissen.
Der Flashback sollte zunächst deaktiviert werden, um ihn dann zu reaktivieren;
ENABLE/DISABLE zu verschachteln ist in diesem Fall nicht zulässig.
Eine Flashback-Abfrage kann z. B. nicht erfolgreich ausgeführt werden, wenn ein Index
nach dem Aktivieren des Flashbacks erstellt oder neuerstellt wurde. Aus diesem
Grunde sollte die Abfrage mit einem Hinweis (Hint) ausgeführt werden, den Index nicht
zu verwenden, etwa SELECT /* FULL(emp) */ from emp ;
Der Flashback kann nicht auf entfernte Tabellen (database link) angewendet werden.
Fazit
Der Mechanismus der Flashback-Abfragen erfüllt einige wichtige Anforderungen und
kann in bestimmten Fällen helfen, Redo-Protokolle zu vermeiden; das Prinzip ist sehr
einfach, erfordert aber grundlegende Kenntnisse der PL/SQL-Programmierung, um
daraus optimalen Nutzen zu ziehen. Oracle hat bereits angekündigt, FlashbackAbfragen im nächsten Release als SQL-Anweisungen einzuführen.
Kamel Bouzenad
Trivadis SA
Rue Marterey 5
CH-1005 Lausanne
E-Mail:
Tel.:
Fax:
Internet:
[email protected]
+41-21-321 47 00
+41-21-321 47 01
http://www.trivadis.com