Oracle DBで監査情報を取得するため、統合監査を設定しています。
ただ、取得するだけならば問題ないのですが、日々の膨大な監査情報を蓄積していくため、システムによっては1日で数百GBの監査情報を出力する場合があるかと思います。

そのため、保存期間を決めて、その期間を過ぎたものは削除する運用が必要になってきます。
今回は日次で監査情報を削除していく方法と注意点を紹介します。

【目次】

統合監査表の構造
統合監査レコードの削除方法
DROP PARTITIONとDELETE
日次のDROP PARTITIONで削除するための注意点

【RDBMSバージョン】
ORACLE DATABASE 19c

統合監査表の構造

統合監査表はデフォルトでは月次の時間隔パーティションで構成されています。

このパーティションの構成は日次、月次、年次といった形に変更することが可能です。
変更するには専用のプロシージャから変更する必要があります。

変更方法は以下。

#日ごとのパーティションを1日間隔で作成
SQL>
BEGIN
  DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
    interval_number       => 1,
    interval_frequency    => 'DAY');
  END;
/

統合監査レコードの削除方法

まずは現在の統合監査表（AUD$UNIFIED）の状態を確認していきます。

SQL> set linesize 150 pages 50000
SQL> col table_name for a15
SQL> col partition_name for a15
SQL> col high_value for a35
SQL> select table_name, partition_name, high_value, interval from dba_tab_partitions where table_name = 'AUD$UNIFIED';

TABLE_NAME	PARTITION_NAME	HIGH_VALUE			    INT
--------------- --------------- ----------------------------------- ---
AUD$UNIFIED	SYS_P42459	TIMESTAMP' 2023-01-30 00:00:00'     NO
AUD$UNIFIED	SYS_P42691	TIMESTAMP' 2023-01-31 00:00:00'     YES
AUD$UNIFIED	SYS_P42730	TIMESTAMP' 2023-02-01 00:00:00'     YES

統合監査表（AUD$UNIFIED）に対して、通常のDELETEやTRUNCATE、DROP PARTITIONによる処理は許可されていないため、エラーとなります。

SQL> exec dbms_pdb.exec_as_oracle_script('alter table audsys.aud$unified drop partition SYS_P42459');
BEGIN dbms_pdb.exec_as_oracle_script('alter table audsys.aud$unified drop partition SYS_P42459'); END;

*
行1でエラーが発生しました。:
ORA-00604: 再帰SQLレベル1でエラーが発生しました。 ORA-46385:
表"AUDSYS"."AUD$UNIFIED"ではDMLおよびDDL操作は許可されていません。 ORA-06512:
"SYS.DBMS_PDB", 行42
ORA-06512: 行1


SQL>

削除するためには専用のプロシージャを実行することで削除が可能となります。
その前に、LAST_ARCHIVE_TIMESTAMPを設定していきます。

SQL>
#LAST_ARCHIVE_TIMESTAMPの設定
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time    => TO_TIMESTAMP('2023/01/30 00:00:01.00','YYYY-MM-DD HH24:MI:SS.FF'));
END;
/

LAST_ARCH_TIMESTAMPはこの時点までアーカイブしたという印で、LAST_ARCHIVE_TIMESTAMPより前のタイムスタンプのレコードは削除してもOKということになります。

SQL>
#LAST_ARCHIVE_TIMESTAMPを基に監査表を削除
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
    audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
    use_last_arch_timestamp => true);
END;
/

引数としては、削除対象の監査表とLAST_ARCHIVE_TIMESTAMPを基に削除するかどうか（true or false)の設定となります。
今回はuse_last_arch_timestamp => trueで実施していますが、falseに設定することで、LAST_ARCH_TIMESTAMPに関係なく、全ての監査レコードを削除します。

DROP PARTITIONとDELETE

この削除プロシージャを実行すると、どのように削除しているのでしょうか。

10046トレースを設定して出力されたトレースを確認してみます。

SQL>
#トレースファイルの設定
alter session set tracefile_identifier='10046';
alter session set max_dump_file_size = UNLIMITED;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

#削除プロシージャの実行
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
    audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
    use_last_arch_timestamp => true);
  END;
/

#トレースファイルの設定解除
alter session set events '10046 trace name context off';

トレースを確認すると、LAST_ARCHIVE_TIMESTAMPより古いパーティションをDROP PARTITIONで削除し、その後でDELETEでLAST_ARCHIVE_TIMESTAMPより古いレコードを削除していることが確認できます。

まずはDROP PARTITIONで削除できるものは削除して、DROP PARTITIONできないパーティション内のLAST_ARCHIVE_TIMESTAMPより古いレコードを削除している感じですね。

日次のDROP PARTITIONで削除するための注意点

一日で大量の監査レコードを出力するため、運用では毎日、削除処理を実施したい要件のシステムもあると思います。

私が担当したシステムで、DB内には10日間、OS上に30日、オブジェクトストレージに2年間保持するといった要件のものがありました。

毎日、数百GBの監査レコードを出力するため、毎日、期限切れのパーティションを削除して運用したいと考えて設定していました。

ただ、実際にシステムが稼働してみると、本来であればDROP PARTITIONで削除してほしいところが、DELETE処理になっているため、何日もDELETE処理が流れ続けるといったことがありました。

原因はパーティションの境界値とLAST_ARCHIVE_TIMESTAMPの設定の問題で、パーティションの境界値であるyyyy/mm/dd 00:00:00でLAST_ARCHIVE_TIMESTAMPを設定すると、dd日のパーティションに対してはDROP PARTITIONではなくDELETEで削除を実施します。

これを解消するには、LAST_ARCH_TSを＋1秒マージンを設けて、yyyy/mm/dd 00:00:01とすることでdd日のパーティションをDROP PARTITIONで削除してくれます。