Chwytanie zmian – część 1

Bohdan Szymczak

Powszechnie wiadomo, że przetwarzanie danych w bazie Oracle odbywa się, zgodnie z modelem relacyjnym, w strukturach zorganizowanych w wiersze, które fizycznie są składowane w blokach bazodanowych. Wiadomo również, że baza Oracle jest zarówno szybka w działaniu, jak i bezpieczna, co oznacza, że twórcom systemu udało się praktycznie pogodzić dwie rzeczywiste sprzeczności.

Bezpieczeństwo danych można uzyskać tylko wtedy, gdy zostaną one trwale zapisane w systemie dyskowym, co z kolei zaprzecza możliwości szybkiego działania. Jeżeli bowiem każda zmiana danych wymagałaby natychmiastowego zapisu do pliku danych na dysk przy zatwierdzeniu transakcji, to dodatkowy narzut czasowy na obsługę systemu dyskowego w trybie zapisu randomowego wykluczyłby wysoką wydajność. Z kolei najszybszą pracę uzyskać można poprzez buforowanie danych w pamięci operacyjnej serwera i wykonywanie zmian w blokach zapisanych w RAMie, a nie na dysku. To rozwiązanie niesie ze sobą ryzyko utraty danych w przypadku awarii serwera.

Wysoka wydajność bazy Oracle została osiągnięta właśnie poprzez inteligentne buforowanie danych w pamięci operacyjnej serwera i opóźnione zapisywanie zmienionych bloków na dysk przy pomocy procesów tła Database Writer (DBWn). Drugim niezależnym kanałem, zabezpieczającym dane przed awarią, jest zapis zmienionych danych przy pomocy procesu Log Writer (LGWR). Proces ten nie zapisuje danych w postaci bloków bazodanowych bezpośrednio do plików przestrzeni tabel, co jest procesem mało wydajnym, ale dokonuje zapisu zmian do specjalnie do tego celu przeznaczonych plików dziennika powtórzeń (redo log files). Zapis jest wykonywany w trybie sekwencyjnym, a więc bardzo wydajnym, odbywa się również za pomocą buforów pamięciowych (redo log buffers). Co najważniejsze, zapis odbywa się zawsze przed zakończeniem polecenia commit, co gwarantuje zachowanie danych każdej zatwierdzonej transakcji bazodanowej. W przypadku awarii bazy, odtworzenie jej stanu na moment awarii jest zawsze możliwe, o ile zabezpieczona została wcześniejsza kopia bazy i pliki dziennika powtórzeń z momentu awarii. Dodatkowym zabezpieczeniem jest możliwość zwielokrotnienia liczby jednocześnie zapisywanych kopii plików dziennika powtórzeń.

Rysunek nr 1 pokazuje schematycznie proces zapisu zmian w bazie danych przez procesy asynchroniczne DBWn i LGWR.


Rys. 1. Schemat zapisu zmian danych.

Pliki dziennika powtórzeń pracują w cyklu zamkniętym, co oznacza, że po zapisaniu wszystkich kolejnych plików, proces ponownie używa pierwszego z nich do zapisu zmian w bazie. Aby zabezpieczyć te dane przed ponownym nadpisaniem, możliwe jest uruchomienie bazy w trybie archiwizacji logów, co powoduje uaktywnienie kolejnych procesów Archivers (ARCn), odpowiedzialnych za skopiowanie całkowicie zapisanych plików dziennika powtórzeń do osobnej, przeznaczonej do tego celu lokalizacji.

Rysunek nr 2 pokazuje schematycznie cykl pracy plików dziennika powtórzeń.


Rys. 2. Cykl pracy plików dziennika powtórzeń.

Mechanizm zapisu zmian poprzez pliki dziennika powtórzeń został pierwotnie opracowany w celu ochrony danych przed utratą w wyniku awarii. Łatwo jednak zauważyć, że istnieje potencjalna możliwość innego wykorzystania tej cechy mechanizmu jaką jest ciągła rejestracja zmian w bazie. Zauważmy, że chcąc śledzić na bieżąco zmiany w bazie, a więc identyfikować nowe, zmodyfikowane lub usunięte wiersze tabel, konieczne byłoby specyficzne oprogramowanie tej funkcjonalności. Mając do dyspozycji tylko standardowe mechanizmy dostępu do danych, czyli polecenia SQL, można taką funkcjonalność zrealizować na przykład poprzez:

  • Cykliczny odczyt monitorowanych tabel i wyszukiwanie zmian w wierszach kolejnych odpowiedzi;
  • Założenie wyzwalaczy na monitorowanych tabelach dla zdarzeń insert, delete, update i rejestrowanie, bądź przetwarzanie zidentyfikowanych zmian.

Niezależnie od skuteczności i optymalności użytych algorytmów, obie metody posiadają przynajmniej dwie wady: po pierwsze – są pracochłonne, a po drugie – wpływają obciążająco na bazę danych. Szczególnie pierwsza metoda, w przypadku dużych tabel powoduje znaczne obciążenie bazy, a w krańcowych przypadkach może nie nadawać się do zastosowania, ze względu na nieakceptowanie długi czas generacji odpowiedzi.

Być może analogiczne rozważania skłoniły producenta bazy Oracle do innego wykorzystania plików dziennika powtórzeń, niż pierwotnie zakładano. Ciekawym przypadkiem użycia tej niejako ubocznej właściwości jest funkcjonalność udostępniona użytkownikom bazy w Change Data Capture.

Zanim bliżej zostanie przedstawiona ta funkcjonalność, która zgodnie z intencją producenta jest przeznaczona głównie do zastosowań w hurtowniach danych, przyjrzyjmy się bliżej samym plikom dziennika powtórzeń oraz sposobom dostępu do ich zawartości. To wyjaśnienie ułatwi zrozumienie mechanizmów i funkcjonowania modułu Change Data Capture.

Opisane w niniejszym artykule mechanizmy, pakiety oraz struktury dotyczą bazy danych Oracle w wersji 10gR2. Funkcjonalność Change Data Capture była w bardziej ograniczonej postaci dostępna również w wersji 9i, zaś LogMiner również w jeszcze wcześniejszych wersjach bazy.

Naturalnym sposobem dostępu do danych w bazie Oracle jest użycie języka zapytań SQL oraz perspektyw, umożliwiających oglądanie danych poprzez widoki relacyjne. Taki interfejs został również zastosowany przez producenta dla umożliwienia przeglądania zapisów w plikach dziennika powtórzeń, zarówno tych on-line, jak i archiwalnych. Narzędziem opracowanym do tego celu jest LogMiner, stanowiący standardową część oprogramowania bazy. Zgodnie z założeniami producenta, biorąc pod uwagę, iż w logach znajdują się wszystkie informacje o zmianach w bazie, LogMiner może być używany do następujących celów:

  1. Odnajdywanie momentu, w którym w bazie zostały wykonane niepożądane operacje, najczęściej na skutek błędu użytkownika lub aplikacji. Znajomość dokładnego czasu, po którym baza znalazła się w niewłaściwym stanie, na przykład na skutek omyłkowego usunięcia tabeli, lub też dużej, błędnej modyfikacji danych, umożliwia odzyskanie bazy do stanu przed logiczną awarią, pod warunkiem posiadania prawidłowej kopii bezpieczeństwa (odtwarzanie do punktu w czasie). Planując politykę bezpieczeństwa, administrator powinien zakładać taki scenariusz, co umożliwi mu szybkie naprawienie sytuacji. Oczywiście takie rozwiązanie możliwe jest tylko w pewnych klasach aplikacji – takich, w których można później uzupełnić dane wprowadzone do bazy po punkcie, do którego bazę odtworzono.
  2. Wycofywanie zmian w poszczególnych tabelach. Ponieważ w logach znajdują się zapisy o wszelkich zmianach w bazie, możliwe jest selektywne wycofanie niektórych z nich, oczywiście tylko pod warunkiem znajomości logiki aplikacji pracującej na bazie, gdyż gwarantuje to zachowanie spójności danych.
  3. Wykorzystanie logów na potrzeby strojenia aplikacji, poprzez analizę statystyczną dostępu do określonych tabel.
  4. Użycie zapisów w logach do celu auditingu, poprzez śledzenie podejrzanej aktywności użytkowników. Szczególnie przydatne może okazać się uzupełnienie założonego na bazie standardowego auditingu, zapisującego zdarzenia w tabeli SYS.AUD$, o wykonywaną w określonych przypadkach szczegółową analizę, bazującą na informacjach z logów. W tym przypadku możliwe jest na przykład dokładne wskazanie: kto i kiedy dokonał zmiany wartości określonej kolumny tabeli oraz podanie starej i nowej wartości.

Te cztery zastosowania sugerowane przez Oracle nie wyczerpują innych możliwości użycia LogMinera – zostały one tak sformułowane prawdopodobnie dlatego, że w dobry sposób ilustrują możliwości pozyskiwania informacji z logów, zaś inne zastosowania są jedynie konsekwencją możliwości manipulacji danymi, które wcześniej zostały zapisane w plikach dziennika powtórzeń.

Dostęp do zapisanych w plikach dziennika powtórzeń danych jest możliwy, po uruchomieniu LogMinera, poprzez perspektywę V$LOGMNR_CONTENTS. Wymaga to podłączenia się do bazy danych dowolnym narzędziem, na przykład SQL*Plusem. Oznacza to, że nie można przy użyciu LogMinera odczytywać wprost zapisów z logów; trzeba używać pomocniczej bazy. Może to być zarówno baza źródłowa, z której pochodzą logi, jak i dowolna inna, spełniająca następujące warunki:

  • jest posadowiona na tej samej platformie sprzętowej,
  • jest obsługiwana przez tę samą, lub późniejszą wersję oprogramowania RDBMS,
  • korzysta z tego samego zestawu znaków.

Skoro jednak przeglądanie plików nie wymaga dostępu do bazy źródłowej, to chcąc posługiwać się nazwami tabel i kolumn, zamiast wewnętrznymi identyfikatorami, dodatkowo konieczne jest jeszcze utworzenie słownika danych o obiektach w tej bazie. A zatem można wyróżnić następujące komponenty systemu, dające dostęp do plików dziennika powtórzeń:

  1. źródłowa baza danych, która produkuje pliki logów,
  2. pomocnicza baza danych, z której odczytujemy zapisy w logach; może nią być również baza źródłowa,
  3. słownik danych o obiektach w bazie źródłowej,
  4. pliki logów utworzone przez bazę źródłową.

Ostatecznie konfigurację narzędzia można przedstawić jak na rysunku:


Rys. 3. Konfiguracja LogMinera.

Fakt, iż do przeglądania zapisów w logach nie jest konieczny dostęp do bazy źródłowej, jest wyjątkowo korzystną cechą narzędzia i daje bardzo szerokie możliwości jego wykorzystania w odległych lokalizacjach – na przykład jako sposób transportu informacji o zmianach w bazie źródłowej.

Funkcjonalność LogMinera jest dostępna poprzez dwa pakiety bazodanowe oraz zestaw perspektyw. Pierwszym pakietem jest DBMS_LOGMNR_D, odpowiedzialny za utworzenie słownika danych. Zawiera on tylko dwie procedury:

  • SET_TABLESPACE – pozwala zmienić domyślną przestrzeń tabel SYSAUX, w której tworzone są tabele LogMinera na dowolną inną,
  • BUILD – tworzy słownik LogMinera, przy czym możliwe są dwa miejsca w których słownik LogMinera może być składowany: pierwszym z nich jest płaski plik, drugim pliki dziennika powtórzeń.

Drugi pakiet, w którym zawarta jest główna funkcjonalność narzędzia zawiera następujące procedury:

  • ADD_LOGFILE – dodaje podany jako parametr plik dziennika powtórzeń do listy plików, które mogą być analizowane podczas sesji LogMinera,
  • REMOVE_LOGFILE – usuwa podany jako parametr plik dziennika powtórzeń z listy plików, które mogą być analizowane podczas sesji LogMinera,
  • START_LOGMINER – uruchamia sesję LogMinera, wczytując słownik danych, umożliwiający interpretację wewnętrznych identyfikatorów obiektów na ich nazwy,
  • END_LOGMINER – kończy sesję LogMinera,
  • COLUMN_PRESENT – funkcja logiczna umożliwiająca sprawdzenie, czy określona kolumna tabeli jest obecna w wierszu odpowiedzi perspektywy V$LOGMNR_CONTENTS; jest ona potrzebna do interpretacji wartości NULL funkcji MINE_VALUE. W takim przypadku wartość 0 funkcji COLUMN_PRESENT oznacza brak kolumny, zaś wartość 1 – obecność kolumny o wartości NULL,
  • MINE_VALUE – konwertuje surowe zapisy w kolumnach REDO_VALUE lub UNDO_VALUE perspektywy V$LOGMNR_CONTENTS na postać czytelną dla użytkownika.

Jak widać, liczba dostępnych procedur i funkcji jest niewielka, niemniej jednak obejmują one kompletną funkcjonalność potrzebną do uruchomienia i prowadzenia analizy zapisów w plikach dziennika powtórzeń. Dynamiczne perspektywy związane z LogMinerem to:

  • V$LOGMNR_CONTENTS
  • V$LOGMNR_DICTIONARY
  • V$LOGMNR_DICTIONARY_LOAD
  • V$LOGMNR_LATCH
  • V$LOGMNR_LOGS
  • V$LOGMNR_PARAMETERS
  • V$LOGMNR_PROCESS
  • V$LOGMNR_SESSION
  • V$LOGMNR_STATS

Informacje o zapisanych w logach danych znajdują się w perspektywie V$LOGMNR_CONTENTS, zawierającej bardzo szczegółowe dane w 60 kolumnach. Na potrzeby niniejszego artykułu bliżej zajmiemy się tylko niektórymi z nich, wyszczególnionymi w Tabeli 1.

Tabela 1. Wybrane kolumny perspektywy V$LOGMNR_CONTENTS.

Kolumna Typ Znaczenie
SCN NUMBER Wartość SCN (System change number) dla zarejestrowanej w bazie zmiany
TIMESTAMP DATE Dokładna wartość czasu wykonania zmiany
SEG_OWNER VARCHAR2(32) Właściciel zmodyfikowanego segmentu
SEG_NAME VARCHAR2(256) Nazwa zmodyfikowanego segmentu
TABLE_NAME VARCHAR2(32) Nazwa zmodyfikowanej tabeli
SEG_TYPE NUMBER Typ zmodyfikowanego segmentu
SESSION# NUMBER Numer sesji, która wykonała zmianę
SERIAL# NUMBER Numer seryjny sesji, która wykonała zmianę
USERNAME VARCHAR2(30) Nazwa użytkownika wykonującego transakcję
SESSION_INFO VARCHAR2(4000) Szczegółowe informacje o sesji wykonującej transakcję. Informacje mogą zawierać, w zależności od dostępności danych np. nazwę końcówki, nazwę modułu (programu), identyfikator użytkownika w systemie operacyjnym, itp.
ROLLBACK NUMBER Określa, czy zapis dotyczy wykonywanego odwołania transakcji. Kolumna przyjmuje wartość: 1 – gdy zapis zmiany jest skutkiem częściowego lub całkowitego odwołania skojarzonej transakcji; 0 – zapis nie jest związany z odwołaniem transakcji.
OPERATION VARCHAR2(32) Określenie operacji SQL skutkującej wykonaniem zmiany. Kolumna przyjmuje kilkanaście możliwych wartości, takich jak np. INSERT, UPDATE, DELETE, COMMIT, itp.
OPERATION_CODE NUMBER Kod operacji (analog do kolumny OPERATION). Przykładowe kody: 0 – INTERNAL 1 – INSERT 2 – DELETE 3 – UPDATE 7 – COMMIT
SQL_REDO VARCHAR2(4000) Polecenie SQL, którego skutkiem jest zarejestrowana zmiana
SQL_UNDO VARCHAR2(4000) Polecenie SQL, którego skutkiem byłoby odwrócenie zmiany wywołanej przez polecenie zawarte w SQL_REDO
RS_ID VARCHAR2(32) Para (RS_ID,SSN) stanowi unikalny identyfikator wiersza w perspektywie V$LOGMNR_CONTENTS
SSN NUMBER
REDO_VALUE NUMBER Kodowana wartość opisująca zmianę, będąca parametrem dla funkcji LOGMNR.MINE_VALUE i LOGMNR.COLUMN_PRESENT
UNDO_VALUE NUMBER Analogicznie jak REDO_VALUE, dla operacji odwracającej zmianę
AUDIT_SESSIONID NUMBER Identyfikator audytowy sesji użytkownika wykonującego zmianę

Należy zwrócić uwagę na fakt, że kolumna SQL_REDO nie zawiera oryginalnego polecenia SQL, w wyniku którego nastąpiła zmiana w bazie, ale taką jego rekonstrukcję, której skutek jest taki sam, jak polecenia oryginalnego. Na analogicznej zasadzie tworzone są wartości w kolumnie SQL_UNDO, z tym zastrzeżeniem, że nie obejmują one poleceń typu DDL.

Z przeglądu wybranych kolumn perspektywy V$LOGMNR_CONTENTS, opisanych w Tabeli nr 1 widać, że zawierają one wszystkie niezbędne informacje – zarówno do identyfikacji zmian wykonanych w bazie w określonym przedziale czasu, jak i do przypisania ich do określonego użytkownika, a nawet określonego stanowiska. W niektórych przypadkach możliwe jest nawet określenie programu, z którego zmiana została wykonana.

Aby zademonstrować funkcjonowanie LogMinera przeprowadźmy opisany poniżej test, koncentrując się na symulacji funkcjonalności polegającej na przenoszeniu zmienionych danych zarejestrowanych w plikach dziennika powtórzeń.

Załóżmy, że oczekiwaną aplikacją jest system modyfikujący na bieżąco własne dane o produktach na podstawie zmiany stanów magazynowych w innych systemach, przy czym posiada on dostęp do plików dziennika powtórzeń systemów źródłowych. Stany magazynowe w systemie źródłowym są zapisywane w tabeli STANY_MAG ulokowanej w schemacie USER1:

SQL> desc USER1.STANY_MAG

Nazwa  Wartość NULL?   Typ
------ --------------- ----------------
INDEKS NOT NULL        NUMBER
NAZWA  VARCHAR2(25)
STAN   NUMBER

Jeżeli przyjmiemy, że każdy ruch magazynowy (przyjęcie, wydanie lub przesunięcie między magazynami) powoduje modyfikację tabeli STANY_MAG, to wystarczy śledzić w niej na bieżąco zmiany i na tej podstawie modyfikować własne dane. W tym celu uruchomiony zostanie LogMiner, a następnie będą wyszukiwane zmiany w tabeli STANY_MAG przy użyciu perspektywy V$LOGMNR_CONTENTS.

Pierwszym krokiem będzie uruchomienie dodatkowych zapisów w logach wszystkich niezbędnych informacji, wymaganych przez LogMinera do pracy:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Baza danych została zmieniona.

W naszym przykładzie będziemy się posługiwać logami on-line, zatem konieczna jest ich identyfikacja:

SQL> select l.GROUP#,l.SEQUENCE#,l.MEMBERS,l.ARCHIVED,l.STATUS,f.MEMBER
2 from v$log l, v$logfile f
3 where f.GROUP#=l.GROUP#
4 order by GROUP#;

GROUP# SEQUENCE# MEMBERS ARC STATUS MEMBER
------ --------- ------- --- ------------ ------------------------------------------
     1 644       1       NO      INACTIVE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG
     2 645       1       NO       CURRENT C:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG
     3 643       1       NO      INACTIVE C:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG

Znając lokalizację plików dziennika powtórzeń, można przypisać je do procesu LogMinera przy pomocy procedury DBMS_LOGMNR.ADD_LOGFILE:

SQL> begin
  2 DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG', OPTIONS =>
  3 DBMS_LOGMNR.ADDFILE);
  4 DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'C:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG', OPTIONS =>
  5 DBMS_LOGMNR.ADDFILE);
  6 DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'C:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG', OPTIONS =>
  7 DBMS_LOGMNR.ADDFILE);
  8 end;
  9 /
Procedura PL/SQL została zakończona pomyślnie.
SQL>

Po tej operacji, wszystkie zapisy znajdujące się w przypisanych plikach dziennika powtórzeń staną się dostępne po uruchomieniu sesji LogMinera. Ponieważ jednak obserwacje zmian będziemy wykonywać od chwili bieżącej, konieczne jest ustalenie punktu w czasie, od którego dane są w obszarze naszego zainteresowania. Jeżeli ten punkt nie byłby określony, to w perspektywie V$LOGMNR_CONTENTS widoczne byłyby również wszystkie dawniejsze zmiany w tabeli źródłowej, które zostały zapisane w okresie czasu obejmowanego przez przypisane pliki dziennika powtórzeń.

Dobrym rozwiązaniem jest użycie wskaźnika SCN, jako znacznika czasu. W naszym przykładzie sesję LogMinera uruchomimy od bieżącej wartości SCN. Wartość tę odczytuje się z perspektywy V$DATABASE:

SQL> select CURRENT_SCN from v$database;
CURRENT_SCN
------------------
 9539236

Używając odczytanej wartości można uruchomić sesję LogMinera:

SQL> execute DBMS_LOGMNR.START_LOGMNR( STARTSCN => 9539236, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );
Procedura PL/SQL została zakończona pomyślnie.

Ponieważ w przykładzie korzystamy z dostępu do źródłowej bazy danych, więc nie ma potrzeby dokonywania ekstrakcji słownika danych procedurą DBMS_LOGMNR_D.BUILD, stąd ustawiona opcja DICT_FROM_ONLINE_CATALOG. Od tego momentu można przeglądać zapisy w logach poprzez zapytania do perspektywy V$LOGMNR_CONTENTS. Odpowiedź jest zawężona wyłącznie do wierszy dotyczących tabeli STANY_MAG:

SQL> select SCN, TABLE_NAME, SQL_REDO, SQL_UNDO
  2 from V$LOGMNR_CONTENTS
  3 where SEG_OWNER='USER1' and TABLE_NAME='STANY_MAG' and SCN>9539236;

nie wybrano żadnych wierszy

Wykonajmy teraz modyfikację tabeli źródłowej:

SQL> insert into STANY_MAG
  2 ( INDEKS, NAZWA, STAN )
  3 values
  4 ( 1001,'Towar 1', 100 );

1 wiersz został utworzony.

Ponowny odczyt perspektywy pokazuje zmianę w tabeli:

SQL> select SCN, TABLE_NAME, SQL_REDO, SQL_UNDO
  2 from V$LOGMNR_CONTENTS
  3 where SEG_OWNER='USER1' and TABLE_NAME='STANY_MAG' and SCN>9539236;

     SCN TABLE_NAME
-------- --------------------------------
SQL_REDO
-------------------------------------------------------------------------
SQL_UNDO
-------------------------------------------------------------------------
  9542972 STANY_MAG
insert into "USER1"."STANY_MAG"("INDEKS","NAZWA","STAN") values ('1001','Towar 1','100');
delete from "USER1"."STANY_MAG" where "INDEKS" = '1001' and "NAZWA" = 'Towar 1' and "STAN" = '100' and ROWID = 'AAAN8wAAEAAABs0AAA';

Powtórzmy oba ostatnie kroki, modyfikując wprowadzony wcześniej wiersz do tabeli STANY_MAG:

SQL> update STANY_MAG
  2 set STAN=STAN+10
  3 where INDEKS=1001;

1 wiersz został zmodyfikowany.

SQL> select SCN, TABLE_NAME, SQL_REDO, SQL_UNDO
  2 from V$LOGMNR_CONTENTS
  3 where SEG_OWNER='USER1' and TABLE_NAME='STANY_MAG' and SCN>9542972;

     SCN TABLE_NAME
-------- --------------------------------
SQL_REDO
-------------------------------------------------------------------------
SQL_UNDO
-------------------------------------------------------------------------
 9543525 STANY_MAG
update "USER1"."STANY_MAG" set "STAN" = '110' where "STAN" = '100' and ROWID = 'AAAN8wAAEAAABs0AAA';
update "USER1"."STANY_MAG" set "STAN" = '100' where "STAN" = '110' and ROWID = 'AAAN8wAAEAAABs0AAA';

W zapytaniu do perspektywy V$LOGMNR_CONTENTS, zmieniliśmy wartość SCN na tę odczytaną w poprzednim zapytaniu, dzięki czemu otrzymaliśmy w odpowiedzi tylko nowe zmiany. Bez tej czynności odpowiedź zawierałaby wszystkie zmiany wykonane w tabeli STANY_MAG od chwili rozpoczęcia sesji LogMinera.

Zwróćmy uwagę na zawartość kolumn SQL_REDO i SQL_UNDO. Zawierają one gotowe do użycia polecenia SQL, z których pierwsze daje efekt analogiczny do uzyskanego przez zapytanie oryginalne, zaś drugie jest odwróceniem tej operacji. Nie używając zapytań wprost kierowanych do tabeli źródłowej, uzyskuje się (poprzez analizę logów) wszelkie niezbędne dane do zidentyfikowania operacji na niej wykonanych. Wystarczy teraz oprogramować przetwarzanie wartości podanej w kolumnie SQL_REDO, by pobrane z niej wartości użyć we własnej aplikacji, czyli w omawianym przypadku zapisać zmiany stanów magazynowych w projektowanej aplikacji.

Jak łatwo zauważyć, transakcje wykonane na tabeli STANY_MAG nie zostały zatwierdzone poleceniem commit, a mimo to zostały zapisane do logów. W przypadku odwołania transakcji uzyskamy następujący efekt:

SQL> rollback;

Wycofywanie zostało zakończone.

SQL> select SCN, TABLE_NAME, SQL_REDO, SQL_UNDO
  2 from V$LOGMNR_CONTENTS
  3 where SEG_OWNER='USER1' and TABLE_NAME='STANY_MAG' and SCN>9543525;

     SCN TABLE_NAME
-------- --------------------------------
SQL_REDO
-------------------------------------------------------------------------
SQL_UNDO
-------------------------------------------------------------------------
  9544063 STANY_MAG
update "USER1"."STANY_MAG" set "STAN" = '100' where ROWID = 'AAAN8wAAEAAABs0AAA';

  9544063 STANY_MAG
delete from "USER1"."STANY_MAG" where ROWID = 'AAAN8wAAEAAABs0AAA';

Zmiany w tabeli źródłowej zostały zaprezentowane jako ciąg kolejnych zmian update, a następnie delete, zaś w kolumnie SQL_UNDO brak jest wartości.

Z punktu widzenia potrzeb przykładowej aplikacji, konieczność analizy logów ze względu na transakcyjność w bazie źródłowej jest dość uciążliwa. Również interpretacja biznesowa zmian w niezatwierdzonych transakcjach wskazuje, że zmiany w tabeli źródłowej powinny być uwzględniane dopiero po ich zatwierdzeniu. Taką możliwość bardzo łatwo uzyskać otwierając sesję LogMinera w trybie dostępu do danych zatwierdzonych. Służy do tego opcja COMMITTED_DATA_ONLY, którą należy użyć w procedurze START_LOGMNR.

Powtórzmy teraz przykład z użyciem tej opcji. Na początku konieczne jest zamknięcie bieżącej sesji, a następnie powtórzenie kolejnych kroków do otwarcia nowej:

SQL> execute DBMS_LOGMNR.END_LOGMNR;

Procedura PL/SQL została zakończona pomyślnie.

SQL> begin
  2 DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG', OPTIONS => DBMS_LOGMNR.ADDFILE);
  3 DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG', OPTIONS => DBMS_LOGMNR.ADDFILE);
  4 DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG', OPTIONS => DBMS_LOGMNR.ADDFILE);
  5 end;
  6 /

Procedura PL/SQL została zakończona pomyślnie.
SQL> select CURRENT_SCN from v$database;
  CURRENT_SCN
-------------
      9545315

SQL> execute DBMS_LOGMNR.START_LOGMNR( STARTSCN => 9545315, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

Procedura PL/SQL została zakończona pomyślnie.

LogMiner został uruchomiony w trybie dostępu do zatwierdzonych transakcji. Teraz można sprawdzić tę funkcjonalność wykonując analogiczne operacje:

SQL> insert into STANY_MAG
  2 ( INDEKS, NAZWA, STAN )
  3 values
  4 ( 1001,'Towar 1', 100 );

1 wiersz został utworzony.

SQL> select SCN, TABLE_NAME, SQL_REDO, SQL_UNDO
  2 from V$LOGMNR_CONTENTS
  3 where SEG_OWNER='USER1' and TABLE_NAME='STANY_MAG' and SCN>9545315;

nie wybrano żadnych wierszy

Jak widać, pomimo dopisania wiersza do tabeli STANY_MAG, nie jest on widoczny w perspektywie V$LOGMNR_CONTENTS:

SQL> commit;

Zatwierdzanie zostało ukończone.

SQL> select SCN, TABLE_NAME, SQL_REDO, SQL_UNDO
  2 from V$LOGMNR_CONTENTS
  3 where SEG_OWNER='USER1' and TABLE_NAME='STANY_MAG' and SCN>9545315;

     SCN TABLE_NAME
-------- --------------------------------
SQL_REDO
-------------------------------------------------------------------------
SQL_UNDO
-------------------------------------------------------------------------
 9548114 STANY_MAG
insert into "USER1"."STANY_MAG"("INDEKS","NAZWA","STAN") values ('1001','Towar 1','100');
delete from "USER1"."STANY_MAG" where "INDEKS" = '1001' and "NAZWA" = 'Towar 1' and "STAN" = '100' and ROWID = 'AAAN8wAAEAAABs0AAA';

Dopiero zatwierdzenie transakcji na tabeli źródłowej spowodowało, że zmiana stała się widoczna w sesji LogMinera.

Do zakładanego celu – stworzenia aplikacji obsługującej bazę danych poprzez śledzenie zmian w tabelach źródłowych, ten sposób pracy sesji LogMinera jest najbardziej odpowiedni. Do celów poglądowych, w przykładzie użyto tej samej bazy w charakterze zarówno bazy źródłowej, jak i pomocniczej oraz on-line plików dziennika powtórzeń. Można pokazać, że jądrem aplikacji może być prosta pętla o następującej konstrukcji:

Oczywista korzyść z zastosowania LogMinera do przenoszenia zmodyfikowanych danych występuje dopiero wtedy, gdy baza aplikacji jest różna od bazy źródłowej. W tym przypadku pliki logów przypisane do sesji LogMinera muszą być plikami archiwalnymi, zaś dodatkowo należy zapewnić transport tych plików do lokalizacji docelowej. W tym przypadku należy pamiętać, że informacje o zmianach będą dostępne z opóźnieniem, równym czasowi przełączania plików dziennika powtórzeń w bazie źródłowej, co jest zależne od ich rozmiaru oraz wielkości przetwarzania. Jeżeli czas opóźnienia jest istotnym parametrem, to można wymusić w bazie źródłowej przełączanie plików logów co pewien ustalony czas, o ile bieżący poziom przetwarzania okazałby się niski.

LogMiner jest funkcjonalnością bazy danych o szerokich możliwościach zastosowań, choć wydaje się, że wciąż jest niewystarczająco doceniany i wykorzystywany przez użytkowników. Zdaniem autora, wart jest bliższego poznania i szerszego stosowania.

Bibliografia:

  1. Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) B14223-02
  2. Oracle® Database Concepts 10g Release 2 (10.2) B14220-02
  3. Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) B14258-02
  4. Oracle® Database Utilities 10g Release 2 (10.2) B14215-01