最近、リリースしたばかりの環境で、AUDIT / NOAUDIT 実行時にORA-4021 / ORA-60 が発生するとの連絡がありました。
構成は以下の通りです。
![【オラクル認定資格試験対策書】ORACLE MASTER Bronze［Bronze DBA 12c］（試験番号：1Z0-065）完全詳解＋精選問題集 オラクルマスタースタディガイド [ エディフィストラーニング株式会社 ]](https://thumbnail.image.rakuten.co.jp/@0_mall/book/cabinet/5428/9784797375428.jpg?_ex=128x128 "【オラクル認定資格試験対策書】ORACLE MASTER Bronze［Bronze DBA 12c］（試験番号：1Z0-065）完全詳解＋精選問題集 オラクルマスタースタディガイド [ エディフィストラーニング株式会社 ]")
ASH(ActiveSessionHistory)を取得して確認したところ、NOAUDIT文と業務のSQL（select文）がデッドロックに陥っていました。
業務のSQLはrow chache lock待機、NOAUDIT文はlibrary cache lock待機で、それぞれ互いのblocking sessionとなっています。
ORA-60が発生した方はデッドロックを検出できたので20秒ほどでエラーになっていますが、ORA-4021の方は同じ事象なのに、15分かかってタイムアウトしていました。
実はこの環境、AUDIT / NOAUDIT文同士の同時実行で同じようにrow chache lock待機とlibrary cache lock待機でデッドロックに陥っており、その時のサポート問い合わせ結果が以下のようになります。
弊社のナレッジベースを調べたところ、以下のバグに該当する可能性が高いと考えております。
Bug 30468833 : AUDIT POLICY CAUSED RC DEADLOCK AND SUBSEQUENT HANG
バグ 30468833 は、本件と同じように「AUDIT POLICY」処理は、他の監査ポリシーテーブルへアクセスする処理をお互いブロックしたが、「deadlock」を検出できなかったという報告でありました。
上記のバグに対して、弊社の開発部門は調査した上、以下2点の結論になりました。
１、「AUDIT POLICY」処理は、「library cache lock」と「row cache lock」を「x-mode」で多く確保する必要がありますので、複数の「AUDIT POLICY」処理や、監査ポリシーテーブルへアクセスする処理と同時に実行すると、本件の様な「deadlock」は発生しえます。
２、「１」番目の「deadlock」が発生した時に、自動的に検知できなかった件について、以下のバグに該当します。
Bug 30489582 : ROW CACHE LOCK BLOCKER/DEADLOCK DETECTION DOES NOT WORK IN RAC
このシステムでは12cへのアップグレードと共に統合監査（UnifiedAuditing）による監査ポリシーを作成し、設定を行ったのですが、監査要件が非常に細かく多岐に渡っている為、17ものポリシーを作成する必要がありました。
その内、3つは特定時間のみ有効にする必要があったので、cronでAUDIT / NOAUDIT処理を入れていたのです。
AUDIT / NOAUDIT処理は5秒間隔で実行するようにし、同時実行を避けてエラー回避できていたのですが、本番運用が始まり、業務のSQLとの間でデッドロックが発生してしまったのでした。
業務のSQLは監査ポリシーテーブルへのアクセスは行っておらず、監査対象のテーブルをselectするのみですが、確認したところ、それでもこの不具合に該当するとの事です。
ちなみに、2つとも非公開の不具合なので、事前調査ではチェックできませんでした・・・
上記不具合の個別パッチは12.2用しか存在していないので適用できず、今はAUDIT / NOAUDIT処理を止め、監査通知処理の方で対処する方向で考えています。
統合監査には別の問題もあるのですが、それは又、別の機会に。
