goodbyegangsterのブログ

備忘録的な

OracleDBでサプリメント・ロギング機能を利用する

OracleDBでサプリメント・ロギング機能を利用した時に調べたメモ。

サプリメント・ロギングとは

REDOログに、追加の情報を出力してくれる機能です。

一般に、REDOログ・ファイルは、インスタンスリカバリおよびメディア・リカバリに使用されます。これらの操作に必要なデータは、REDOログ・ファイルに自動的に記録されます。ただし、REDOベースのアプリケーションでは、追加の列をREDOログ・ファイルに記録する必要がある場合があります。これらの追加の列を記録するプロセスは、サプリメンタル・ロギングと呼ばれます。

サプリメンタル・ロギング

いわゆるアクティブログの内容を利用して、DB間でデータ連携するようなツールを使用する際に、利用される機能となります。

設定の確認

以下のSQLで現在の設定を確認できます。

> set linesize 100
> col supplemental_log_data_min format a30
> col supplemental_log_data_pk format a30
> col supplemental_log_data_ui format a30
> SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui FROM v$database;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

各カラムの説明。

  • SUPPLEMENTAL_LOG_DATA_MIN
    • 最小限のサプリメンタル・ロギングが使用可能かどうか
  • SUPPLEMENTAL_LOG_DATA_PK
    • 主キーを持つすべての表について、更新が実行されるたびに、すべての主キー列がREDOログに書き込まれるかどうか
  • SUPPLEMENTAL_LOG_DATA_UI
    • 一意キーを持つすべての表について、一意キー列のいずれかが変更されるたびに、その一意キーに属する他のすべての列がREDOログに書き込まれるかどうか

サプリメンタル・ロギングの有効化

サプリメンタル・ロギングを有効とする範囲は、以下の範囲から選べます。

  • データベース・レベル
  • 表レベル

データベースレベルのサプリメンタル・ロギングの有効化

データベース・レベルでサプリメンタル・ロギングを有効化する場合、以下のログレベルをを設定可能です。

  • 最小サプリメンタル・ロギング
    • サプリメンタル・ロギングを実施する上で必須となる
    • その名の通り、最小レベルのログを出力
  • 識別キー・ロギング
    • ALL
      • 行が更新された場合、その行のすべての列(LOB、LONGS、ADTを除く)がREDOログ・ファイルに格納される
    • PRIMARY KEY
      • 主キーを含む行が更新された場合(主キーの値に変更がない場合も)、主キーのすべての列がREDOログ・ファイルに格納される
    • UNIQUE
      • コンポジット一意キーまたはビットマップ索引に属する列が変更された場合、行のコンポジット一意キーまたはビットマップ索引のすべての列がREDOログ・ファイルに格納される
    • FOREIGN KEY
      • 外部キーに属する列が変更された場合、行の外部キーのすべての列がREDOログ・ファイルに格納される

最小サプリメンタル・ロギングの設定。

> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

PRIMARY KEY による、識別キー・ロギングの設定。

> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

識別キー・ロギングを利用する場合の注意点です。

データベース全体の識別キー・ロギングを有効にすると、REDOログ・ファイルを生成するデータベースにオーバーヘッドが発生する場合があります。

識別キー・ロギングが有効な場合にデータベースがオープンしていると、カーソル・キャッシュ内のすべてのDMLカーソルが無効になります。したがって、カーソル・キャッシュに再移入を行うまで、パフォーマンスに影響する場合があります。

データベース・レベルで識別キー・ロギングを有効にすると、最小サプリメンタル・ロギングが暗黙で有効になります。

データベース・レベルのサプリメンタル・ロギング

表レベルのサプリメンタル・ロギングの有効化

表レベルのサプリメンタル・ロギングですが、基本的にデータベースレベルで設定する内容を表単位で設定できる、という内容になります。

表レベルでの識別キー・ロギングでは、データベース・レベルの場合と同じオプション(すべて、主キー、外部キー、一意キー)が提供されます。ただし、表レベルで識別キー・ロギングを指定すると、指定した表のみが影響を受けます。

実行SQL例がこちら。

> ALTER TABLE (schema).(table) ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

LogMinerの使用

LogMinerというOracle機能を利用することで、REDOログに出力された内容を確認することができます。具体的には、LogMinerの機能を利用するには、 DBMS_LOGMNR というパッケージを利用するということになります。

以下が手順です。実行するDBユーザには LOGMINING管理者権限 が必要とのことですが、とりあえずsysユーザでconnectしています。

ADD_LOGFILEプロシージャを利用して、検索対象のREDOログを指定します。

> EXECUTE dbms_logmnr.add_logfile( LogFileName => '/data/orcl/redo01.log', options => dbms_logmnr.new );

ADD_LOGFILEプロシージャ

START_LOGMNRプロシージャで、LogMinerを起動します。この時に、REDOログを解析するための、現在のOracleのディクショナリ情報を渡してあげる必要があります。以下の例では、オンライン・カタログを利用して、REDOログの解析を試みるものです。

> EXECUTE dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog);

START_LOGMNRプロシージャ

オンライン・カタログの使用

ログの中身を検索するには、 V$LOGMNR_CONTENTS を検索します。以下のように、過去に実行されたSQL文を取得することができます。

> set linesize 140
> col timestamp format a10
> col table_name format a15
> col operation format a20
> col sql_redo format a80
> SELECT scn, timestamp, table_name, sql_redo FROM v$logmnr_contents WHERE table_name = 'SAMPLE';

       SCN TIMESTAMP  TABLE_NAME      SQL_REDO
---------- ---------- --------------- --------------------------------------------------------------------------------
   1734552 26-NOV-19  SAMPLE          create table sample.sample (col1 int, col2 int, primary key(col1));
   1735652 26-NOV-19  SAMPLE          insert into "SAMPLE"."SAMPLE"("COL1","COL2") values ('1','1');
   1735657 26-NOV-19  SAMPLE          insert into "SAMPLE"."SAMPLE"("COL1","COL2") values ('2','2');
   1735662 26-NOV-19  SAMPLE          insert into "SAMPLE"."SAMPLE"("COL1","COL2") values ('3','3');

V$LOGMNR_CONTENTS

END_LOGMNRプロシージャにて、LogMinerを終了します。

> EXECUTE dbms_logmnr.end_logmnr;

END_LOGMNRプロシージャ

サプリメンタル・ロギングの無効化

サプリメンタル・ロギングを無効化する方法はこちら。

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベース・レベルのサプリメンタル・ロギングの無効化