Podróż w czasie, czyli Oracle Flashback

Paweł Barut
Pawel@Barut.info

Baza danych Oracle od wersji 9i udostępnia niezwykły mechanizm do podróży w czasie. Mechanizm ten, nazwany Flashback, umożliwia odczyt danych w wersji, jaka była w przeszłości. Czy nie zdarzyło się wam uruchomić proces modyfikacji danych, zatwierdzić zmiany i zastanawiać się co tak naprawdę zostało zmienione? Jakie dane były przed wykonaniem modyfikacji? W odpowiedzi na te pytanie pomoże właśnie mechanizm Flashback Query.

Zaczynamy, czyli konfiguracja bazy danych

Mechanizm Oracle Flashback działa prawidłowo przy włączonym mechanizmie automatycznego zarządzania segmentem UNDO (Automatic Undo Management, parametr UNDO_MANAGEMENT=AUTO). Przy tradycyjnych segmentach wycofania użycie Flashback też jest możliwe, ale w praktyce ogranicza się do cofnięcia się do danych sprzed kilku minut.

Następnie należy zadbać, aby dane z segmentów wycofania były przechowywane przez odpowiednio długi czas. W tym celu ustawiamy parametr bazy danych UNDO_RETENTION na odpowiednio dużą wartość, np. 43200 sekund, czyli 12 godzin. Należy pamiętać, że ustawienie dużej wartości może wymagać odpowiednio większej przestrzeni wycofań (UNDO TABLESPACE). Wielkość potrzebnej przestrzeni jest zależna od intensywności zmian w bazie danych, i należy ją określić na podstawie empirycznie zmierzonego współczynnika:

select sum(UNDOBLKS)/ (sum(END_TIME-BEGIN_TIME)*24) blokow_na_godzine from v$undostat where BEGIN_TIME >= :p_start and END_TIME <= :_p_stop

gdzie za p_start i p_stop należy podstawić okres największej intensywności pracy bazy danych.

Wtedy minimalną wielkość przestrzeni UNDO można wyliczyć ze wzoru:

UNDO_SIZE = blokow_na_godzine * (UNDO_RETENTION/3600)* DB_BLOCK_SIZE + margines_bledu

Margines błędu musi być na tyle duży, aby w przypadku niespodziewanego wzrostu intensywności generacji informacji wycofań, nie nastąpiło przepełnienie przestrzeni UNDO.

Potrzebne uprawnienia

Jeśli nie jesteś właścicielem tabeli, na której wykonywana będzie operacja odczytu danych historycznych, to musisz otrzymać od administratora bazy przywilej FLASHBACK dla danej tabeli. Możliwe jest również otrzymanie prawa do wykonania operacji Flashback na wszystkich tabelach bazy, czyli przywilej FLASHBACK ANY TABLE.

Jeśli dodatkowo zamierzasz korzystać z pakietu DBMS_FLASHBACK, to musisz uzyskać prawo do wykonania funkcji z tego pakietu. Pakiet ten znajduje się
w schemacie SYS'a.

DBMS_FLASHBACK

Podstawowym sposobem korzystania z mechanizmu Flashback Query jest przełączenie sesji w tryb Flashback. Przełączenia trybu sesji dokonuje się korzystając z procedur zawartych w pakiecie DBMS_FLASHBACK:

enable_at_time (query_time in TIMESTAMP)	Włącza tryb Flashback, przywracając stan danych na godzinę określoną w parametrze query_time
enable_at_system_ change_number (query_scn in NUMBER)	Włącza tryb Flashback, przywracając stan danych do zmiany opatrzonej numerem SCN (System Change Number) podanym w parametrze query_scn

Procedura enable_at_time w rzeczywistości dokonuje przemapowania podanego czasu na numer zmiany SCN i wykonanie enable_at_system_change_number. Mapowanie czasu na SCN można odczytać z tabeli systemowej SMON_SCN_TIME:

SELECT SCN_BAS, to_char(TIME_DP, 'YYYY-MM-DD HH24:MI:SS') TIME_DP from sys.SMON_SCN_TIME where TIME_DP > sysdate-0.1 order by SCN_BAS;

SCN_BAS TIME_DP -------------- ----------------------------- 8113055 2004-07-13 23:17:11 8114349 2004-07-13 23:22:21 8115568 2004-07-13 23:27:28 8116821 2004-07-13 23:32:36 8118282 2004-07-13 23:38:19 8119575 2004-07-13 23:43:26

...

Mapowanie jest wykonywane z dokładnością do 5 minut, więc podanie czasu 23:23 i 23:26 powoduje mapowanie do tego samego numeru SCN. Jeżeli wiemy, że zmiana nastąpiła o 23:24, a następnie o 23:26 dane zostały ponownie zmienione, to używając procedury enable_at_time, nie będziemy w stanie cofnąć się do godziny 23:25. W takim przypadku należy poeksperymentować z różnymi numerami SCN z przedziału 8114349 - 8115568. Dlatego, o ile to możliwe, należy korzystać bezpośrednio z numeru SCN i procedury enable_at_system_change_number, a mapowania czasu na numer SCN wykonać samemu na podstawie zawartości tabeli SMON_SCN_TIME. Procedura enable_at_time jest za to przydatna do oszacowania, kiedy nastąpiła zmiana, która jest dla nas interesująca.

Włączenie trybu Flashback przy użyciu pakietu DBMS_FLASHBACK powoduje, że niemożliwe jest wykonanie jakiejkolwiek operacji modyfikującej zawartość danych w bazie. Niemożliwe jest także wykonanie jakiejkolwiek operacji DDL.

Zobaczmy jak to działa w praktyce.

Na początek zapiszmy aktualny czas i SCN:

SQL> select to_char(sysdate, 'HH24:MI:SS') czas, dbms_flashback.get_system_change_number SCN from dual

CZAS SCN ------------ ------------------- 23:29:15 8115934

Utwórzmy tabelę testową:

create table demo_flashback (liczba number not null ,tekst varchar2(20) ,utworzony date ,zmieniony date);

i wyzwalacz zapisujący czas utworzenia i ostatniej zmiany:

create or replace trigger demo_flash_trg before insert or update on demo_flashback for each rowbegin if inserting then :new.utworzony := sysdate; :new.zmieniony := null; end if; if updating then :new.utworzony := :old.utworzony; - bug w wersji 9.2.0.1 :new.zmieniony := sysdate; end if; end; /

Wstawmy kilka wierszy to tej tabeli:

insert into demo_flashback (liczba, tekst) select rownum, 'WIERSZ'||rownum from all_objects where rownum <=5; commit;

Zapiszmy teraz aktualną wartość SCN (oznaczmy ją jako SCN01):

SQL> select to_char(sysdate, 'HH24:MI:SS') czas, dbms_flashback.get_system_change_number SCN from dual

CZAS SCN ------------ ------------------ 23:45:15 8119953

Zmieniamy kilka wierszy, np.:

update demo_flashback set tekst = 'NOWA WARTOSC' where liczba <= 2; commit;

Po wykonaniu tej operacji zupełnie nie wiemy, co było początkowo wstawione do tabeli.

Sprawdzamy, co zawiera tabela:

SQL>select * from demo_flashback;

LICZBA TEKST UTWORZON ZMIENION ----------- ----------------------- ------------------ ----------------

1 NOWA WARTOSC 04/07/13 04/07/14 2 NOWA WARTOSC 04/07/13 04/07/14 3 WIERSZ3 04/07/13 4 WIERSZ4 04/07/13 5 WIERSZ5 04/07/13

Odtwórzmy stan tabeli przed operacją update.

Włączamy tryb Flashback podając SCN przed operacją wstawienia danych:

begin dbms_flashback.enable_at_system_change_number(8119953); end; /

Sprawdzamy, co zawiera tabela:

SQL>select * from demo_flashback;

LICZBA TEKST UTWORZON ZMIENION ---------- ------------- ------------ ---------------- 1 WIERSZ1 04/07/13 2 WIERSZ2 04/07/13 3 WIERSZ3 04/07/13 4 WIERSZ4 04/07/13 5 WIERSZ5 04/07/13

Jak widać, mamy zawartość tabeli sprzed wykonania operacji Update.

Spróbujmy zatem wykonać kopię odtworzonych danych:

SQL> create table kopia as select * from demo_flashback; create table kopia * BŁĄD w linii 1: ORA-08182: operacja nie jest obsługiwana w trybie Flashback

Niestety operacje zmieniające dane, jak i operacje DDL nie są możliwe, gdy baza pracuje w trybie Flashback. Wyłączamy zatem tryb Flashback:

begin dbms_flashback.disable; end; /

I tabela powraca do aktualnego stanu:

SQL>select * from demo_flashback;

LICZBA TEKST UTWORZON ZMIENION ---------- ------------- ------------ ---------------- 1 NOWA WARTOSC 04/07/13 04/07/14 2 NOWA WARTOSC 04/07/13 04/07/14 3 WIERSZ3 04/07/13 4 WIERSZ4 04/07/13 5 WIERSZ5 04/07/13

Powstaje zatem pytanie: Jak skorzystać z trybu Flashback do odzyskania danych, skoro nie można ich nigdzie skopiować? Przecież nie sposób przepisać wszystkich danych na kartce papieru i potem "wklepać" z powrotem do bazy danych!

Jest na to sposób: kursor, który jest otwarty podczas aktywnego trybu Flashback, pozostanie w trybie Flashback nawet po wyłączeniu trybu Flashback dla sesji. Tak więc teraz już będzie prosto - utworzymy najpierw tabelę, do której skopiujemy dane:

create table kopia as select * from demo_flashback where rownum <1;

i wykonamy kopiowanie:

declare cursor c_flash is select * from demo_flashback; r demo_flashback%rowtype;begin - - Włączamy tryb flashback dbms_flashback.enable_at_system_change_number(8119953); - - otwieramy kursor open c_flash; - - wyłączamy tryb flashback, - - kursor c_flash pozostaje w trybie flashback ! dbms_flashback.disable; fetch c_flash into r; while c_flash%found loop - - Kopiowanie insert into kopia values r; fetch c_flash into r; end loop; commit; end; /

Teraz możemy już w jednym zapytaniu porównać dane:

select nvl(d.liczba, k.liczba) liczba, k.tekst stary, d.tekst nowy from demo_flashback d FULL OUTER JOIN kopia k ON d.liczba = k.liczba where k.tekst <> d.tekst;

LICZBA STARY NOWY ---------- ------------ ----------------- 1 WIERSZ1 NOWA WARTOSC 2 WIERSZ2 NOWA WARTOSC

Wygląda to skomplikowanie? Można taki sam efekt osiągnąć prościej.

SELECT AS OF TIMESTAMP/SCN

W poprzednim przykładzie operacja wykonania kopii utraconych danych i porównania z danymi aktualnymi wymagała wykonania kilku operacji. Ten sam efekt można uzyskać wykonując jedno zapytanie. Składnia instrukcji SELECT w Oracle9i została rozszerzona o klauzulę AS OF [TIMESTAMP|SCN]. Podając w klauzuli FROM tabelę, można dla niej określić moment czasu (lub numer zmiany SCN) wg. którego będą wybierane dane.

Tak więc porównanie danych aktualnych z danymi sprzed zmiany można zrealizować jednym zapytaniem:

select nvl(d.liczba, k.liczba) liczba, k.tekst stary, d.tekst nowy from demo_flashback d FULL OUTER JOIN demo_flashback as of scn 8119953 k ON d.liczba = k.liczba where k.tekst <> d.tekst;

Jeśli natomiast chcielibyśmy wykonać kopię tabeli z poprzednimi danymi, to wykonujemy:

create table kopia as select * from demo_flashback as of scn 8119953;

Przypuśćmy, że ktoś usunął wszystkie dane z tabeli i chcemy je odtworzyć. W tym celu należy najpierw odnaleźć dokładny SCN lub czas, dla którego dane jeszcze występują.

Spróbujmy to zasymulować:

SQL>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER ------------------------- 8151460

Usuńmy wszystkie dane:

delete from demo_flashback; commit;

Usuwanie musimy wykonać poprzez operację DELETE, gdyż TRUNCATE TABLE powoduje, że nie jest generowana informacja o sposobie wycofania instrukcji (UNDO).

Jeśli odszukamy właściwy SCN, to operacja jest niezwykle prosta:

insert into demo_flashback select * from demo_flashback as of scn 8151460; commit;

Tego nie da się wykonać jedną instrukcją przy użyciu pakietu dbms_flashback!

Eksport też potrafi

Wyobraźmy sobie wyjątkowo niekorzystny zbieg okoliczności. Z bazy usunięte zostały pomyłkowo istotne dane, jednocześnie bieżące terminowe prace nie pozwalają ci na przeprowadzenie "standardowej" procedury odzyskiwania danych z backupu. Na dodatek parametr UNDO_RETENTION ma małą wartość, i za chwilę wszelkie ślady po nieszczęśliwie skasowanych danych bezpowrotnie znikną. Nie mamy także miejsca na dysku, aby szybko zwiększyć rozmiar przestrzeni UNDO, co umożliwiłoby zwiększenie UNDO_RETENTION i dało nam nieco więcej czasu na rozwiązanie problemu!

Czy jest to sytuacja bez wyjścia? Nie. Skuteczną metodą odzyskania skasowanych danych w takim przypadku jest skorzystanie z zaawansowanych opcji eksportu dostępnych w bazie 9i. Eksport danych w tej wersji bazy poprzez rozbudowę o nowe parametry FLASHBACK_SCN oraz FLASHBACK_TIME ma możliwość użycia mechanizmu Oracle Flashback. Wykorzystując te parametry określić możemy numer zmiany, lub czas według którego będą eksportowane dane. Pamiętajmy, że czas podawany w parametrze FLASHBACK_TIME musi mieć format YYYY-MM-DD HH24:MI:SS. Aby operacja eksportu danych "archiwalnych" zakończyła się sukcesem, dodatkowo muszą być spełnione następujące warunki:

Użytkownik wykonujący eksport musi mieć prawo wykonania pakietu DBMS_FLASHBACK.
Użytkownik musi mieć przywilej FLASHBACK dla wszystkich eksportowanych tabel.
Pomiędzy czasem określonym w parametrze a chwilą obecną na interesujących nas tabelach nie były wykonywane operacje DDL.
Odzyskiwane dane nie zostały skasowane poleceniem TRUNCATE TABLE.

Czy SYS naprawdę nie może?

Sporym zaskoczeniem dla wszystkich, którzy próbowaliby powyższe przykłady wykonać jako użytkownik SYS, będzie pojawienie się błędu podczas wykonywania procedury dbms_flashback.enable_at_system_change_number:

ORA-08185: Tryb Flashback nie jest obsługiwany dla użytkownika SYS ORA-06512: przy "SYS.DBMS_FLASHBACK", linia 0 ORA-06512: przy linia 2

Tak samo dotyczy procedury dbms_flashback.enable_at_time.

To żałosne! Wszak SYS powinien potrafić wszystko. Na przykład podejrzeć, jakie obiekty usunął praktykant, któremu ktoś nieroztropnie zdradził ważne hasła. A tu taka niespodzianka! Niestety, jest to jedno z poważnych ograniczeń opisywanej funkcjonalności.

Jedynym wyjściem z zaistniałej sytuacji jest znalezienie rozwiązania alternatywnego.

Użytkownik SYS co prawda nie może włączyć trybu Flashback przy użyciu pakietu dbms_flashback, ale zawsze pozostaje możliwość użycia zapytania SELECT AS OF SCN|TIMESTAMP.

Tak więc teraz zadanie jest już banalne:

select name, subname, obj# from obj$ as of timestamp sysdate-2/24 minus select name, subname, obj# from obj$

Zastosowanie odczytu z tabeli obj$ zamiast widoku np. DBA_OBJECTS jest związane z wydajnością. Ponieważ działanie mechanizmu Flashback jest oparte na odtwarzaniu stanu tabeli na podstawie segmentów wycofania, to operacja ta jest dość czasochłonna. Szczególnie negatywnie objawia się to we wszelkich złączeniach większej liczby tabel, lub przy odczycie ze skomplikowanych widoków.

Cierpliwym polecam wypróbowanie wersji korzystającej z widoku DBA_OBJECTS:

select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects as of timestamp sysdate-2/24 minus select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects

Naprawdę, powyższe zapytanie może trwać wiele minut, nawet w przypadku, gdy nie było żadnych zmian w liście obiektów.

Do czego jest to przydatne?

W powyższych przykładach zaprezentowany został sposób odtwarzania zawartości pojedynczej tabeli, bez konieczności użycia odtwarzania bazy danych z backupu. Przedstawiony został także sposób analizowania zmian
w strukturach bazy danych. Proponuję rozważyć możliwość zastosowania mechanizmu Flashback podczas testowania aplikacji.

Często zdarza się, że mamy do przetestowania skomplikowany proces, operujący na tablicach sporych rozmiarów, który modyfikuje małą część danych. Jednakże w wyniku zaistnienia złych warunków w klauzuli WHERE dla operacji UPDATE, może dojść do modyfikacji nie tych danych co trzeba. Dzięki Oracle Flashback możemy łatwo sprawdzić, jakie zmiany zaszły w interesujących nas tabelach.

Zapytanie typu:

select * from ((select 'Nowy', LICZBA, TEKST from demo_flashback a minus select 'Nowy', LICZBA, TEKST from demo_flashback as of scn 8119953 b ) union all (select 'Stary', LICZBA, TEKST from demo_flashback as of scn 8119953 c minus select 'Stary', LICZBA, TEKST from demo_flashback d )) order by 2,1

zwraca uporządkowaną listę zmian, pomiędzy czasem aktualnym, a określonym czasem z przeszłości:

NOWY LICZBA TEKST ------ -------- -------------- Nowy 1 NOWA WARTOSC Stary 1 WIERSZ1 Nowy 2 NOWA WARTOSC Stary 2 WIERSZ2

Dzięki temu możemy tak konstruować przypadki testowe, aby bez względu na stan początkowy bazy danych, dokonać wstawienia wierszy, na których ma zostać przeprowadzony test. Następnie wykonać test, i zapytaniem o konstrukcji przedstawionej powyżej zweryfikować, czy proces operował na właściwych danych, a także czy nie zostało zmienione nic ponadto. Całość można zawrzeć w skrypcie, którego wynikiem będzie zapisanie wyniku zapytania do pliku (spool). Mechaniczne porównanie zawartości pliku z przygotowanym wcześniej wzorcem zapewni automatyzację testów regresyjnych.

Ograniczenia

Podstawowe ograniczenie w stosowaniu mechanizmu Flashback, to czas - informacja UNDO nie jest przechowywana w nieskończoność. Administrator systemu musi zadeklarować oczekiwany czas przechowywania danych w segmentach UNDO.

Drugim niezwykle ważnym ograniczeniem jest fakt, że Oracle Flashback wykorzystuje aktualny stan słownika danych. Dlatego zmiana struktury tabeli, np. dodanie kolumny powoduje generowanie błędu "ORA-01466: nie można odczytać danych - definicja tabeli jest zmieniona." Dlatego nie można tego mechanizmu zastosować, jeśli próbujemy cofnąć się do stanu tabeli sprzed usunięcia/dodania kolumny.

Podsumowanie

DBMS_FLASHBACK	SELECT - AS OF
Włącza tryb Flashback dla całej sesji	Włącza tryb flashback dla wskazanej tabeli (widoku) w zapytaniu
Łatwo zastosować w raportach - możliwe parametryzowanie raportu - dane aktualne / dane historyczne
W trybie flashback nie można modyfikować danych	Możliwe jednoczesne odczytywanie danych historycznych i modyfikowanie, np.: `insert into demo_flashback select * from demo_flashback as of scn 7867855`
Nie można użyć będąc zalogowanym jako SYS	Działa także w schemacie SYS

Co dalej?

W Oracle10g mechanizm Flashback został znacząco rozbudowany. Zostały dodane nowe instrukcje, np. FLASHBACK TABLE, które ułatwiają wykonanie pewnych standardowych operacji, takich jak przywrócenie tabeli do stanu z określonego czasu. Została także wprowadzona możliwość przywrócenia tabeli, usuniętej za pomocą drop table, poprzez zastosowanie mechanizmu podobnego do kosza w systemach okienkowych. Wprowadzona została także możliwość cofnięcia całej bazy przy użyciu jednej instrukcji. W celu zapoznania się ze szczegółami, zachęcam do zapoznania się z dokumentacją Oracle 10g.

Dodaj swój komentarz

wróć

Do góry

Drukuj