【Oracle】LogMiner完全ガイド｜DBMS_LOGMNR で REDO ログを解析して変更履歴を調査する方法まで解説

-- LogMiner を実行するユーザーに必要な権限
-- ① EXECUTE ON DBMS_LOGMNR（ログ解析の実行）
-- ② EXECUTE ON DBMS_LOGMNR_D（データディクショナリ操作）
-- ③ SELECT ANY TRANSACTION（全トランザクションへのアクセス）
-- ④ LOGMINING（Oracle 12c 以降、推奨）

-- SYS または DBA が権限を付与する
GRANT LOGMINING TO logminer_user;
GRANT EXECUTE ON DBMS_LOGMNR TO logminer_user;
GRANT EXECUTE ON DBMS_LOGMNR_D TO logminer_user;
GRANT SELECT ANY TRANSACTION TO logminer_user;

-- アーカイブモードが有効かどうかを確認する（LogMiner には推奨）
ARCHIVE LOG LIST;
-- ARCHIVELOG モードが望ましい（NOARCHIVELOG ではオンラインREDOログのみ解析可能）

-- サプリメンタルロギングの状態を確認する
SELECT log_mode, supplemental_log_data_min
FROM V$DATABASE;
-- SUPPLEMENTAL_LOG_DATA_MIN = YES → 最小サプリメンタルロギングが有効

-- データベース全体の最小サプリメンタルロギングを有効にする
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- これで LogMiner で ROW_ID が取得可能になる

-- すべての列の変更前値を記録する（完全サプリメンタルロギング）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ALL COLUMNS;

-- 特定のテーブルのみ有効にする（推奨: 対象を絞る）
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA ALL COLUMNS;

-- テーブルのサプリメンタルロギング設定を確認する
SELECT table_name,
       log_group_name,
       log_group_conditional,
       always
FROM DBA_LOG_GROUP_COLUMNS
WHERE owner = 'HR'
ORDER BY table_name;

-- データベースレベルの設定を確認する
SELECT name, value FROM V$PARAMETER WHERE name LIKE 'enable_goldengate%';

-- オンライン REDO ログのパスを確認する
SELECT member FROM V$LOGFILE ORDER BY group#;

-- アーカイブログのパスを確認する（期間指定）
SELECT name, first_time, next_time
FROM V$ARCHIVED_LOG
WHERE first_time >= TRUNC(SYSDATE) - 1   -- 昨日以降
  AND standby_dest = 'NO'
ORDER BY sequence#;

-- LogMiner にログファイルを追加する
BEGIN
    -- 最初のファイル: DBMS_LOGMNR.NEW で新規セッションとして追加
    DBMS_LOGMNR.ADD_LOGFILE(
        logfilename => '/u01/app/oracle/archivelog/arch_001.arc',
        options     => DBMS_LOGMNR.NEW
    );

    -- 追加ファイル: ADDFILE で続けて追加
    DBMS_LOGMNR.ADD_LOGFILE(
        logfilename => '/u01/app/oracle/archivelog/arch_002.arc',
        options     => DBMS_LOGMNR.ADDFILE
    );
END;
/

-- LogMiner セッションを開始する
BEGIN
    DBMS_LOGMNR.START_LOGMNR(
        -- 解析時間範囲を指定する方法（ADD_LOGFILE でファイルを追加している場合）
        starttime   => SYSTIMESTAMP - INTERVAL '1' DAY,   -- 1日前から
        endtime     => SYSTIMESTAMP,                         -- 現在まで
        options     =>
            DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG           -- データディクショナリの取得方法
            + DBMS_LOGMNR.COMMITTED_DATA_ONLY              -- コミット済みデータのみ
            + DBMS_LOGMNR.SKIP_CORRUPTION                  -- 破損ブロックをスキップ
    );
END;
/

-- オプションの説明:
-- DICT_FROM_ONLINE_CATALOG : オンラインカタログを使用（現在の定義で解析）
-- DICT_FROM_REDO_LOGS      : ログ内のデータディクショナリを使用（過去の定義で解析）
-- COMMITTED_DATA_ONLY      : コミット済みトランザクションのみ返す
-- NO_ROWID_IN_STMT         : SQL 文に ROWID を含めない
-- DDL_DICT_TRACKING        : DDL 変更もトラッキングする

-- 特定テーブルへの DML 変更履歴を確認する
SELECT
    timestamp,           -- 変更のタイムスタンプ
    seg_owner,           -- テーブル所有者
    seg_name,            -- テーブル名
    operation,           -- INSERT / UPDATE / DELETE / DDL
    username,            -- 実行ユーザー
    os_username,         -- OS ユーザー名
    sql_redo,            -- 変更を再現する SQL（REDO）
    sql_undo,            -- 変更を取り消す SQL（UNDO）
    row_id               -- 対象行の ROWID
FROM V$LOGMNR_CONTENTS
WHERE seg_name = 'EMPLOYEES'
  AND seg_owner = 'HR'
  AND operation IN ('INSERT', 'UPDATE', 'DELETE')
ORDER BY timestamp;

-- 特定ユーザーの操作を抽出する
SELECT timestamp, operation, seg_name, sql_redo
FROM V$LOGMNR_CONTENTS
WHERE username = 'APP_USER'
  AND operation != 'SELECT_FOR_UPDATE'
  AND seg_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY timestamp;

-- LogMiner セッションを終了する（SGA リソースを解放）
BEGIN
    DBMS_LOGMNR.END_LOGMNR;
END;
/

-- 誤って削除されたレコードの INSERT SQL を LogMiner から取得する例
SELECT sql_undo
FROM V$LOGMNR_CONTENTS
WHERE seg_name   = 'EMPLOYEES'
  AND seg_owner  = 'HR'
  AND operation  = 'DELETE'
  AND timestamp >= SYSTIMESTAMP - INTERVAL '1' HOUR
ORDER BY timestamp;
-- sql_undo 列には "INSERT INTO HR.EMPLOYEES (col1, col2, ...) VALUES (...)" が含まれる
-- この SQL を実行することで削除前の状態に戻せる

-- 注意: UNDO SQL の実行前に必ずデータの整合性を確認すること
-- ・外部キー制約・順序・トリガーの影響を検討する
-- ・FLASHBACK 機能で対応できる場合は Flashback を優先する