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で削除してくれます。