Oracle Flashback – przegląd możliwości

Mariusz Żuk

Termin Flashback pojawił się po raz pierwszy już kilka lat temu wraz z wersją Oracle 9i w postaci nowej technologii znanej jako Flashback Query. Było to nowe rozwiązanie mające na celu usprawnienie korygowania błędów popełnianych przez użytkowników lub też wykonywanie analiz historycznych.

Oracle 10g znacznie rozszerzyło funkcjonalność flashback pokrywając niemalże każdy obszar bazy danych. Różnorodność wprowadzonych zmian w wersji 10g oraz później w 11g odnośnie tej technologii sprawia, że posługiwanie się tym narzędziem w pierwszej chwili może wydać się kłopotliwe.

W bazie danych może wystąpić wiele rodzajów błędów, wywoływanych najczęściej przez działania użytkowników.

Podanie błędnych wartości w poleceniach update, pominiecie klauzuli where w poleceniu delete, omyłkowe usunięcie tabeli bądź też użytkownika – wcale nie należy do rzadkości.

Do wersji Oracle 9i niemalże jedynym sposobem na poprawienie takich błędów było wykonanie point-in-time recovery. Alternatywą była jeszcze dodatkowa baza danych typu standby z ustawionym kilkugodzinnym opóźnieniem w stosunku do bazy primary. Opóźnienie zmniejsza ryzyko przejęcia błędu, lecz rozwiązanie to nie jest proste w implementacji. Wiąże się z dodatkowymi kosztami, a i tak nie pomoże nam, jeśli odkryjemy błąd, który wystąpił wcześniej niż wynosi opóźnienie. Również zabezpieczenie bazy primary może nie być w takim przypadku wystarczające.

Point-in-time recovery również posiada dość znaczne ograniczenia. Mianowicie, jeśli przywrócimy bazę do momentu w którym wystąpił dany błąd, również poprawne dane – które byśmy chcieli zachować, zostaną odrzucone. Czas przywracania bazy jest zależny od wielkości baz danych i ilości wprowadzonych zmian. Jeśli natomiast Backup znajduje sie na taśmach, czas przywracania może się jeszcze znacznie bardziej wydłużyć.

Flashback pomaga!

Oracle daje nam w postaci technologii Flashback kilka sposobów, aby móc cofnąć się do przeszłości.

Od Oracle 9i:

  • Flashback (Version) Query.

Od Oracle 10g:

  • Flashback Transaction Query,
  • Flashback Table,
  • Flashback Transaction,
  • Flashback Drop,
  • Flashback Database.

Od Oracle 11g:

  • Flashback Transaction Backout,
  • Flashback Data Archive.

Działają one znacznie szybciej niż klasyczny point-in-time recovery (PITR). Działają na poziomie transakcji, tabeli oraz bazy. Wiele typowych błędów może zostać naprawionych całkowicie bez użycia PITR. Również znalezienie dokładnego punktu w czasie, w którym wystąpił błąd, nie stanowi większego problemu.

Konfiguracja bazy danych

Aby Flashback działał prawidłowo, należy włączyć automatyczne zarządzanie segmentami wycofania (parametr UNDO_MANAGEMENT=AUTO). Następnie ustawiamy czas, przez jaki będą przechowywane segmenty wycofania. Domyślnie jest to 900 czyli 15 min. Należy pamiętać, że dopiero ustawienie parametru RETENTION GUARANTEE zagwarantuje nam, że segmenty te będą faktycznie przez taki czas przechowywane. W innym przypadku, jeśli rozmiar undo tablespace będzie ustawiony na zbyt małą wartość, inna transakcja może nadpisać nam zawartość segmentów wycofania. Jednak i tu trzeba być ostrożnym, gdyż ustawione RETENTION GUARANTEE przy zbyt małej undo tablespace spowoduje, że instancja zostanie wstrzymana – podobnie jak w przypadku problemów z procesami archiwizatora!

Flashback Query

Jak wyglądały dane dziś rano? Jakie zmiany na danych zostały dziś rano wykonane? Kiedy dokładnie zostało wykonane błędne polecenie SQL? Jak można te zmiany z powrotem cofnąć? Jeśli szukamy odpowiedzi na powyższe pytania, sięgnijmy po Flashback Query. Używamy go przez zastosowanie w zapytaniu select klauzuli as of, która służy do sprawdzenia stanu tabeli w momencie wskazywanym przez SCN, bądź przez określony punkt w czasie. Aby zatem odpowiedzieć na pierwsze pytanie stosujemy przykładowe zapytanie, które zwróci nam stan tabeli z godziny 7 rano:

SQL> select * from dept
AS OF timestamp to_timestamp('04-11-2009 07:00:00','dd-mm-yyyy hh24:mi:ss');
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

Flashback Version Query zapewni nam wyższy poziom szczegółowości, niż użycie klauzuli as of. Mianowicie, w naszym przypadku pozwoli nam odpowiedzieć na drugie pytanie, czyli jakie dane zostały zmienione. Flashback Version Query zwraca historię danej tabeli między podanymi SCN lub danym przedziale czasowym. Prześledźmy poniższy przykład:

Sprawdzamy aktualny SCN:

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  584603

Zmiana danych w tabeli:

update dept set loc='WARSZAWA' where deptno=10;
1 row updated.
SQL> commit;

Dodanie nowego wiersza:

SQL> insert into dept values (50,'MARKETING', 'KRAKOW');
1 row created.
SQL> commit;
Commit complete.

Sprawdzenie SCN po zmianach:

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  584648

Używając klauzuli version between zapytanie zwróci nam wszystkie zmiany dokonane na danych w tabeli. Można również dodać szereg tzw. pseudokolumn, aby uzyskać więcej szczegółów:

SQL> select versions_startscn startscnt, versions_endscn endscn, versions_xid xid, versions_operation oper, deptno, dname, loc
from scott.dept versions between scn 584603 and 584648;
STARTSCNT  ENDSCN              XID O  DEPTNO DNAME       LOC
---------- ------ ---------------- -- ------ ----------- ---------------
584647            0A00010041020000 I  50     MARKETING   KRAKOW
584619            090011001A020000 U  10     ACCOUNTING  WARSZAWA
           584619                     10     ACCOUNTING  NEW YORK
                                      20     RESEARCH    DALLAS
                                      30     SALES       CHICAGO
                                      40     OPERATIONS  BOSTON

Pseudokolumny:

VERSIONS_STARTTIME Początkowy timestamp danej wersji
VERSIOSNS_ENDTIME Końcowy timestamp danej wersji
VERSIONS_STARTSCN Początkowy SCN danej wersji
VERSION_ENDSCN Końcowy SCN danej wersji
VERSIONS_XID Identyfikator transakcji
VERSIONS_OPERATION Przeprowadzona operacja I-insert, U-update, D-delete

Flashback Transaction Query

Gdy udało nam się już zidentyfikować nieprawidłową zmianę w tabeli, możemy dowiedzieć się, jaka transakcja jest za to odpowiedzialna i jaki użytkownik tę transakcję wykonał. Posłuży nam do tego Flashback Transaction Query. W przeciwieństwie do Flashback Query, funkcja Flashback Transaction Query nie bazuje na tabeli, na której zostały wykonane błędne transakcje, lecz korzysta z widoku słownika danych FLASHBACK_TRANSACTION_QUERY.

SQL> select logon_user, undo_sql
from flashback_transaction_query
where table_owner ='SCOTT' and table_name = 'DEPT'
order by commit_timestamp desc;
LOGON_USER UNDO_SQL
---------- --------------------------------------------------------------------------------
KOWALSKI update "SCOTT"."DEPT" set "LOC" = 'NEW YORK' where ROWID = 'AAAQ+JAAEAAAAAPAAA';KOWALSKI insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('50','MARKETING','WARSZAWA');

Flashback Transaction Backout

Czy nie byłoby pięknie móc cofnąć nawet zatwierdzone transakcje wraz z ich wszystkimi zależnościami, bez konieczności zmiany trybu bazy na offline? W Oracle 11g jest to możliwe i to za pomocą pojedynczego kliknięcia! Flashback Transaction Backout bazuje na segmentach wycofania, jak również na archived redo logs. Zanim zaczniemy stosować Flashback Transaction Backout należy włączyć dodatkowy poziom logowania (supplemental logging):

SQL> alter database add supplemental log data;

Flashback Transaction Backout można zarządzać poprzez Database Control jak i procedurę TRANSACTION_BACKOUT.

Procedura Transaction Backout zawiera następujące parametry:

  • NUMBEROFXIDS – ilość transakcji do wycofania;
  • XIDS – numery identyfikujące dane transakcje przekazane w postaci wektora;
  • OPTIONS – zależności odnośnie transakcji będących rodzicem i potomkiem;
  • NOCASCADE – opcja domyślna – używamy, jeśli nie przewidujemy żadnych zależności między transakcjami;
  • CASCADE – wycofuje najpierw zależne transakcje;
  • NOCASCADE_FORCE – wycofuje główne transakcje (rodzica) i ignoruje wszystkie inne;
  • SCNHINT – początkowy numer SCN danej transakcji.

Poniższy przykład demonstruje wycofanie transakcji wraz z transakcją jej podlegającą za pomocą pojedynczego wykonania.

declare
trans_arr xid_array;
begin
trans_arr := xid_array('030003000D02840','D10001000D02850');
dbms_flashback.transaction_backout (
numtxns => 1,
xids => trans_arr,
options => dbms_flashback.nocascade
);
end;

Flashback Table

Funkcja Flashback Table umożliwia odtworzenie stanu tabeli z określonego momentu w przeszłości bez potrzeby sięgania do backupu. Wywołując Flashback Table nie tylko przywrócimy wiersze w tej tabeli, lecz również indeksy, więzy integralności, wyzwalacze. Powyższą metodę stosujemy wówczas, gdy zakres błędów jest stosunkowo niewielki, tzn. ogranicza się do pojedynczej tabeli, bądź też co najwyżej kilku tabel.

Przed zastosowaniem Flashback Table trzeba włączyć możliwość zmiany numeru wierszy – ROWID (alter table … enable row movement). Następnym krokiem jest nadanie prawa FLASHBACK ANY TABLE. Użytkownik wykonujący funkcję Flashback Table musi również posiadać prawa select, insert, delete oraz alter table na docelowej tabeli. Aby dokładnie określić moment, do jakiego chcemy przywrócić tabelę możemy użyć wcześniej omówione Flashback Query i Flashback Transaction Query.

Poniższe polecenie przywróci nam stan tabeli sprzed pół godziny:

SQL> flashback table emp to timestamp systimestamp - interval '30' minute;
Flashback complete.

Stosowanie Flashback Table jest dość proste, funkcję tę możemy również wywołać z Enterprise Managera. W obu przypadkach należy jednak pamiętać o pewnych ograniczeniach. Flashback Table nie można stosować:

  • Na tabelach systemowych
  • Na zmaterializowanych widokach
  • Po wykonaniu polecenia shrink
  • Po poleceniach DDL:
    • alter table … drop column
    • alter table … drop partition
    • create cluster
    • truncate table
    • alter table move

Jeśli nie zastosujemy się do tych ograniczeń, otrzymamy błąd ORA-01445: Unable to read data – table definition Has changed.

Flashback Drop

W wersjach wcześniejszych niż Oracle 10g, wywołanie polecenia drop table na stałe usuwało tabelę z bazy, a jedynym sposobem na jej przywrócenie było wykonanie point-in–time recovery. Od wersji 10g obiekty nie są natychmiastowo usuwane. System wygeneruje dla danego obiektu i obiektów mu podlegających (indeksy, więzy integralności, wyzwalacze) nową nazwę, lecz fizycznie pozostaną one tymczasowo w tym samym miejscu. Możemy się odwołać do tych obiektów poprzez „kosz” (recycle bin). Rozwiązanie to pozwala na przywrócenie omyłkowo skasowanej tabeli. Flashback Drop w odróżnieniu od Flashback Table nie opiera się na segmentach wycofania (undo), lecz wykorzystuje mechanizm recycle bin.

Na początek sprawdzamy czy funkcja recycle bin jest włączona:

SQL> show parameter recyclebin
NAME       TYPE    VALUE
---------- ------- ---------
recyclebin string  on

Usuwamy tabelę:

          SQL> drop table scott.emp;
          Table dropped.
          SQL> select * from scott.emp;
          select * from scott.emp
          
 *
          ERROR at line 1:
ORA-00942: table or view does not exist

Przeglądamy nowy wpis w recycle bin:

SQL> select object_name, original_name, ts_name, droptime from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME TS_NAME DROPTIME
------------------------------ ------------- ------- -------------------
BIN$d7A4q8DPGxLgQKjADwod9w==$0 EMP           USERS   2009-11-06:08:59:20

Można również użyć polecenia:

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT         TYPE   DROP TIME
------------- ------- ---------------------- ------ --------------------
EMP           BIN$d7A4q8DPGxLgQKjADwod9w==$0 TABLE  2009-11-6:08:59:20

Przywracanie skasowanej tabeli:

SQL> flashback table scott.emp to before drop;
Flashback complete
SQL> select * from recyclebin;
no rows selected

Przywracanie ze zmianą nazwy tabeli:

SQL> flashback table flashback_test to before drop  rename to flashback_test_1;
Flashback complete.

Opróżnianie recycle bin:

  • Purge recyclebin – dla aktualnego użytkownika
  • Purge table <nazwa_tabeli> – usuwa tabelę na stałe
  • Purge index <nazwa indeksu> – usuwa indeks
  • Purge tablespace <nazwa_ts> – opróżnia kosz w podanej przestrzeni tabel
  • Purge dba_recyclebin – opróżnia cały kosz

Kasowanie tabeli z pominięciem kosza:

Drop table <nazwa_tabeli> purge

Recycle bin posiada pewne ograniczenia. Obiekty będą w nim przechowywane, dopóki jest wystarczająca ilość miejsca. Jeśli miejsca jest mało, obiekty będą kasowane zaczynając od najstarszego. Funkcja autoextend również zacznie od opróżnienia recycle bin. Jego rozmiar i czas przechowywania obiektów zależy tylko od ilości wolnego miejsca w przestrzeni tabel. Nie jest możliwe korzystanie z jego funkcji w systemowej przestrzeni tabel.

Flashback Database

Wcześniej opisane funkcje Flashback operowały na poszczególnych obiektach. Flashback Database umożliwia przywrócenie całej bazy do określonego momentu z przeszłości i odbywa się to zdecydowanie szybciej, niż innymi dostępnymi metodami. Jest on bardzo użyteczny w przypadku logicznych błędów, na skutek których zostały zmienione duże ilości danych.

Aby móc skorzystać z Flashback Database należy najpierw wykonać poniższe polecenie na bazie w trybie mount:

SQL> alter database flashback on;
Database altered.

Następnie ustawiamy poniższe parametry:

DB_RECOVERY_FILE_DEST – ustala obszar szybkiego odtwarzania FRA (Flash Recovery Area), w którym przechowywane będą między innymi logi flashback; DB_RECOVERY_FILE_DEST_SIZE – rozmiar FRA;

DB_FLASHBACK_RETENTION_TARGET – ustala górny limit wyrażony w minutach, do jakiego możemy cofnąć bazę danych.

Kolejnym założeniem jest, aby baza pracowała w trybie archivelog.

Flashback database tworzy dodatkowe logi, które są niezbędne do odtworzenia bazy. Za ich zapisywanie odpowiedzialny jest nowy proces RVWR (Recovery Writer Process).

Gdy mamy już poprawnie skonfigurowaną bazę, możemy wykonać zapytanie na widoku V$FLASBHACK_DATABASE_LOG, aby określić, do jakiego momentu możemy przywrócić bazę danych:

SQL> select * from v$Flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
------------------------------------------------------------------------------
655767              06-11-2009      09:58            1440        16384000

Aby przywrócić bazę danych do żądanego okresu należy posiadać uprawnienia sysdba i uruchomić bazę w trybie mount:

SQL> flashback database to timestamp to_date('06-11-2009 10:15');
Flashback complete.

Po ukończeniu operacji otwieramy bazę z opcją resetlogs:

SQL> alter database open resetlogs;
Database altered.

Od wersji Oracle 10.2 mamy możliwość tworzenia tak zwanych punktów przywracania, które są niczym innym jak aliasem do SCN:

SQL> create restore point przed_testem;
Restore point created.
FLASHBACK DATABASE ...
... TO (SCN|TIMESTAMP);
... TO Before RESETLOGS;
... TO RESTORE POINT <RESTORE_POINT_NAME)

Wynik zastosowania Flashback Database jest taki sam jak w przypadku tradycyjnego point-in-time recovery. Jednakże jest on dużo prostszy w użyciu, a przy tym wymaga znacznie mniej czasu.

W jakich przypadkach Flashback Database nam nie pomoże?

Flashback Database nie da się zastosować, gdy usunięta zostanie przestrzeń tabel. Również, gdy plik danych zostanie pomniejszony lub gdy stworzymy nowe pliki kontrolne. Jeżeli w zarchiwizowanych dziennikach oraz obszarze FRA nie ma wystarczającej ilości danych, w celu przywrócenia danych należy zastosować tradycyjne metody odzyskiwania.

Flashback Data Archive

Dotychczas opisane funkcje flashback sprawdzają się doskonale w przypadku, gdy chcemy się dowiedzieć jak wyglądały dane w niedalekiej przeszłości. W zależności od wielkości przestrzeni tabel i parametrów – takich, jak UNDO RETENTION, bądź też DB_FLASHBACK_RETENTION_TARGET, mogliśmy mieć dostęp do danych sprzed kilku do kilkunastu godzin. Nasuwa się więc pytanie: jak śledzić zmiany danych na przestrzeni lat? Z pomocą idzie nam najnowsze narzędzie z rodziny Flashback dostępne od wersji Oracle 11g, Flashback Data Archive. Narzędzie to zapisuje zmiany DML danej tabeli przez zdefiniowany okres czasu. W tym celu Flashback Data Archive tworzy nową wewnętrzną tabelę, która jest repliką danej tabeli z dodatkowymi kolumnami zawierającymi znacznik czasowy timestamp. Każda operacja update czy też delete na tabeli śledzonej powoduje utworzenie nowego wiersza w tabeli wewnętrznej. Dla uzyskania lepszej wydajności zastosowano tabelę partycjonowaną, która jest również kompresowana, aby zaoszczędzić miejsce na dysku.

Aby rozpocząć pracę z Flashback Data Archive, przestrzeń tabel musi być zarządzana automatycznie (Automatic Segment Space ASSM). Drugim wymaganiem jest automatyczne zarządzanie segmentami wycofania (Automatic Undo Management).

Następnie definiujemy miejsce przechowywania naszych danych historycznych:

SQL> create flashback archive fda1
            2 tablespace tbs1
            3 retention 5 year;
Flashback archive created.

Za pomocą tego polecenia utworzyliśmy logiczny kontener na nasze dane, które przechowywane będą przez okres 5 lat. Opcjonalnie możemy określić parametr quota czyli limit miejsca na dysku przeznaczony na archiwizacje.

Podajemy, dla jakiej tabeli chcemy rejestrować zmiany:

SQL> alter table scott.emp flashback archive fda1;
Table altered.

W celu dostępu do danych historycznych stosujemy konstrukcję „AS OF”:

Select first_name, salary
       from employees
       as fo timestamp to_timestamp('05-01-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Należy pamiętać, że operacje DDL – takie, jak modyfikowanie kolumn, drop, czy truncate są niemożliwe na śledzonej tabeli jedynie w wersji Oracle 11.1. W najnowszej wersji 11.2 ograniczenie to zostało zniesione.

Podsumowanie

Rodzina Oracle Flashback rozrasta się z każdą kolejną wersją bazy danych Oracle. Stanowi ona doskonałe narzędzie do naprawiania błędów logicznych i błędów popełnianych przez użytkowników. Operacje przywracania danych w porównaniu do tradycyjnych metod zostały zredukowane do kilku minut lub też nawet sekund, zachowując w wielu przypadkach wysoką dostępność bazy danych. Trzeba jednak pamiętać, że technologia ta nie pomoże nam w przypadku błędów fizycznych takich jak uszkodzenia dysku. Stosowanie Oracle Flashback wiąże się również – w zależności od zdefiniowanego przedziału czasowego – ze znacznym wzrostem miejsca na dysku wymaganego przez przestrzeń wycofań (UNDO), Flashback logs, czy też Flashback Data Archive. Większość funkcji Flashback dostępnych jest jedynie w wersji Enterprise, a za korzystanie z Flashback Data Archive trzeba dodatkowo zapłacić. Niemniej Oracle Flashback jest bardzo ciekawym i funkcjonalnym rozszerzeniem narzędzi każdego DBA.

Bibliografia:

  1. Oracle Flashback – ein Überblick. Daniel Schulz, Opitz Consulting
  2. Oracle Database10g Podręcznik administratora baz danych. Kevin Loney Bob Bryla
  3. Expert Oracle Database 11g Administration. Sam R. Alapati