ログマイナー再び！！ その５
＜ログマイナー再び！！ その５＞
ペンネーム： ぽっちゃりメタボン
こんにちは！！、食欲旺盛なぽっちゃりメタボンです。
今週はログマイナーととっても関係が深いサプリメンタル・ロギングを取り
上げてみたいと思います!!
▼ サプリメンタル・ロギングとは？
簡単にまとめると以下のとおりです。
・9iから導入された機能である。
・本来REDOログは更新前の値と更新後の値だけを持っているが補助的に
更新列以外の列値を追加できる。
・v$logmnr_contentsで確認可能なSQL文(sql_redo列,sql_undo列)の生成
に密接に関係している。
・最小サプリメンタル・ロギングは分析するログ・ファイルを生成する前に
必ず有効にしておかなければならない。
また、以下はマニュアル(Oracle Database ユーティリティ 10gリリース2)
からの抜粋となりますが補助的なカラムが必要となる例を示しています。
===================
1.再構築されたSQL文を別のデータベースに適用するアプリケーションでは、
行を一意に識別する列（主キーなど）で更新文を識別する必要があります。
ROWID はデータベースごとに異なり、他のデータベースでは意味を持たな
いため、V$LOGMNR_CONTENTS ビューによって返される再構築されたSQL に
示されるROWID では識別できません。
2.アプリケーションは、行変更の追跡をより効率的にするために、変更された
列のみでなく、行全体のビフォア・イメージを記録する必要がある場合があ
ります。
===================
1.については分析対象データベース（ソース・データベース）と分析データ
ベース（マイニング・データベース）が異なっているパターン、およびData
Guard環境等を想定しているものと思われます。ログマイナーの構成として、
いくつか条件はありますが必ずしもソース・データベースとマイニング・
データベースは同一である必要はありません。
もう一例の 2.についてですが、設定することによってどのようなメリット
があるのかイメージが掴みにくいような気がします。
よって、今回は実際にサプリメンタル・ロギングの設定を行いながら、どの
ような設定を行った場合に、どのような結果になるのかを確認していきたい
と思います。
▼ 環境のおさらい
OS：Red Hat Enterprise Linux ES release 3
DB：10gR2(10.2.0.1)
おなじみの DEPT表 を用意しました。
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ; 用意したデータは以下となります。 SQL> select * from dept; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 SALES4 CHIGASAKI
▼ サプリメンタル・ロギングを有効にしてみよう！！
まずは構文です。
いくつかのオプションがあることが確認できます。
※サプリメンタル・ロギングはデータベースレベル、表レベルで設定が可能で
すが今回はデータベースレベルでのみ確認を行っていく事にします。
[追加]
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (&option) COLUMNS
[削除]
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (&option) COLUMNS
option=[PRIMARY KEY|UNIQUE KEY|FOREIGN KEY|ALL]
1) 最小サプリメンタル・ロギングのみ “YES” の状態
SQL> alter database add supplemental log data; v$databaseのSUPPLEMENTAL_LOG_DATA_MIN、SUPPLEMENTAL_LOG_DATA_PK、 SUPPLEMENTAL_LOG_DATA_ALL列を参照することで現在の設定状態を確認する ことができます。 MIN PK ALL -------- --- --- YES NO NO SQL> update dept set DNAME='SALES5' where DEPTNO=50;
ログマイナーを起動して、v$logmnr_contentsを参照します。
sql_redo列を確認することによって実際にどのようなDMLが実行されたのか
を確認することが可能です。ただし、誤解の無い様にお伝えしておきますと
v$logmnr_contentsは更新行単位で問い合わせ結果を返却します。仮に100行
のデータを条件指定無しで更新を行った場合には100行の結果が返されます。
よって、実際に発行されたSQLは「update 表 set=値 」であったとしても、
それぞれのv$logmnr_contents.sql_redo列は「update 表 set=値 where
条件(行を一意に特定する)」となります。
SQL_REDO ---------------------------------------------------------------------- update "METABON"."DEPT" set "DNAME" = 'SALES5' where "DNAME" = 'SALES4 ' and ROWID = 'AAAC6nAAEAAAAi/AAE';
2) 1)の状態から、行の更新があった場合に、主キー情報を保持するオプシ
ョン 「PRIMARY KEY」を指定します。
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; データベースが変更されました。 MIN PK ALL -------- --- --- YES YES NO ログマイナーを起動して、v$logmnr_contentsのsql_redo列を参照します。 SQL> update dept set DNAME='SALES6' where DEPTNO=50; ログマイナーを起動して、v$logmnr_contentsを参照します。 SQL_REDO ---------------------------------------------------------------------- update "METABON"."DEPT" set "DNAME" = 'SALES6' where "DEPTNO" = '50' a nd "DNAME" = 'SALES5' and ROWID = 'AAAC6nAAEAAAAi/AAE';
where句に”DEPTNO”列が増えており、
主キーの情報を確認することができますね。
3) さらに、更新処理があった場合に行の全ての列のビフォアイメージを保持
するオプション「ALL」を指定してみます。
SQL> ALTER DATABASE add SUPPLEMENTAL LOG DATA (ALL) COLUMNS; データベースが変更されました。 MIN PK ALL -------- --- --- YES YES YES SQL> update dept set DNAME='SALES7' where DEPTNO=50; ログマイナーを起動して、v$logmnr_contentsを参照します。 SQL_REDO ---------------------------------------------------------------------- update "METABON"."DEPT" set "DNAME" = 'SALES7' where "DEPTNO" = '50' a nd "DNAME" = 'SALES6' and "LOC" = 'CHIGASAKI' and ROWID = 'AAAC6nAAEAA AAi/AAE'; ~~~~~~~~~~~~~~~~~~~
2)のwhere句に加えて、「”LOC” = ‘CHIGASAKI’」も追加されていますね。
これで、DEPT表の全ての列の状態がwhere句で確認可能になっています。
▼ まとめ
今回は、サプリメンタル・ロギングオプション「PRIMARY KEY」「ALL」のみ
の確認でしたがサプリメンタル・ロギングがどういったものかイメージを掴
んで頂くことができましたでしょうか。
ちょっと想像すると、これを上手に使えれば v$logmnr_contentsの検索に
すごーく幅がでてきそう予感がしますよね。
sql_redo列を確認すれば更新列以外の状態も知ることができるのですから、
上記の例で言えば「 部署の場所が茅ヶ崎にあった時代に部署名がどのように
遷移していったか？」なーんて事は簡単に引っ張ってこれそうです。
ただし、余分なデータをREDOログに書出すオーバーヘッドが出てくるわけです
から必要最低限の設定が望ましいと考えられます。
今回はここまでです。
スポーツジム代が勿体ない 茅ヶ崎より