PL/SQLで業務イベントを正しく記録しつつ、問題発生時に実行経路を迅速に再現できる仕組みを整えるには、アプリケーションロギングとデータベース監査、さらに実行トレースの三層を目的別に設計して整合させることが不可欠である。ロギングは業務文脈を残し、監査は責任追跡性を保証し、トレースは性能と不具合の根因分析を担う。これらを無秩序に重ねると過剰な書き込みや可観測性の断絶を招くため、本稿では各レイヤの役割分担を明確にし、相関IDとアプリケーションコンテキストを軸に統一する監査設計を解説する。
目的と設計原則
監査設計の目的は、いつ誰が何を行い、その結果がどうなったかを技術的にも業務的にも再構成できる状態を保つことにある。PL/SQLでは、アプリケーションロギングで業務イベントを整形し、Unified AuditingやFGAで規制要件に応える技術監査を付与し、DBMS_APPLICATION_INFOとDBMS_MONITORにより実行経路の追跡性を補完する。これらは同一の相関IDを共有し、V$SESSIONやログテーブル、監査ビューから同一の操作を縦断的にたどれるようにする。パフォーマンスやトランザクション一貫性を損なわないために、業務ログの書き込みは自律トランザクションで非介入的に行い、詳細トレースはモジュール単位のオンデマンドで有効化する方針が有効である。
監査対応ロギングのデータモデル
アプリケーションログは業務主語で検索しやすい正規化を基本としつつ、ペイロードはJSONとして柔軟に保持すると運用が安定する。発生時刻と相関ID、主体、対象、結果、重要度、モジュール、アクションをキーに、可変情報をJSONへ格納する。JSON列にはチェック制約を付け、検索用に仮想列を付与してインデックス最適化を行う。
CREATE TABLE app_log (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
log_ts TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
severity VARCHAR2(10) CHECK (severity IN ('DEBUG','INFO','WARN','ERROR','FATAL')),
module VARCHAR2(48),
action VARCHAR2(32),
client_id VARCHAR2(64),
correlation_id VARCHAR2(64),
user_name VARCHAR2(128),
target_key VARCHAR2(128),
result_code VARCHAR2(64),
message VARCHAR2(512),
payload_json CLOB CHECK (payload_json IS JSON),
source_line VARCHAR2(256)
)
LOB (payload_json) STORE AS SECUREFILE (ENABLE STORAGE IN ROW);
ALTER TABLE app_log ADD CONSTRAINT app_log_pk PRIMARY KEY (log_id);
CREATE INDEX app_log_ix1 ON app_log (correlation_id, log_ts);
CREATE INDEX app_log_ix2 ON app_log (module, action, log_ts);
アプリケーションコンテキストと相関IDの統一
同じ操作をログ、監査、トレースで結びつけるには、V$SESSIONに露出するモジュール、アクション、クライアント識別子と、アプリケーションコンテキスト及び相関IDを同時に設定する。相関IDは一連の要求を一意に表し、親子処理にも引き継がれるべきである。
CREATE OR REPLACE CONTEXT app_ctx USING pkg_app_ctx;
/
CREATE OR REPLACE PACKAGE pkg_app_ctx AUTHID DEFINER AS
PROCEDURE init(p_correlation_id IN VARCHAR2, p_client_id IN VARCHAR2,
p_module IN VARCHAR2, p_action IN VARCHAR2);
FUNCTION correlation_id RETURN VARCHAR2;
END pkg_app_ctx;
/
CREATE OR REPLACE PACKAGE BODY pkg_app_ctx AS
PROCEDURE init(p_correlation_id IN VARCHAR2, p_client_id IN VARCHAR2,
p_module IN VARCHAR2, p_action IN VARCHAR2) IS
BEGIN
DBMS_SESSION.set_context('APP_CTX','CORRELATION_ID',p_correlation_id);
DBMS_SESSION.set_identifier(p_client_id);
DBMS_APPLICATION_INFO.set_module(p_module, p_action);
END;
FUNCTION correlation_id RETURN VARCHAR2 IS
BEGIN
RETURN SYS_CONTEXT('APP_CTX','CORRELATION_ID');
END;
END pkg_app_ctx;
/
自律トランザクションで壊れにくいロガーを実装する
業務トランザクションのロールバックに巻き込まれず、例外時でも確実に残るログが必要な場面では、自律トランザクションを用いる。エラーが発生してもFORMAT_ERROR_BACKTRACEとFORMAT_CALL_STACKで現場情報を添えてコミットし、過剰なI/Oを避けるためにメッセージの最大長やサンプリングを制御する。
CREATE OR REPLACE PACKAGE pkg_logger AUTHID DEFINER AS
PROCEDURE log_info(p_msg IN VARCHAR2, p_payload IN CLOB DEFAULT NULL);
PROCEDURE log_error(p_msg IN VARCHAR2, p_payload IN CLOB DEFAULT NULL);
END pkg_logger;
/
CREATE OR REPLACE PACKAGE BODY pkg_logger AS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE write_row(p_sev IN VARCHAR2, p_msg IN VARCHAR2, p_payload IN CLOB) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO app_log(
severity, module, action, client_id, correlation_id,
user_name, target_key, result_code, message, payload_json, source_line
) VALUES (
p_sev,
SYS_CONTEXT('USERENV','MODULE'),
SYS_CONTEXT('USERENV','ACTION'),
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'),
pkg_app_ctx.correlation_id,
SYS_CONTEXT('USERENV','SESSION_USER'),
NULL,
NULL,
SUBSTR(p_msg,1,512),
p_payload,
DBMS_UTILITY.format_error_backtrace || ' | ' || DBMS_UTILITY.format_call_stack
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
PROCEDURE log_info(p_msg IN VARCHAR2, p_payload IN CLOB DEFAULT NULL) IS
BEGIN
write_row('INFO', p_msg, p_payload);
END;
PROCEDURE log_error(p_msg IN VARCHAR2, p_payload IN CLOB DEFAULT NULL) IS
BEGIN
write_row('ERROR', p_msg, p_payload);
END;
END pkg_logger;
/
例外捕捉の標準化とバックトレース
実運用ではエラーが投げられた直後に相関IDと実行文脈を付与して記録し、呼び出し元へ再送出する。これにより上位層の再試行や補償処理と、監査上の事実記録を両立できる。
CREATE OR REPLACE PACKAGE pkg_order AS
PROCEDURE place_order(p_order_id IN NUMBER);
END pkg_order;
/
CREATE OR REPLACE PACKAGE BODY pkg_order AS
PROCEDURE place_order(p_order_id IN NUMBER) IS
BEGIN
pkg_logger.log_info('start place_order', '{"order_id":' || p_order_id || '}');
-- ビジネスロジック
NULL;
pkg_logger.log_info('end place_order', '{"order_id":' || p_order_id || '}');
EXCEPTION
WHEN OTHERS THEN
pkg_logger.log_error(SQLERRM, '{"order_id":' || p_order_id || '}');
RAISE;
END;
END pkg_order;
/
オンデマンドトレースの制御と収集
恒常的な10046イベントは過剰なので、DBMS_MONITORでサービスやモジュール単位に限定して有効化する。事前にset_moduleしておけば、特定機能の実行経路だけを詳細収集できる。終了時は必ず無効化し、トレースファイルは相関IDでフィルタできるようログ側にモジュールとアクションを残す。
BEGIN
DBMS_APPLICATION_INFO.set_module('ORDER','PLACE');
DBMS_MONITOR.serv_mod_act_trace_enable(
service_name => NULL,
module_name => 'ORDER',
action_name => 'PLACE',
waits => TRUE,
binds => TRUE
);
pkg_order.place_order(1001);
DBMS_MONITOR.serv_mod_act_trace_disable(
service_name => NULL,
module_name => 'ORDER',
action_name => 'PLACE'
);
END;
/
統合監査の適用とFGAによる列単位監視
規制やセキュリティ要件にはデータベース監査を適用し、アプリロギングとは独立に真正性を確保する。Unified Auditingではポリシを定義して有効化し、FGAでは列参照や条件式に基づく詳細な監査を実現する。アプリの相関IDはCLIENT IDENTIFIERに写し、監査ビューとアプリログを突合する前提を整える。
-- Unified Auditing (例: 顧客テーブルのSELECT/UPDATEを監査)
CREATE AUDIT POLICY pol_customer_acc
ACTIONS SELECT, UPDATE ON app.customer
WHEN 'SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'') IS NOT NULL';
AUDIT POLICY pol_customer_acc;
-- Fine-Grained Auditing (例: 機微列に対する参照のみ監査)
BEGIN
DBMS_FGA.add_policy(
object_schema => 'APP',
object_name => 'CUSTOMER',
policy_name => 'cust_fga',
audit_condition => 'new_value IS NOT NULL',
audit_column => 'CARD_NO,SSN',
handler_module => NULL,
enable => TRUE
);
END;
/
相関分析と運用クエリ
障害調査では相関IDと時刻範囲で横断検索し、アプリログと監査記録を統合して時系列解釈する。JSONの抽出はJSON_VALUEを使い、業務キーでの合流を容易にする。必要に応じてアクセスの多い抽出キーは機能別の仮想列と関数インデックスで加速する。
-- 相関IDでアプリログを検索
SELECT log_ts, severity, module, action, message
FROM app_log
WHERE correlation_id = :cid
ORDER BY log_ts;
-- 監査ビュー（UNIFIED_AUDIT_TRAIL）との粗結合
SELECT a.event_timestamp, a.dbusername, a.action_name, a.object_schema, a.object_name,
l.message
FROM unified_audit_trail a
LEFT JOIN app_log l
ON l.correlation_id = a.client_identifier
WHERE a.client_identifier = :cid
ORDER BY a.event_timestamp;
保守とパフォーマンスの均衡
ログは保持要件を満たす最小限で運用し、パーティションや圧縮を活用して肥大化を抑える。高頻度処理ではDEBUGレベルを抑制し、INFO以上の重要イベントとERRORの事実記録に集中させる。ローテーションと再編成はDBMS_SCHEDULERで日次に実行し、業務時間帯を避けて実施する。自律トランザクションの書き込みはコミットが多いため、集約可能なイベントは上位層でまとめるなどアプリ側の工夫も有効である。
BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'JOB_APP_LOG_PURGE',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_cutoff TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP - INTERVAL '90' DAY;
BEGIN
DELETE FROM app_log WHERE log_ts < v_cutoff AND severity <> 'FATAL';
COMMIT;
END;]',
start_date => SYSTIMESTAMP + INTERVAL '5' MINUTE,
repeat_interval => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0',
enabled => TRUE
);
END;
/
検証手順とリスク低減
導入前には負荷試験でログ書き込みオーバーヘッドを計測し、INFO比率と自律トランザクションのコミットレートを監視しながら閾値を調整する。トレース有効化はステージングでモジュール単位に実験し、バインドと待機イベントの収集コストと得られる診断価値の釣り合いを評価する。監査ポリシは過剰なイベント発生でSYSAUX表領域を圧迫し得るため、条件式やアクション対象を業務要件に沿って最小化する。
まとめ
ロギングは業務文脈の再構成、監査は真正性と責任追跡、トレースは性能と根因分析という役割を持ち、それぞれが相関IDと実行文脈で結び付くことで初めて価値を最大化する。PL/SQLではDBMS_APPLICATION_INFOとアプリケーションコンテキストで経路情報を統一し、自律トランザクションのロガーで壊れにくい事実記録を担保し、Unified AuditingとFGAで規制要件に耐える記録を補強する。オンデマンドのトレース運用と適切な保守計画を併用すれば、トラブルシュートの即応性と運用コストの均衡を保ちながら、長期運用に耐える監査設計を実現できる。
