Wstęp do Oracle FLASHBACK

ORACLE FLASHBACK to grupa funkcji bazy danych Oracle, które pozwalają przeglądać przeszłe stany obiektów bazy danych lub przywracać obiekty bazy danych do poprzedniego stanu bez korzystania z point-in-time recovery.

W trakcie pracy na bazie danych mogą wystąpić błędy gdy np. usuniemy rekord którego nie chcieliśmy usunąć lub go zmienimy. Dopóki nie wykonamy operacji COMMIT zmianę możemy łatwo odwołać wykonując polecenie ROLLBACK. Co jednak gdy zatwierdzimy transakcję poprzez COMMIT? W tej sytuacji przychodzi nam z pomocą technologia ORACLE FLASHBACK która pozwala przeglądać stare dane oraz metadane.

Ten wpis jest częścią Kurs Oracle SQL

W tym kursie nie opiszę całego mechanizmu jednak pokażę najpopularniejsze funkcjonalności które pozwolą nam przywrócić dane. Wpis ten będzie podzielony na dwie części i tak w tej części opiszę:

• Wstęp i pojęcia
• Konfigurację bazy danych
• Użycie Oracle Flashback Query (SELECT AS OF)
• Użycie Oracle Flashback Version Query

Wstęp

Przede wszystkich technologia ta wykorzystuje specjalne przestrzenie danych takie jak UNDO oraz REDO. Korzysta również z specjalnego znacznika bazy danych SCN.

UNDO TABLESPACE

Undo tablespace jest specjalną przestrzenią danych która służy do przechowywania bloków danych przed zatwierdzeniem ich zmiany oraz jakiś czas po zatwierdzeniu transakcji. Służy do wycofywania transakcji (rollback) i spójności odczytu. Ponadto Oracle używa parametru undo_retention jako minimalnego czasu, w którym dane powinny pozostać w undo po zatwierdzeniu transakcji (commit). Ma to na celu zachowanie spójności odczytu.

REDO LOG

Redo log jest dziennikiem zmian każdej transakcji w trakcie istnienia bazy danych w tym wszystkie zmiany dokonane w ramach undo. Redo log jest wykorzystywany do zapewnienia integralności danych oraz do przywrócenia bazy do dowolnego momentu w czasie.

Różnica między UNDO i REDO jest przede wszystkim taka, że w undo przechowywane są dane przez krótki czas i są to całe bloki danych natomiast w REDO przechowywane są wektory zmian przez cały okres istnienia bazy danych.

SCN

SCN(System Change Number) to numer “zegara/wersji” w bazie danych który jest zwiększany za każdym razem, gdy zatwierdzamy transakcję, wyznacza spójny moment w bazie danych.

Aby sprawdzić aktualny SCN należy wykonać polecenie:

SELECT
    DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
FROM DUAL;

W moim przypadku wynik to: 3896992 i po każdej transakcji wartość ta wzrasta.

Konfiguracja bazy danych

Aby mieć pewność, że Flashback będzie działał poprawnie musimy mieć ustawione kilka parametrów na bazie:

• Parametr undo_management ustawiony na wartość AUTO
• Parametr undo_retention na odpowiednią wartość, domyślnie 900. Parametr ten wskazuje jak długo baza powinna przetrzymywać dane w undo. Prezentowana wartość jest jednostką sekund.
• Mieć utworzoną przestrzeń tabel undo_tablespace wraz z parametrem RETENTION na wartość GUARANTEE

Aby sprawdzić parametry należy wykonać poniższe zapytanie:

SELECT
    NAME
  , VALUE
  , DEFAULT_VALUE
  , DESCRIPTION
FROM V$PARAMETER
WHERE 1 = 1
  AND NAME IN ('undo_management'
             , 'undo_retention'
             , 'undo_tablespace');

Sprawdzenie UNDO_TABLESPACE:

SELECT
     TABLESPACE_NAME
   , RETENTION
 FROM DBA_TABLESPACES
 WHERE 1 = 1
   AND TABLESPACE_NAME = 'UNDOTBS2';

Aby zmieć parametr RETENTION na GUARANTEE należy wykonwać polecenie:

ALTER TABLESPACE UNDOTBS2 RETENTION GUARANTEE;

Należy uważać na ten parametr ponieważ w sytuacji gdy zabraknie miejsca w przestrzeni tabel UNDO może dojść do wyłączenia bazy danych. Jeżeli nie mamy pewności pozostawny parametr z wartością NOGUARANTEE. Wartość NOGUARANTEE spowoduje, że w sytuacji gdy zacznie brakować miejsca w przestrzeni UNDO zatwierdzone dane zostaną usunięte wcześniej niż wskazuje parametr undo_retention.

Uprawnienia

Warto nadać użytkownikowi dwa uprawnienia do korzystania z pakietu DBMS_FLASHBACK oraz do wykonywania operacji FLASHBACK na wszystkich tabelach:

GRANT FLASHBACK ANY TABLE TO FOO;
GRANT EXECUTE ON DBMS_FLASHBACK TO FOO;

Oracle Flashback Query (SELECT AS OF)

Gdy mamy już skonfigurowaną bazę danych możemy przystąpić do testów ORACLE FLSHBACK QUERY. Wykorzystujemy do tego celu klauzulę AS OF wraz z parametrem czasowym lub SCN.

Aby sprawdzić działanie mechanizmu Najpierw jednak przygotujmy dane testowe:

CREATE TABLE TEST_TABLE (
    ID NUMBER GENERATED ALWAYS AS IDENTITY
  , FOO VARCHAR2(255)
  , BAR NUMBER
  , CONSTRAINT ID_PK PRIMARY KEY (ID)
);

INSERT INTO TEST_TABLE (FOO, BAR) VALUES ('ADAM', 500);
INSERT INTO TEST_TABLE (FOO, BAR) VALUES ('BARBARA', 1000);
INSERT INTO TEST_TABLE (FOO, BAR) VALUES ('JAN', 600);
INSERT INTO TEST_TABLE (FOO, BAR) VALUES ('PIOTR', 65);
INSERT INTO TEST_TABLE (FOO, BAR) VALUES ('OLA', 333);
COMMIT;

W powyższym przykładzie utworzyliśmy zwykłą tabelę z kolumnami ID, FOO, BAR oraz wstawiliśmy do tej tabeli 5 rekordów.

Wartość parametru po dodaniu danych SCN: 3897041

Zobaczmy więc jak wyglądają w tym momencie dane w tabeli:

Widzimy, że wszystko poszło zgodnie z planem więc usuńmy rekord z wartością “PIOTR” i zobaczmy czy uda nam, się ją odzyskać.

DELETE FROM TEST_TABLE
 WHERE 1 = 1
   AND FOO = 'PIOTR';
 COMMIT;

Wartość parametru SCN: 3897514

Zobaczmy jak teraz wyglądają dane w tabeli:

Jak widać, skutecznie usunęliśmy z tabeli rekord z wartością FOO=’PIOTR’.

Na pewno każdemu zdażyło się usunąć dane których wcale nie chcieliście usuwać lub zmodyfikować dane tak jak nie chciał. Zazwyczaj sytuacja taka generuje dużo stresu szczególnie jeżeli zrobiliście to na produkcji i dane które usunęliście były ważne 🙂 Na szczęście istnieje możliwość podejrzenia stanu tabeli w wskazanym momencie czasowym (nie tylko po przywrócenie backupu) więc wykonajmy polecenie SELECT wraz z klauzulą AS OF:

SELECT
    *
FROM TEST_TABLE
AS OF TIMESTAMP TO_TIMESTAMP('19-10-2019 10:03:00','dd-mm-yyyy hh24:mi:ss');

Oraz wynik:

W wyniku powyższego zapytania znów widzimy rekord z wartością “Piotr”. Co więc tak naprawdę widzimy? Najprościej można powiedzieć, że widzimy stan tabeli TEST_TABLE na moment 19-10-2019 10:03:00. A widzimy ją ponieważ baza danych przechowuje wszystkie zmiany jakie na niej wprowadziliśmy. Skoro więc mamy dostęp do usuniętych czy zmodyfikowanych rekordów możemy je przywrócić, najprościej poprzez ponowne zainsertowanie usuniętego rekordu.

Dzięki możliwości otrzymania stanu danych w tabeli w dowolnym momencie możemy zobaczyć różnicę między rekordami w czasie np. poprzez zastosowanie operacji MINUS:

SELECT
    *
FROM TEST_TABLE
AS OF TIMESTAMP TO_TIMESTAMP('19-10-2019 11:01:00','dd-mm-yyyy hh24:mi:ss')
MINUS
SELECT
    *
FROM TEST_TABLE;

Po zmianie struktury tabeli (np. usunięcie kolumny) nie mamy możliwości otrzymania danych sprzed zmiany przy pomocy AS OF. Otrzymamy błąd ORA-01466:

ORA-01466: unable to read data - table definition has changed
01466. 00000 -  "unable to read data - table definition has changed"
 *Cause:    Query parsed after tbl (or index) change, and executed
        w/old snapshot
 *Action:   commit (or rollback) transaction, and re-execute 

Oracle Flashback Version Query

Zapytanie Oracle Flashback Version Query zwraca tabelę z wierszem dla każdej wersji wiersza, która istniała w dowolnym momencie w określonym przedziale czasu. Każdy wiersz w tabeli zawiera pseudokolumny metadanych dotyczące wersji wiersza. Dane te są przechowywane w UNDO tablespace dlatego też czas ich istnienia określony jest parametrem undo_retention. Istnieje możliwość uzyskania dodatkowych metadanych trzymanych w pseudokolumnach:

Version Query używamy z ograniczeniem czasowym lub scn. Zamiast konkretnych wartości SCN istnieje możliwość podania wartości MINVALUE oraz MAXVALUE

Zobaczmy więc w praktyce jak użyć oraz co pokaże nam Version Query:

SELECT
    ID
  , FOO
  , BAR
  , VERSIONS_STARTSCN
  , VERSIONS_STARTTIME
  , VERSIONS_ENDSCN
  , VERSIONS_ENDTIME
  , VERSIONS_XID
  , VERSIONS_OPERATION
FROM TEST_TABLE
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

Oraz wynik powyższego zapytania:

Gdy spojrzymy na wyniki powyższego zapytania to nie znajdziemy w nich nic ciekawego. Wszystkie pseudo kolumny są nullami a na dodatek nie ma rekordu “Piotr”. VERSION QUERY korzysta z przestrzeni tabel UNDO której czas przechowywania na mojej bazie ustawiona jest na 900s czyli 15min a usunięcie rekordu “Piotr” wykonałem więcej niż 15 min. temu. Po tym czasie dane z UNDO są usuwane żeby nie zajmować niepotrzebnie miejsca dlatego też nie widzimy reordu “Piotr” oraz nie widzimy wartości w pseudo kolumnach.

Zróbmy więc nowe operacje na tabeli np.:

INSERT INTO TEST_TABLE (FOO, BAR) VALUES ('JAROSLAW', 500);
COMMIT;
UPDATE TEST_TABLE SET BAR = 1500 WHERE FOO = 'JAROSLAW';
DELETE FROM TEST_TABLE WHERE FOO = 'JAN';
COMMIT;

Tak więc dodajemy nowy rekord “JAROSLAW” następnie go zmieniamy i usuwamy rekord “JAN”. Zobaczmy więc wynik dla zapytania ale tym razem przy użyciu timestampa. Nie ma znaczenia czy ograniczamy wynik timestampem czy przy pomocy SCN:

SELECT
    ID
  , FOO
  , BAR
  , VERSIONS_STARTSCN
  , VERSIONS_STARTTIME
  , VERSIONS_ENDSCN
  , VERSIONS_ENDTIME
  , VERSIONS_XID
  , VERSIONS_OPERATION
FROM TEST_TABLE
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('19-10-2019 11:38:00','dd-mm-yyyy hh24:mi:ss')
  AND TO_TIMESTAMP('19-10-2019 11:42:00','dd-mm-yyyy hh24:mi:ss');

Tu już widzimy sporo a dokładniej widać:

Wiersz 3 -> Moment kiedy rekord “JAROSLAW” został dodany i jak długo obowiązywał. Jako, że insert i update rekordu zrobiłem w tym samym czasie to rekord “JAROSLAW” z BAR=500 nie obowiązywał w zasadzie wcale ponieważ data starttime oraz endtime są sobie równe natomiast widać nieznaczną różnicę w SCN.

Wiersz 2 -> Aktualizacja rekordu “Jarosław”, w operacji widzimy U co oznacza Update oraz widzimy, że rekord nie ma wyznaczonego końca co oznacza, że na moment wykonania zapytania z Versions nie został zmieniony.

Wiersz 1 i 6 -> Usunięcie rekordu “JAN”, w operacji D co oznacza Delete oraz widać zakres obowiązywania każdego z rekordów.

Wiersz 4,5,7 -> Same nulle co oznacza, że w ciągu ostatnich 15 min. nic z rekordami tymi się nie zadziało.

Dzięki zastosowaniu Oracle Flashback Version Query jesteśmy w stanie odnaleźć wszystkie zmiany na rekordach jakie miały miejsce w przeciągu ostatnich minut. W zależności od ustawienia parametru undo_retention.

Podsumowanie

• Gdy usuniemy lub zmienimy dane których usunąć/zmienić nie chcieliśmy, nie panikujemy 🙂
• Oracle nie usuwa “trwale” naszych danych
• Technologia Oracle Flashback pozwala na odzyskanie i podgląd zatwierdzonych zmian
• Aby Oracle Flashback działał musimy odpowiednio skonfigurować bazę danych
• Flashback Query pozwala nam sprawdzić stan tabeli w dowolnym momencie
• Flashback Version Query pozwala sprawdzić wszystkie zmiany jakie zaszły na rekordach w ciągu ostatnich chwil.

W kolejnej części opiszę szerzej działanie oraz wykorzystanie technologii Oracle Flashback. Pokażę jak znaleźć użytkownika który usuną/dodał/zmienił dany rekord oraz jak przywrócić tabelę.