Oracleエラー対応(ORA-01555):ORA-01555発生時の対処方法について

現役DBAが解説-Oracleエラー対応
UNDO表領域関係の大きさが不足している際に、ORA-01555が発生することがあります。
本記事では原因および対処法をご紹介します。
1. エラーメッセージ
ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号16、名前"_SYSSMU16_295376492$"が小さすぎます
2. 原因
ORA-01555は、UNDO表領域が必要としていたデータがその後の処理により上書きされてなくなった際に発生します。
<原因例>
・UNDO 表領域を超える量の更新が行われた
・SQLの実行時間が長くなった
3. エラー調査と対応
以下コマンドにて、UNDO表領域の大きさと、AUTOEXTENDのステータス、AUTOEXTENDが有効である場合の拡張最大時の大きさを確認します。
SQL>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where file_name like '%undo%'; FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024 ---------------------------------------------------------- ------------------------ ------ ----------------------------- /app/oracle/oradata/ORCL/undotbs1.dbf 200 YES 200
3-1 UNDO_RETENTIONの値を超えるSQLの処理時間によるエラー発生
AUTOEXTENDが有効な際は、TUNED_UNDORETENTIONが有効になります。
そのことにより、TUNED_UNDORETENTIONを過ぎてしまったUNDOデータが上書きされてしまう場合があります。
以下コマンドにて、TUNED_UNDORETENTIONの下限値である初期化パラメータのUNDO_RETENTION、およびALERTLOGに記載されているORA-01555のQuery Durationの値を確認します。
SQL> show parameter undo_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900
CMD>view /app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log ※該当エラー箇所を抜粋 >ORA-01555 caused by SQL statement below (SQL ID: 9mpyc83bm12y0, Query Duration=1 sec, SCN: 0x00000000001c624c):
UNDO_RETENTIONの値が900(秒)であり、Query DURATIONの値が1secであったため、今回はUNDO_RETENTIONの設定に問題が無いことが分かりました。
3-2 UNDO表領域の容量を超過したことによるエラー発生
上記に当てはまらない場合は、この条件である可能性があります。
UNDO表領域に追加の空き容量がない場合は古いUNDOデータから上書きされていきます。
以下コマンドにてUNDO表領域で利用した最大サイズを確認することができます。
SQL>select tablespace_name, sum(bytes)/1024/1024 from dba_segments where tablespace_name like '%UNDO%' group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024 ------------------------------ ------------------------------ UNDOTBS1 199.1875
初めに確認したSQLからUNDO表領域の容量が最大まで拡張されており、今回確認した表領域を利用した最大サイズがほぼ同じ値になっていることから、今回のORA-01555は古いUNDOデータが上書きされたことによるエラーだと分かりました。
以下コマンドにてUNDO表領域の現在の容量と拡張した際の最大容量を修正します。
SQL>alter database datafile '/app/oracle/oradata/ORCL/undotbs1.dbf' autoextend on maxsize 400M; データベースが変更されました。 SQL> alter database datafile '/app/oracle/oradata/ORCL/undotbs1.dbf' resize 400M; データベースが変更されました。
4. 最後に
本エラーはUNDO_RETENTIONの値かUNDO表領域を増やすことで解消できますが、これらの対処が難しい場合は、SQLのチューニングやSQLの実行時間を分散するなど、クライアント側の操作による対処を検討してください。
また、UNDO表領域の適切な容量については、Enterprise ManagerのUNDOアドバイザなどをご利用いただき、見積もりを行ってください。