Oracle では FGA 監査を利用している場合は select 文でも REDO ログは生成される
いきなり回答。監査を設定している場合には、当然の事ながら監査ログが出力されます。出力先を OS ではなく Oracle にしている場合、select 文を流すだけでも結果的に REDO ログ（監査ログの insert 分について）、アーカイブログが生成されます。
これ見落としがちですが、かなり注意が必要です。
以下詳細です。結論からすると、上記の前提を単に見落としていただけなのだが、これには正直参った。
Oracle を使う理由のひとつとして監査ログ機能の充実があります。Oracle 9i 以降を業務でお使いの方は、機微な情報を持つテーブルに対しては何かしらの監査を行っていることと思います。
10g では監査の種類として以下のようなものが用意されています。
※参考ページ
- Oracle Japan / オラクル通信 / Oracle Database 10g 徹底検証レポート
- データベース・アクティビティの監査
- 日本版SOX法時代のOracleセキュリティ（後編） 2/3 − ＠IT
- メールマガジン - バックナンバー : Audit Trail についての検証1
必須監査
DBA監査
標準監査
文監査：
特定のスキーマオブジェクトに対する監査ではなく、SQL のタイプ別による監査。文監査では、監査対象となるユーザーを指定できる。
権限監査：
ユーザーに付与されたシステム権限を行使する SQL を監査する。文監査と同様に、監査対象となるユーザーを指定できる。
オブジェクト監査：
特定のスキーマオブジェクトにおける特定の SQL に対する監査で、データベースのすべてのユーザーに常に適用される。
ファイングレイン（FGA）監査（EE版のみ）
また FGA 監査では、特定の行や列に対する SELECT を監査し、監査レコードをFGA監査証跡（FGA_LOG$）に出力する。10g からは DML（UPDATE、DELETE、INSERT）の監査も可能になった。監査の対象列を指定して、指定された列に影響を与える SQL のみを監査することもできる。
DML イベントトリガー
LogMiner（ログマイナー）
上記の監査の中でも、最も強力かつ実際に使いモノになる監査ログ機能は、まちがいなく FGA 監査だと感じています。Oracle を使う理由の１つに FGA 監査があるから。と考えている企業も少なくはないと思います。
ここからは失敗談。
冒頭で述べたとおり、FGA 監査ログ取得先を Oracle にしていると、当然ながら insert が走るので、その分だけ REDO ログとアーカイブログが生成され続けることを見落としていました。
ぶっちゃけ DB 設計時に FGA 監査ログ分のアーカイブログスペースを計算していなかったので、２ヶ月前にテーブルの再設計（正規化）に伴って 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 の詳細はここらへんが詳しいです。
- OTN Japan - Oracleデータベース 性能対策機能 〜 StatspackとDiagnostics Packを使いこなす 〜 第1回 StatspackとDiagnostics Packの概要と使用方法
Oracle Database パフォーマンス・チューニング・ガイド - 自動パフォーマンス統計
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. 次に調査する何日前のデータからを調査するか指定します。全データを調査したい場合はリターンだけでＯＫ。
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. Pressingwithout 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. レポートファイル名を指定します。デフォルトのままで良ければそのままリターンでＯＫ。
Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_763_917.txt. To use this name, pressto 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 にするか、アーカイブログ出力先に割り当てているディスク領域を増やすか３つの方法から選択します。
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 まだまだ奥が深いです。
コメントやシェアをお願いします！