Überblick Einstellungen zur Verwendung von Flashback
Werbung
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
