2017年4月25日火曜日

ログマイナーを試してみる

Oracle LogMinerは、Oracle DatabaseのREDOログファイル(オンライン・アーカイブともに)の内容ををSQLで問い合わせることができるものです。
REDOログファイルの内容を見ることができるようになることで、データベースの履歴情報が確認できます。

LogMinerを使用するためには、REDOログファイルに対して追加の列を用意し、記録をする必要があります。追加の列に記録することを「サプリメンタル・ロギング」といいます。
デフォルトでは、Oracle Databaseはサプリメンタル・ロギングは有効になっていません。そのため、LogMinerを使用したい場合はサプリメンタルロギングを有効にする必要があります。

まずは、現在のデータベースの設定を確認し、サプリメンタル・ロギングが有効か確認します。

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO


NOはサプリメンタル・ロギングが設定されていないことを示しています。

さて、それではサプリメンタル・ロギングを有効にしましょう。

SQL> alter database add supplemental log data;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES


有効になりました。

さて、LogMinerによるデータの取得をしてみましょう。

1.LogMinerディクショナリの指定
ディクショナリの指定を行うことができますが、今回はLogMiner起動時に指定したので、
特になにもしませんでした。

2.REDOログファイルの指定
LogMinerのデータを取得したいREDOログファイルを指定します。
まずは、今使われているのオンラインREDOログファイルを確認します。


SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/balloon/redo03.log
/u01/app/oracle/oradata/balloon/redo02.log
/u01/app/oracle/oradata/balloon/redo01.log
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1    1       1   52428800   512 1 YES
INACTIVE       1127233 13-NOV-16      1147535 09-APR-17
2    1       2   52428800   512 1 NO
CURRENT       1147535 09-APR-17   2.8147E+14
3    1       0   52428800   512 1 YES
UNUSED     0    0

現在はredo02.logを使用されているため、今回はredo02.logを確認したいと思います。

SQL> execute dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/balloon/redo02.log', options=> dbms_logmnr.new);
PL/SQL procedure successfully completed.

指定が完了しました。

3.LogMinerを起動
LogMinerを起動します。ここではディクショナリの指定にオンラインカタログを使用します。
オンラインカタログはオンラインREDOログ、アーカイブREDOログの分析に使用できます。

SQL> execute dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.

★ここで、REDOログに記録するSQLを発行します。
今回は、qb.keiyakuテーブルに新たに行を追加したのち、削除後ロールバックした場合と削除後コミットした場合のログを見てみます。

SQL> select * from qb.keiyaku;
NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10

SQL>  insert into qb.keiyaku values('oriko',to_date('2011/03/20','yyyy/mm/dd'));
1 row created.
SQL> commit;
Commit complete.

SQL> select * from qb.keiyaku;
NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10
oriko     20-MAR-11 ★
6 rows selected.

SQL> delete from qb.keiyaku where name = 'oriko';
1 row deleted.
SQL> rollback;
Rollback complete.

SQL> select * from qb.keiyaku;
NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10
oriko     20-MAR-11 ★
6 rows selected.

SQL> delete from qb.keiyaku where name = 'oriko';
1 row deleted.
SQL> commit;
Commit complete.

SQL> select * from qb.keiyaku;
NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10

4.REDOログの内容を確認
REDOログの内容を確認します。
確認するには、SQLにてv$logmnr_contentsビューを確認することで可能となります。

set line 6000
select SCN,TIMESTAMP,TABLE_NAME,OPERATION,SQL_REDO from v$logmnr_contents where operation!='INTERNAL';
       SCN TIMESTAMP TABLE_NAME      OPERATION       SQL_REDO
(略)
   1152964 15-APR-17 KEIYAKU      INSERT       insert into "QB"."KEIYAKU"("NAME","KEIYAKUBI") values ('oriko',TO_DATE('20-MAR-11', 'DD-MON-RR'));
(略)
   1153175 15-APR-17 KEIYAKU      DELETE       delete from "QB"."KEIYAKU" where "NAME" = 'oriko' and "KEIYAKUBI" = TO_DATE('20-MAR-11', 'DD-MON-RR') and ROWID = 'AAASAfAAEAAAAEPAAB';
   1153180 15-APR-17 KEIYAKU      INSERT       insert into "QB"."KEIYAKU"("NAME","KEIYAKUBI") values ('oriko',TO_DATE('20-MAR-11', 'DD-MON-RR'));
   1153181 15-APR-17      ROLLBACK       rollback;
(略)
   1153193 15-APR-17 KEIYAKU      DELETE       delete from "QB"."KEIYAKU" where "NAME" = 'oriko' and "KEIYAKUBI" = TO_DATE('20-MAR-11', 'DD-MON-RR') and ROWID = 'AAASAfAAEAAAAEPAAB';
   1153194 15-APR-17      COMMIT       commit;

略としてある場所には、裏で発行SQL文をv$sqlに格納するなど、システム側の動きがありました。

5.LogMinerを停止
最後にLogMinerを停止します。

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.

以上のような手順でログマイナーを使用できます。
オペミスなどのときにも確認に使えそうですね。

ただ、気になるのはシステムへの負荷。
今回試してみたのは「最小サプリメンタル・ロギング」でしたが、最小サプリメンタル・ロギングでは、マニュアルで読む限り、さほど負荷は大きくならないとなっています。もちろん、導入の際にはしっかり事前検証が必要でしょう。
行の内容までを記録できる「認識キー・ロギング」もありますが、こちらは最小サプリメンタル・ロギングと比べると負荷が大きくなるようです。