Logo Oracle Deutschland   Application Express Community
Transaktionsarchiv für eine Tabelle: "Flashback Data Archive" für alle!

Erscheinungsmonat APEX-Version Datenbankversion
Januar 2014 alle 11.2.0.4 und höher

Abfragen "in die Vergangenheit": Flashback Query

Bereits seit geraumer Zeit (um genau zu sein: seit der Version 9) unterstützt die Oracle-Datenbank Flashback Query, also die Möglichkeit, Abfragen "in der Vergangenheit" auszuführen. Die älteren Versionen werden dabei aus den UNDO-Tablespaces geholt, in denen Sie von der Datenbank ohnehin verwaltet werden. Im Aktionsmenü eines interaktiven Berichts ist diese Möglichkeit sogar out-of-the-box integriert (Abbildung 1).

Integration "Flashback Query" in einem interaktiven Bericht
Integration "Flashback Query" in einem interaktiven Bericht

Flashback Query kann in jeder SQL-Anfrage einfach genutzt werden.

SQL> delete from emp;

14 Zeilen gelöscht.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from emp as of timestamp (systimestamp - interval '5' minute);

EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM DEPTNO
----- ---------- --------- ----- ------------------- ----- ----- ------
 7369 SMITH      CLERK      7902 17.12.1980 00:00:00   800           20
 7499 ALLEN      SALESMAN   7698 20.02.1981 00:00:00  1600   300     30
 7521 WARD       SALESMAN   7698 22.02.1981 00:00:00  1250   500     30
 7566 JONES      MANAGER    7839 02.04.1981 00:00:00  2975           20
 7654 MARTIN     SALESMAN   7698 28.09.1981 00:00:00  1250  1400     30
 7698 BLAKE      MANAGER    7839 01.05.1981 00:00:00  2850           30
 7782 CLARK      MANAGER    7839 09.06.1981 00:00:00  2450           10
 7788 SCOTT      ANALYST    7566 09.12.1982 00:00:00  3000           20
 7839 KING       PRESIDENT       17.11.1981 00:00:00  5000           10
 7844 TURNER     SALESMAN   7698 08.09.1981 00:00:00  1500     0     30
 7876 ADAMS      CLERK      7788 12.01.1983 00:00:00  1100           20
 7900 JAMES      CLERK      7698 03.12.1981 00:00:00   950           30
 7902 FORD       ANALYST    7566 03.12.1981 00:00:00  3000           20
 7934 MILLER     CLERK      7782 23.01.1982 00:00:00  1300           10

14 Zeilen ausgewählt.

Da die UNDO-Tablespaces regelmäßig überschrieben werden, ist die Zeitspanne begrenzt - auf einem produktiven System dürften, je nach Transaktionslast, 5 bis 15 Minuten realistisch sein. Sind die älteren Versionen der Tabellenzeilen nicht mehr in den UNDO-Tablespaces vorhanden, so bekommt man eine Fehlermeldung.

SQL> select * from emp as of timestamp (systimestamp - interval '1' month);
select * from emp as of timestamp (systimestamp - interval '1' month)
              *
FEHLER in Zeile 1:
ORA-08180: Kein Snapshot gefunden basierend auf angegebener Zeit

Früher: Datenbankoption "Total Recall"

Um auch längere Zeiträume zu unterstützen, wurde mit der Datenbankversion 11g die Option "Total Recall" eingeführt - diese erforderte bislang eine separate Lizenz. Damit ist es möglich, sog. Flashback Data Archives zu erstellen, in welche die Datenbank die älteren Versionen der Tabellenzeilen regelmäßig kopiert. Findet eine "Flashback Query" die gewünschte Version nicht mehr im UNDO-Tablespace, schaut es im Flashback-Archiv nach. Die Flashback-Archive selbst wurden mit Total Recall automatisch partitioniert und komprimiert, um den Zugriff so performant wie möglich zu gestalten.

Heute: Flashback Data Archives für alle

Ab dem Datenbankrelease 11.2.0.4 können Flashback Data Archives mit allen Datenbankeditionen genutzt werden - die Option "Total Recall" gibt es nicht mehr. Die Archive werden nun aber nicht mehr automatisch partitioniert und komprimiert - diese Data Optimization kann nun separat eingeschaltet werden und nur diese erfordert eine Lizenz der Advanced Compression Option. Die Nutzung eines Flashback-Archive an sich (ohne "Data Optimization") erfordert dagegen keine zusätzliche Lizenz.

Ein Hinweis für Nutzer von Oracle 12c: Flashback Data Archives können derzeit noch nicht in einer Datenbank mit der Multitenant-Architektur (Container-Database, Pluggable Database) genutzt werden. Sie benötigen eine Datenbank ohne Multitenant-Architektur.

Nutzung eines Flashback-Archivs

Die folgende SQL-Anweisung (CREATE FLASHBACK ARCHIVE) legt ein Flashback Data Archive an - das muss entweder vom DBA oder einem anderen Datenbankuser mit dem Privileg FLASHBACK ARCHIVE ADMINISTER getan werden. Mit RETENTION wird angegeben, für welchen Zeitraum dieses Flashback-Archiv Daten aufnehmen soll. Im Gegensatz z diesem Beispielkommando empfiehlt es sich, eigene Tablespaces einzurichten - zumindest nach RETENTION-Zeiten. Wichtig ist der Zusatz NO OPTIMIZE DATA - damit wird das Flashback-Archiv ohne Partitionierung oder Komprimierung angelegt und erfordert so keine zusätzliche Lizenz.

create flashback archive FDBA_MYDATA_1Y_USERS 
tablespace users 
quota 100G
retention 1 year
no optimize data
/

Wird eine Tabelle mit diesem Flashback Data Archive verknüpft, so kann diese bis zu einem Jahr rückverfolgt werden - alle Änderungen werden im Flashback Data Archive gespeichert. Das gilt natürlich nur solange die Speicherplatz-Quota eingehalten wird. Das nun folgende Kommando verbindet die Tabelle EMP mit dem gerade erzeugten Flashback-Archiv.

alter table EMP flashback archive FDBA_MYDATA_1Y_USERS
/

Und das war's auch schon. Von nun an kann eine Flashback Query aud die Tabelle EMP bis zu einem Jahr in die Vergangenheit zurückreichen (vorausgesetzt, die Daten sind tatsächlich vorhanden). Die Zuordnung von Tabellen zu Flashback-Archiven kann in der Data Dictionary View USER_FLASHBACK_ARCHIVE_TABLES nachvollzogen werden.

SQL> select table_name, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME 
  2  from USER_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME      FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME
--------------- ------------------------------ -----------------------
EMP             FDBA_MYDATA_1Y_USERS           SYS_FBA_HIST_87108

1 Zeile wurde ausgewählt.

Werfen Sie doch mal einen Blick in die Tabelle SYS_FBA_HIST_87108 ...

SQL> desc SYS_FBA_HIST_87108
 Name                                   Null?    Typ
 -------------------------------------- -------- -----------------------
 RID                                             VARCHAR2(4000)
 STARTSCN                                        NUMBER
 ENDSCN                                          NUMBER
 XID                                             RAW(8)
 OPERATION                                       VARCHAR2(1)
 EMPNO                                           NUMBER(4)
 ENAME                                           VARCHAR2(10)
 :                                               :

SQL> select rid, startscn, endscn, xid, empno, ename from SYS_FBA_HIST_87108 where deptno=10;

RID                    STARTSCN     ENDSCN XID                   EMPNO ENAME
-------------------- ---------- ---------- ---------------- ---------- ----------
AAAVREAAEAAAACXAAN      1805562    1805562 08001B0013070000       7934 MILLER
AAAVREAAEAAAACXAAI      1805562    1805562 08001B0013070000       7839 KING
AAAVREAAEAAAACXAAG      1805562    1805562 08001B0013070000       7782 CLARK
AAAVREAAEAAAACXAAG                 1805562                        7782 CLARK
AAAVREAAEAAAACXAAI                 1805562                        7839 KING
AAAVREAAEAAAACXAAN                 1805562                        7934 MILLER

6 Zeilen ausgewählt.

Man sieht sehr schön, dass in dieser Tabelle alle Versionen der Tabellenzeilen abgelegt werden - eben bis hin zu einem Jahr. Änderungen an der Basistabelle (Hinzufügen oder Umbenennen einer Spalte) sind erlaubt - die Datenbank wendet die Änderung automatisch auch auf das Flashback-Archiv an. Vor versehentlichem Löschen sind Flashback Data Archives dagegen geschützt - das bemerkt man recht schnell, wenn man ein SQL DELETE versucht.

SQL> delete from SYS_FBA_HIST_87108 where deptno=10;
delete from SYS_FBA_HIST_87108
            *
FEHLER in Zeile 1:
ORA-55622: DML-, ALTER- und CREATE UNIQUE INDEX-Vorgänge sind auf 
Tabelle "SCOTT"."SYS_FBA_HIST_87108" nicht zulässig

Das bedeutet jedoch nicht, dass diese Operationen nicht mehr möglich sind: Vielmehr muss die Verbindung zwischen Tabelle und Flashback-Archiv zuerst gelöst werden - unter anderem dazu dient das PL/SQL-Paket DBMS_FLASHBACK_ARCHIVE. Die folgenden Schritte heben zuerst die Verknüpfung zwischen Tabelle und Archiv auf, danach werden einige Zeilen aus dem Archiv gelöscht und schließlich wird die Verbindung wiederhergestellt. Die gleiche Vorgehensweise wählt man, wenn sich ein (nicht für Flashback-Archive unterstütztes) DDL-Kommando auf die Basistabelle angewendet werden soll).

-- Verknüpfung zwischen Tabelle und Flashback Archiv aufheben
begin
  dbms_flashback_archive.disassociate_fba(
    owner_name => 'SCOTT',
    table_name => 'EMP'
  );
end;
/

-- Einige Zeilen aus dem Archiv löschen
delete from SYS_FBA_HIST_87108 where deptno=10
/

-- Verknüpfung zwischen Tabelle und Flashback Archiv wiederherstellen
begin
  dbms_flashback_archive.reassociate_fba(
    owner_name => 'SCOTT',
    table_name => 'EMP'
  );
end;
/

Fazit

Mit Hilfe der Flashback-Archive kann die ohnehin schon vorhandene Möglichkeit, "in die Vergangenheit" abfragen zu können, nochmals massiv erweitert werden. Auch Zeiträume von mehreren Jahren werden nun problemlos unterstützt. Ab der Datenbankversion 11.2.0.4 können die Flashback-Archive auch ohne zusätzliche Lizenz genutzt werden - allerdings werden die Flashback-Archive weder partitioniert noch komprimiert. Wer diese Optimierungen, wegen großer Datenmengen benötigt, kann sie beim Erstellen des Flashback-Archivs einschalten - hierfür ist dann eine Lizenz der Advanced Compression Option erforderlich.

In APEX-Anwendungen nutzen Sie diese Möglichkeiten exakt genauso wie das "herkömmliche" Flashback Query, welches bereits in einem Community-Tipp vorgestellt wurde - mit einem vorhandenen Flashback-Archiv können Sie genauso arbeiten, allerdings mit wesentlich größeren Zeitspannen in der AS OF-Klausel.

Zurück zur Community-Seite