20. April 2009

Abfragen in die Vergangenheit: Flashback Query

English title: Query into the past: FLASHBACK QUERY!

Wusstet Ihr, dass man in der Oracle-Datenbank auch Änderungen zurückverfolgen kann, die bereits mit COMMIT bestätigt wurden. Flashback Query gibt es schon ziemlich lange, allerdings wird es immer noch recht selten genutzt ...
Did you know that you can review old data values even after changes were made permanent with COMMIT? And although Flashback Query is available for quite a long time now it's not used very frequently ...
Probiert mal folgendes aus:
Try the following:
SQL> delete from emp;

14 rows deleted.

SQL> commit;

commit complete.

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

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7369 SMITH      CLERK      7902 17.12.80   801           20
 7499 ALLEN      SALESMAN   7698 20.02.81  1601   300     30
 7521 WARD       SALESMAN   7698 22.02.81  1251   500     30
 7566 JONES      MANAGER    7839 02.04.81  2975           20
 7654 MARTIN     SALESMAN   7698 28.09.81  1251  1400     30
 7698 BLAKE      MANAGER    7839 01.05.81  2850           30
 7782 CLARK      MANAGER    7839 09.06.81     0           10
 7788 SCOTT      ANALYST    7566 09.12.82  3000           20
 7839 KING       PRESIDENT       17.11.81 76000           10
 7844 TURNER     SALESMAN   7698 08.09.81  1501     0     30
 7876 ADAMS      CLERK      7788 12.01.83  1101           20
 7900 JAMES      CLERK      7698 03.12.81   951           30
 7902 FORD       ANALYST    7566 03.12.81  3000           20
 7934 MILLER     CLERK      7782 23.01.82  1301           10

14 rows selected.

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

14 rows created.
Das Beispiel zeigt, wie man eine Änderung rückgängig machen kann, obwohl sie schon mit COMMIT "festgemacht" wurde. Es wird nur eine zusätzliche SQL-Klausel benötigt ... mehr nicht. Flashback Query wurde bereits mit Oracle9i eingeführt. Der Hintergrund ist der, dass die nötigen Undo-Informationen ohnehin anfallen. Solange das COMMIT noch nicht erfolgt ist, sehen andere Datenbanksitzungen stets den alten Stand der Daten vor der Änderung (before images) - In der Oracle-Datenbank blockiert eine schreibende Sitzung niemals eine lesende und umgekehrt.
This example shows how a committed DELETE operation can be undone. Just an additional SQL clause is necessary. The Flashback Query feature was introduced in Oracle9i. It's based on the UNDO management of the Oracle database. When one session manipulated data other sessions see the old values (before images) as long the first session has not issued the COMMIT command. In Oracle a reading session never blocks a writing one and vice versa.
Nach dem COMMIT werden die alten Daten nun mitnichten zerstört - sie werden lediglich zum Überschreiben freigegeben. Wann dieses Überschreiben nun tatsächlich stattfindet, hängt von der Transaktionslast auf dem Datenbanksystem ab. Die Before Images werden in den Undo-Segmenten und diese wiederum in den Undo Tablespaces gespeichert. Man muss sich das Schreiben in einem Undo-Segment "rollierend" vorstellen; wenn die Datenbank am Ende angelangt ist, fängt sie mit der Suche nach dem nächsten freien Platz wieder von vorne an. Und erst wenn die Before Images tatsächlich überschrieben wurden, sind sie weg - vorher können Sie noch genutzt werden ... beispielsweise zum Betrachten älterer Datenstände, wie oben.
After the COMMIT was issued those Before Images don't get explicitly deleted. They're just marked for overwriting. They get actually overwritten when the undo space they occupy is needed - and this depends on the transaction load of the system. All undo data is stored in the undo segments and those are stored in the undo tablespace. Imagine the writing in those undo segments as a "rolling" process. When the end of the segment is reached it starts looking for free space at the beginning. So the before images available as long they don't get overwritten. And as long they're available they can be used: to look into "old" data versions as shown above ...
Je größer die Undo-Segmente nun gestaltet werden (je größer das Undo-Tablespace werden darf), desto weiter kann man in der Vergangenheit zurückgehen. Auf einem Produktionssystem mit hoher Last sind wahrscheinlich 10 bis 15 Minuten das äußerste der Gefühle, auf einem weniger stark belasteten System kann es durchaus mehr werden ...
The larger the undo segments may grow the longer a query "may reach into the past". On a production system with high load this might be only a few minutes, on a system with low load the timeframe might be greater.
Wozu ist das nun gut ...?
So what can this be used for ...?
Man kann dem Endanwender in der Applikation die Möglichkeit geben, Daten zu einem Zeitpunkt der Vergangenheit zu selektieren (bspw. 5 Minuten vorher). Damit bekommt dieser die Möglichkeit, einen eventuell gemachten Fehler (Daten versehentlich gelöscht) selbst zu korrigueren. Wo man ansonsten die Daten aus einem Backup-System zurückspielen müsste, reicht hier eine einfache SQL-Klausel. Aber damit die Endnutzer davon profitieren können, müssen die Anwendungsentwickler Flashback Query in die Applikation einbauen.
With the Flashback Query capability an application developer might allow the end user to query data as of a point of time in the past. The practical benefit is that end users then could correct errors themselfes. When, for instance, an end user accidentially deleted a record they can use the flashback feature to query the data as of before the deletion and optionally directly recover it. Without such a capability this would require to use a backup system, lookup the data there and recover it into the production system. With flashback query this is just the application of a SQL clause - but developers have to leverage it into the applications before the end users can benefit from it.
Datenbankparameter
Database parameters
In diesem Zusammenhang interessant ist der Parameter UNDO_RETENTION, der vom DBA gesetzt werden kann (Doku). Damit lässt sich festlegen, wie weit man "in der ergangenheit zurückgehen" möchte. Standardmäßig steht er auf 900 Sekunden, also 15 Minuten. Die Datenbank wird die UNDO-Segmente und -Tablespaces nun so verwalten, dass diese Vorgabe nach Möglichkeit eingehalten wird. Ist die AUTOEXTEND-Klausel des Undo-Tablespace aktiv, so wird er entsprechend wachsen.
Most interesting is the database parameter UNDO_RETENTION. This parameter (Documentation) might be set by the DBA and controls the amount of time a query might "reach into the past". The default is 900 (seconds), which means 15 Minutes. The database adjusts the undo segments and tablespaces in order to fulfil this reqiurement. If the AUTOEXTEND-clause is activated for the undo tablespace it will grow repectively.
Wenn das UNDO-Tablespace nicht (mehr) wachsen kann, werden die Daten bei Bedarf jedoch überschrieben; UNDO_RETENTION ist also eher als "Hinweis an die Datenbank" und nicht als "Garantie" zu verstehen (das könnte man mit der RETENTION GUARANTEE-Klausel beim CREATE UNDO TABLESPACE-Kommando auch ändern).
If the Undo Tablespace cannot grow (either because there is no space left on the device or the AUTOEXTEND clause is deactivated) then the undo information will be overwritten even if it's not older than the specified undo retention period. So the UNDO_RETENTION parameter is considered as a hint, not as a "hard" parameter. This might be changed with the RENTENTION GUARANTEE clause in the CREATE UNDO TABLESPACE command.
Mehr zu Flashback Query in den nächsten Blog Postings. Die Flashback-Technologie ist in den Versionen seit Oracle9i stetig ausgebaut worden und es geht noch wesentlich mehr als das oben gezeigte ...
More about the Flashback Query technology will follow in the next blog posting. The feature was further developed since Oracle9i and there is a lot more functionality available than shown in the beginning.
Übrigens: In Application Express steht Flashback Query bei den Interaktiven Berichten bereit. Einfach im Menü Flashback auswählen und die Zeit eingeben, die man "zurück" möchte. Genau so sollte Flashback in allen Anwendungen bereitgestellt werden. Der Endanwender legt einfach nur fest, dass er "in die Vergangenheit" abfragen möchte ...
BTW: Application Express leverages Flashback Query in its interactive reports. Just navigate to the menu (for end users), choose Flashback and enter the amount of minutes you want "to go into the past". That's the way I'd like to see Flashback in business applications: End users just have to choose to use it ...

1 Kommentar:

Daniel Wetzler hat gesagt…

In dem Zusammanhang ist noch die Option versions interessant.
Z.B. :

SELECT column1, column, versions_operation, versions_xid, versions_starttime
FROM mytable versions between timestamp minvalue and maxvalue
ORDER BY column1, versions_starttime;

Versiopns_XID ist dabei eine Transaktionsnummer, die man benutzen kann um in flashback_transaction_query das SQL-Statemant abzufragen mit dem man den ursprünglichen Zustand der Tabelle wieder hertstellen kann

Beliebte Postings