Oracle では FGA 監査を利用している場合は select 文でも REDO ログは生成される

いきなり回答。監査を設定している場合には、当然の事ながら監査ログが出力されます。出力先を OS ではなく Oracle にしている場合、select 文を流すだけでも結果的に REDO ログ(監査ログの insert 分について)、アーカイブログが生成されます。

これ見落としがちですが、かなり注意が必要です。
以下詳細です。結論からすると、上記の前提を単に見落としていただけなのだが、これには正直参った。


Oracle を使う理由のひとつとして監査ログ機能の充実があります。Oracle 9i 以降を業務でお使いの方は、機微な情報を持つテーブルに対しては何かしらの監査を行っていることと思います。
10g では監査の種類として以下のようなものが用意されています。
※参考ページ

- スポンサーリンク -

必須監査

常に取得される監査情報。管理者権限によるインスタンスへの接続や、DB の起動/停止を監査。ログ出力先は AUDIT_FILE_DEST で指定。

DBA監査

AUDIT_SYS_OPERATIONS 初期化パラメータを TRUE に設定することで、SYS で接続したユーザ(SYSDBA または SYSOPER で接続したすべてのユーザを含む)のセッションを監査。ログ出力先は AUDIT_FILE_DEST で指定。

標準監査

特定の監査に対して取得される監査。監査対象によって「文監査」「権限監査」「オブジェクト監査」に分けられる。
文監査:
特定のスキーマオブジェクトに対する監査ではなく、SQL のタイプ別による監査。文監査では、監査対象となるユーザーを指定できる。
権限監査:
ユーザーに付与されたシステム権限を行使する SQL を監査する。文監査と同様に、監査対象となるユーザーを指定できる。
オブジェクト監査:
特定のスキーマオブジェクトにおける特定の SQL に対する監査で、データベースのすべてのユーザーに常に適用される。

ファイングレイン(FGA)監査(EE版のみ)

標準監査よりも細かな監査、イベントハンドラにより独自ルールでの監査を実装する。イベントハンドラ機能により、指定した条件の監査がおこなわれた際に任意の処理を実行できるため、独自の監査証跡への監査情報の出力や、管理者への警告メールの送信機能を実装できる。
また FGA 監査では、特定の行や列に対する SELECT を監査し、監査レコードをFGA監査証跡(FGA_LOG$)に出力する。10g からは DML(UPDATE、DELETE、INSERT)の監査も可能になった。監査の対象列を指定して、指定された列に影響を与える SQL のみを監査することもできる。

DML イベントトリガー

DML によって影響受けた行単位でトリガーを発生させ、定義した PL/SQL 内で監査ログ取得を実装する。

LogMiner(ログマイナー)

データベースに対する変更履歴を SQL に変換し、事後監査を行う。


上記の監査の中でも、最も強力かつ実際に使いモノになる監査ログ機能は、まちがいなく FGA 監査だと感じています。Oracle を使う理由の1つに FGA 監査があるから。と考えている企業も少なくはないと思います。

ここからは失敗談。

冒頭で述べたとおり、FGA 監査ログ取得先を Oracle にしていると、当然ながら insert が走るので、その分だけ REDO ログとアーカイブログが生成され続けることを見落としていました。
ぶっちゃけ DB 設計時に FGA 監査ログ分のアーカイブログスペースを計算していなかったので、2ヶ月前にテーブルの再設計(正規化)に伴って FGA 監査ログが大量に出力され、パフォーマンス劣化、ディスク容量枯渇という不具合に悩まされました。FGA 監査ログが原因という意識が無かった分、追跡に時間がかかってしまったのですが、備忘録として事象と解決方法を記録しておきます。


まずはアラートログ監視ツールからのアラートで以下の事象に気がつく。

WL=2:アラートログメッセージが更新されました ORA-19502: ファイル"/db_arc2/oradb_2_3507_585074576.arc"の書込みエラーが発生しました。ブロック番号61441(ブロックサイズ=512) [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-27072: ファイルI/Oエラーが発生しました。 [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-19502: ファイル"/db_arc2/oradb_2_3507_585074576.arc"の書込みエラーが発生しました。ブロック番号61441(ブロックサイズ=512) [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-19502: ファイル"/db_arc1/oradb_2_3507_585074576.arc"の書込みエラーが発生しました。ブロック番号61441(ブロックサイズ=512) [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-27072: ファイルI/Oエラーが発生しました。 [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-19502: ファイル"/db_arc1/oradb_2_3507_585074576.arc"の書込みエラーが発生しました。ブロック番号61441(ブロックサイズ=512) [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-16038: ログ3、順序番号3507をアーカイブできません。 [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-19502: ファイル""の書込みエラーが発生しました。ブロック番号(ブロックサイズ=) [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-00312: オンライン・ログ3 スレッド2: '/db1/oradata/redo2/oradbredo1_1_2.dbf' [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-00312: オンライン・ログ3 スレッド2: '/db2/oradata/redo2/oradbredo1_2_2.dbf' [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-16038: ログ3、順序番号3507をアーカイブできません。 [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-19502: ファイル""の書込みエラーが発生しました。ブロック番号(ブロックサイズ=) [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-00312: オンライン・ログ3 スレッド2: '/db1/oradata/redo2/oradbredo1_1_2.dbf' [Mail Notified]
WL=2:アラートログメッセージが更新されました ORA-00312: オンライン・ログ3 スレッド2: '/db2/oradata/redo2/oradbredo1_2_2.dbf' [Mail Notified]


データベースに何が起きていたかを調査するツールとして 10g には AWR という機能があります。従来のパフォーマンス測定に使っていた statspack を進化させた機能です。
AWR を使うには Oracle Enterprise Manager 10g Diagnostics Pack というオプションライセンスを購入する必要がありますが、実は AWR に夜データ収集自体はライセンスとは関係なくデフォルトで粛々と裏で動作しています。

AWR の詳細はここらへんが詳しいです。


1. AWR からレポートを出力するには、DBA 権限のあるユーザで以下の SQL を実行します。

@ $ORACLE_HOME/rdbms/admin/awrrpt.sql

2. レポート形式を html or text かを選択します。text の方が何かと便利です。

SQL> @ $ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 4009598509 ORADB               1 oradb


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
report_typeに値を入力してください: text

Type Specified:  text

3. 次に調査する何日前のデータからを調査するか指定します。全データを調査したい場合はリターンだけでOK。

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 4009598509        1 ORADB        oradb        srv01

Using 4009598509 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


num_daysに値を入力してください: 7

4. AWR で取得したスナップショット一覧が出力され、レポート作成したい範囲の Snap Id をしていします。

Listing the last 7 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
oradb        ORADB              763 19 3月  2009 00:00     1
                                764 19 3月  2009 01:00     1
                                765 19 3月  2009 02:00     1
....
                                915 25 3月  2009 08:00     1
                                916 25 3月  2009 09:01     1
                                917 25 3月  2009 10:01     1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 763
Begin Snapshot Id specified: 763

end_snapに値を入力してください: 917
End   Snapshot Id specified: 917

5. レポートファイル名を指定します。デフォルトのままで良ければそのままリターンでOK。

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_763_917.txt.  To use this name,
press  to continue, otherwise enter an alternative.

report_nameに値を入力してください: 

Using the report name awrrpt_1_763_917.txt

これで sql を実行したディレクトリ直下に awrrpt_1_763_917.txt という AWR レポートが出力されます。このレポートを分析することで、様々な oracle 内部の事象を把握することが可能です。

今回もこの AWR レポートでこんなのを見つけました。短時間に insert into sys.fga_log が大量に実行されていることがわかりました。

SQL ordered by Executions              DB/Inst: ORADB/oradb  Snaps: 763-927
-> Total Executions:       9,210,392
-> Captured SQL account for     86.3% of Total

                                              CPU per    Elap per
 Executions   Rows Processed  Rows per Exec   Exec (s)   Exec (s)     SQL Id
------------ --------------- -------------- ---------- ----------- -------------
   2,900,630       2,900,630            1.0       0.00        0.00 g2d01djq6h1qr
insert into sys.fga_log$ (sessionid, ntimestamp#, dbuid, osuid, obj$schema, ob
j$name, policyname, scn, oshst, clientid, extid, lsqltext, proxy$sid,user$guid
, instance#, process#, xid, statement, entryid, stmt_type, lsqlbind, auditid)
values( :1, SYS_EXTRACT_UTC(SYSTIMESTAMP), :2, :3, :4, :5, :6, :7, :8, :9, :

念のために sys.fga_log$ テーブルの件数を取得してみる

SQL> select count(*) from sys.fga_log$;

  COUNT(*)
----------
   3191406


ここで原因は FGA 監査ログにあることまでがわかりました。次に FGA 監査ログとして何を取得しているかを調査します。もっとも自分が設計したものであれば把握済みのハズですけど。

SQL> select enable_flag, pname, o.name, o.owner#, username from sys.fga$ f, sys.obj$ o, dba_users u where f.obj#=o.obj# and o.owner#=u.user_id ;

ENABLE_FLAG PNAME                          NAME                               OWNER# USERNAME
----------- ------------------------------ ------------------------------ ---------- ------------------------------
          1 FGA_TBL1                       TBL1                                   33 ORADB
          1 FGA_TBL2                       TBL2                                   33 ORADB
          0 FGA_TBL10                      TBL10                                  33 ORADB


次に対処方法。目的は FGA 監査ログ取得におけるアーカイブログ出力で容量不足で Oracle 停止にならないようにすること。
対処ととしては、FGA 監査ログを無効化するか、ログの出力先を OS にするか、アーカイブログ出力先に割り当てているディスク領域を増やすか3つの方法から選択します。

1. FGA 監査ログを無効化する方法

EXECUTE DBMS_FGA.DISABLE_POLICY( object_schema => 'SCOTT', object_name => 'TBL1', policy_name => 'FGA_TBL1' );

2. ログの出力先を OS にする方法

まずは現在の設定値を把握する。

show parameters
....
audit_file_dest                      string      /db/u01/app/oracle/product/10.2.0/db/rdbms/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE
....

監査ログ出力のディレクトリの設定(デフォルトでは $ORACLE_HOME/rdbms/audit)

SQL>alter system set audit_file_dest='出力先ディレクトリ' scope=spfile;

AUDIT_TRAILパラメータの設定

alter system set audit_trail=os scope=spfile;

変更後に Oracle を再起動すれば FGA 監査ログの出力先が変更されています。


3. ディスク容量を増やす方法

→ディスクを増設して新しいパーティションとしてマウントしてアーカイブログ出力先をそこに変更する。LVM で動的にパーティション容量を増やす。等々・・・


・・・とまぁそんなかんじ。oracle まだまだ奥が深いです。

- スポンサーリンク -