トランケートとORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [SQL・DDL]

台風19号が過ぎ去り被害の報道にこの台風の爪痕の大きさに驚くとともに被災された方々には1日も早い生活の復旧を願うばかりである。

ORA-54は一般的にロック中のテーブル(レコード)に対し、SELECT ... FOR UPDATE NOWAIT等でロックを獲得しようとした場合に発生するエラーである。ロック中のオブジェクトに対しロックをかけようとしているため、このオブジェクトへのロック解除後に再実行すれば解消される。この際、NOWAITではなく、SELECT ... FOR UPDATE WAITでタイムアウトを設定すると、指定された秒数までロック獲得を試み、失敗した場合にエラーとなる(この場合はORA-30006)。ロックとの競合で即時エラーを回避したい場合に有効である。

このORA-54、DML実行中のテーブルにトランケートを実行しても発生する。これはトランケートは該当オブジェクト(セグメント)に対しTMエンキューを排他モードで獲得するためである。alter table ... truncate patitionで複数のパーティションにトランケートを順に実行するとき、たまにORA-54が返って失敗するのは、多くの場合DML(オンライン処理などの業務アプリケーション)との競合が発生し、トランケートがロック獲得に失敗したためである。なお、SELECT実行中のテーブルにトランケートを実行した場合、トランケートでORA-54は発生せず処理されてしまう。SELECTは(Oracleの場合読み取り一貫性のメカニズムにより)ロックを獲得しないためである。トランケートの結果、data_object_idが変わってしまうため、SELECT側にORA-8103: object no longer existsが返る。

トランケートのORA-54を回避するには、当たり前であるがDML実行中にトランケートを実行しないことである。そもそもDML実行中にトランケートを実行しなければならないこと自体に疑問はありつつも、現実問題として、トランケート実行タイミングに影響ある業務APを完全に制御し切れないケースもあるだろう。このような場合に有効なのが、11gから導入されたDDL_LOCK_TIMEOUTパラメータである。例えばこれを以下のようにセッションレベルで設定すると、指定された秒数までDDLのロック獲得を試みることができる。多少のDMLとの競合であれば、DML実行の間にロック獲得を試みて、トランケートの成功確率を高めてくれるので便利である。なお、このデフォルトは0(秒)なので、ロック獲得に失敗したら即ORA-54が返却される。

 alter session set ddl_lock_timeout=60;

TMエンキューに限って言えば、トランケートはセグメントレベルでロックを獲得する。つまり、パーティション表において、ORA-54が発生するのは、あくまで別トランザクションがそのタイミングで該当パーティションにDML操作を行っていた場合に限られる。したがって、例えば日付レンジパーティション表で、使われなくなった過去パーティションをトランケートする場合には、ORA-54の発生確率は低いだろう。逆に、ハッシュパーティションやリストパーティションで、どのパーティションも頻繁にDML操作される状況においては、いくつかのパーティションでORA-54が発生しトランケートが失敗したりする。このような場合に、上記DDL_LOCK_TIMEOUTは非常に有効である。

ただし、パーティションの競合が無いからといってオン中のトランケートを実行しても大丈夫という訳ではない。ORA-54は避けられても、トランケートにより当該テーブルにアクセスするSQLのカーソルのinvalidation、およびそれに伴うハードパース、それがトランケートによるテーブルレベルのlibrary cache lockと競合することは避けられないためである。このあたりの挙動の詳細は記事「トランケートについて知っておくべきこと」を参照されたい。

なお、DDL_LOCK_TIMEOUTはトランケートに限らず、ALTER TABLE・INDEX等のパーティションメンテナンスのDDLにも有効である。例えば、ローカルインデックスリビルド(alter index ... rebuild partition ...)、未使用エクステントの開放(alter table ... modify partition ... deallocate unused)など、パーティションメンテナンスの実行時にORA-54が発生した場合、回避策の1つとして考慮するとよいだろう。

以上
nice!(0)  コメント(0) 

UTL_FILEがNLS_LANGで結果不正 [アーキテクチャ]

先日、業務チームからUTL_FILEで出力したファイルがおかしいという問い合わせがあった。調べてみると、最後の文字がなくなったり、不要な改行が追加されている部分があり、それも文字の種類によって規則性がある、というものであった。環境はOracle19c(Exadata)、データベースのキャラクタセットはJA16SJISである。

結論から言うと、この事象の原因はDBインスタンスを構成する各種プロセスのNLS_LANG環境変数がデフォルトのAMERICAN_AMERICA.US7ASCIIとなっていたことであった。DBサーバのoracleやgridユーザのNLS_LANGをSJISに設定し忘れた訳ではない。以下のように、Grid Infrastructure(GI)にNLS_LANGを設定することにより、当該事象は回避できた。この設定はOCRに書き込まれるので永続的、ただし反映にはインスタンスの再起動が必要である(マルチテナントの場合はPDBではなくコンテナの再起動が必要)。

srvctl setenv database -d dbname -t "NLS_LANG=Japanese_Japan.JA16SJIS"

さて、以下この事象についてもう少し詳しく記載する。UTL_FILEのマニュアルの以下の記述を読んでほしい(特に最後の一文)。

---
PL/SQL Packages and Types Reference, 255.2 UTL_FILE Operational Notes
UTL_FILE expects that files opened by UTL_FILE.FOPEN in text mode are encoded in the database character set. It expects that files opened by UTL_FILE.FOPEN_NCHAR in text mode are encoded in the UTF8 character set. If an opened file is not encoded in the expected character set, the result of an attempt to read the file is indeterminate. When data encoded in one character set is read and Globalization Support is told (such as by means of NLS_LANG) that it is encoded in another character set, the result is indeterminate. If NLS_LANG is set, it should therefore be the same as the database character set.
---

UTL_FILEを使う場合は、NLS_LANGはデータベースのキャラクタセットと一致させる必要がある。そうでない場合の結果は不定(indeterminate)ということである。つまりこれはUTL_FILEの仕様、ということになる。

問題は、ここでいうNLS_LANGとは何か、ということである。例えばsqlplusのクライアントがリスナー経由でDBサーバへ接続し、PL/SQLの無名ブロックでUTL_FILE.PUT_LINE等でファイル出力するケースを考えよう。この場合、実体としてはDBサーバ上のサーバプロセスがファイル出力するため、このサーバプロセスのNLS_LANGを指すと思われる。では、このNLS_LANGはどこから来るのだろうか。普通に考えるとクライアントからの接続要求はリスナーを経由し、リスナーがこのサーバプロセスを生成することから、このリスナーのNLS_LANGが引き継がれると考えられる。リスナーを起動するOSユーザがoracleユーザであれば、その環境変数に指定されたNLS_LANGであると考えられる。11gR2まではGIが無かったので、多くの場合はOSのoracleユーザのNLS_LANGにより、Oracleのプロセス群に設定されるNLS_LANGが決定されていたはずである。そのため、UTL_FILEの上記仕様の制限を意識する必要性はあまりなかったと思われる。

しかし、11gR2以降では話は異なる。多くの場合、DBインスタンスやリスナー等のDBリソースはGIにより管理される。DBインスタンスはsrvctlにより起動停止を制御し、リスナーはGIにより自動起動される。この場合注意が必要なのは、これらのプロセスは、oracleやgridユーザのNLS_LANGの設定如何にかかわらず、デフォルトではAMERICAN_AMERICA.US7ASCIIが設定されるということである。例えoracleユーザやgridユーザのNLS_LANGにJapanese_Japan.JA16SJISしても、DBインスタンスを構成するプロセス、リスナーはNLS_LANG=AMERICAN_AMERICA.US7ASCIIになる、ということである。この状態でUTL_FILEを使うと、上記制約に引っかかる、という訳である。

この事象の回避方法は、上記で述べたとおりsrvctl setenvでDB毎にNLS_LANGを設定するしかないだろう。DBインスタンスの再起動を伴うためそれなりにインパクトがあるが、受け入れるしかない。残念ながら、試した限りではsrvctl setenv listenerだけではこの事象は回避できない(もしかすると単純にリスナープロセスのNLS_LANGだけが原因、ということではないのかもしれない)。上記設定によりDBサーバのプロセス郡のNLS_LANGが変更されることに不安を覚えるかもしれないが、クライアント側に返却される結果についてはクライアント側のNLS_LANGに依存するため、DBサーバのNLS_LANGの影響を受けない。また、リスナー経由しないDB接続(DBサーバでoracleユーザから直接sqlplus / as sysdbaで接続するなど)は、サーバプロセスもoracleユーザで起動されるため、結果そのユーザのNLS_LANGが引き継がれるため影響を受けない。とはいえ、運用中のシステムに対しこれを変更するのは勇気がいることには変わりない。

なお、DBインスタンスを構成する各種バックグラウンドプロセスが出力するalert.logやトレースファイル、これらの文字コードや言語はNLS_LANGと一切関係なく、データベースキャラクタセットと初期化パラメータNLS_LANGUAGEに依存する。そう考えると、そもそもUTL_FILEが何故DBインスタンスを構成するプロセスのNLS_LANGに依存した作りになっているのか理解に苦しむ。単純にデータベースキャラクタセットと同じ文字コードでファイル出力する、という仕様にすれば良いだけの話である。特にエラーが出る訳でもなく、文字コードの組み合わせによっては事象が顕在化しない場合もあるため、顕在化した際の業務的なインパクトが計り知れない。せめて、設定の矛盾をalert.logに出力するなど、気付くきっかけくらいは考えてくれてもよいのではないか。そう考えると、このUTL_FILEの仕様は極めて不親切であり、どちらかというと、ワークアラウンドが確立されたため修正される見込みのない不具合ではないか、という気すらする。

今回の件を通し、先のUTL_FILEの仕様を読み、それを咀嚼してOracleの環境設計に取り込めるOracle技術者はどれほど存在するだろうか、と考えさせられた。このUTL_FILEの仕様は古く9i時代にさかのぼるらしいが、11gR2からのGIの登場によりより問題として顕在化し易くなったのではないだろうか。世の中的にはあるあるネタで知っている人は多いかもしれないが、少し不安に思った方は自分の身を守るために今一度ご自身の環境をチェックされては如何だろうか。

以上
nice!(0)  コメント(0)