【Oracle】補足ロギング（Supplemental Logging）完全ガイド｜LogMiner・GoldenGate 向けの設定と DBA_LOG_GROUPS での確認方法まで解説

-- 現在の補足ロギング設定を確認する（SYS / SELECT ANY DICTIONARY 権限が必要）
SELECT
    name,
    db_unique_name,
    supplemental_log_data_min,   -- YES/NO: 最小補足ロギング
    supplemental_log_data_pk,    -- YES/NO: 主キー補足ロギング
    supplemental_log_data_ui,    -- YES/NO: 一意インデックス補足ロギング
    supplemental_log_data_fk,    -- YES/NO: 外部キー補足ロギング
    supplemental_log_data_all    -- YES/NO: 全列補足ロギング
FROM V$DATABASE;

-- 最小補足ロギングを有効にする（LogMiner の最低要件）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- 効果: トランザクション識別に必要な最小限の情報を追加する

-- 主キー補足ロギングを有効にする（GoldenGate の推奨設定）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-- 効果: 主キー列の変更前値を全 DML に記録する（UPDATE/DELETE 時に主キー値が確実に記録される）

-- 一意インデックス列の補足ロギングを有効にする
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;

-- 外部キー列の補足ロギングを有効にする
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

-- 全列の補足ロギングを有効にする（最大の情報量・REDO ログサイズへの影響が大きい）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- 複数をまとめて有効にする（GoldenGate 標準設定）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

-- 補足ロギングを無効にする（段階的に解除する）
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

-- 最小補足ロギングはデータベースレベルで有効にしておく必要がある
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- テーブルの全列に補足ロギングを設定する（テーブルレベル ALL）
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- 主キー列のみ補足ロギングを設定する（テーブルレベル PRIMARY KEY）
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

-- 特定列に補足ロググループを作成する（名前付きロググループ）
-- ALWAYS: DML のたびにこのグループの全列を記録する
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG GROUP emp_suplog_grp (
    employee_id,
    department_id,
    salary
) ALWAYS;

-- ロググループを削除する
ALTER TABLE hr.employees DROP SUPPLEMENTAL LOG GROUP emp_suplog_grp;

-- テーブルレベルの補足ロググループを確認する
SELECT
    log_group_name,
    table_name,
    log_group_type,      -- USER LOG GROUP / ALL COLUMN LOGGING など
    always               -- ALWAYS: 常に記録 / CONDITIONAL: 列変更時のみ
FROM DBA_LOG_GROUPS
WHERE owner = 'HR'
ORDER BY table_name, log_group_name;

-- ロググループに含まれる列を確認する
SELECT
    lg.log_group_name,
    lgc.column_name,
    lgc.position,
    lgc.log_group_column_type   -- LOG: 記録する / NO LOG: 記録しない
FROM DBA_LOG_GROUPS lg
JOIN DBA_LOG_GROUP_COLUMNS lgc
    ON lg.owner = lgc.owner AND lg.log_group_name = lgc.log_group_name
WHERE lg.owner = 'HR'
ORDER BY lg.table_name, lg.log_group_name, lgc.position;

-- 補足ロギング設定前後の REDO 生成量を AWR で比較する
SELECT
    begin_interval_time,
    end_interval_time,
    redo_size_delta / 1024 / 1024 AS redo_mb_delta
FROM (
    SELECT
        s.begin_interval_time,
        s.end_interval_time,
        (e.value - b.value) AS redo_size_delta
    FROM DBA_HIST_SYSSTAT e
    JOIN DBA_HIST_SYSSTAT b
        ON  e.snap_id = b.snap_id + 1
        AND e.instance_number = b.instance_number
        AND e.stat_name = b.stat_name
    JOIN DBA_HIST_SNAPSHOT s
        ON  e.snap_id = s.snap_id
        AND e.instance_number = s.instance_number
    WHERE e.stat_name = 'redo size'
)
ORDER BY begin_interval_time DESC
FETCH FIRST 24 ROWS ONLY;

-- アーカイブログのサイズを確認する（V$ARCHIVED_LOG）
SELECT
    TRUNC(first_time, 'HH24') AS hour,
    COUNT(*) AS log_count,
    SUM(blocks * block_size) / 1024 / 1024 AS total_mb
FROM V$ARCHIVED_LOG
WHERE first_time >= SYSDATE - 7
  AND standby_dest = 'NO'
GROUP BY TRUNC(first_time, 'HH24')
ORDER BY hour DESC
FETCH FIRST 48 ROWS ONLY;