ORA-1555,ORA-01555の原因と対処

対応バージョン

Oracle Database 11.2 – 12.1

更新履歴

日付	更新概要
2016/06/16	記事公開

ORA-1555は主に長時間のSELECTをした時に発生するエラーです。例えばINSERT-SELECTによってテーブルのコピーを行っている場合などの長時間SQLでよく発生します。このようなケースはよくあるため、頻繁に発生する障害の一つです。

現在11g以降のほとんどのデータベースは自動UNDO管理モード(Oracle推奨)となっているため、自動UNDO管理モードを前提として説明します。
パフォーマンスおよび管理性の観点からも自動UNDO管理モードを選択することが推奨されます。手動UNDO管理モードは下位互換性のために残っています。

参考情報（参考元マニュアル）
https://docs.oracle.com/cd/E57425_01/121/TGDBA/pfgrf_build_db.htm#GUID-8314AD37-F37C-4A61-B909-4853D6FC9747
Oracle Databaseパフォーマンス・チューニング・ガイド
12c リリース1 (12.1)
B71276-01
4 パフォーマンスを考慮したデータベースの構成
4.1 初期インスタンス構成のパフォーマンスの考慮事項
4.1.2 UNDO領域の構成

自動UNDO管理モードの確認は以下のコマンドで行えます。AUTO（又は11g以上のデータベースではNULL）が設定されている場合は、自動UNDO管理モードで動作しています。

SQL> show parameter UNDO_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO

エラーメッセージ

ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号string、名前”string”が小さすぎます
ORA-01555: snapshot too old: rollback segment number %s with name “%s” too small

https://docs.oracle.com/cd/E57425_01/121/ERRMG/ORA-01500.htm#GUID-65B2B9E5-7075-4D53-91B8-FCAECA0AEE0E
Oracle Databaseエラー・メッセージ
12c リリース1 (12.1)
B71353-01
60 ORA-01500からORA-02098

発生原因

SQLでSELECT文を実行している時もデータベースは常に更新されます。データに不整合が出ないように、SELECT文を開始した時点の、更新される前の過去のデータを取得することがあります。その時にUNDO表領域の過去データ（ロールバック・レコード）を読み込みます。
ロールバック・レコードを読み取ろうとした時に、既に新しいデータによって上書き済みとなっていた場合にORA-1555エラーが発生します。

発生要因例

最初に説明した部分とかぶりますが、例えばINSERT-SELECTによってテーブルのコピーを行っている場合などの長時間SQLでよく発生します。

通常運用時では以下のようなパターンが想像されます。

例外的なメンテナンス処理を行った（テーブルのバックアップなど）
処理の変更を加えた
実行計画が変化した
データサイズが肥大化し、ロールバック・セグメントを多く使うようになった

対処方法

ORA-1555回避のアプローチ

ORA-1555回避のアプローチとしては、大まかに2種類あります。1つは出来るだけ過去のデータを見ないようにSQLの時間を短くします。もう1つはUNDO表領域の容量(過去データ保存量)を増やすことで、データの一貫性を保てる時間を延ばします。

データの肥大化などにより起こるべくして起きた内容であれば、UNDO表領域を大きくするような対処になるかと思われますが、SQLの高速化が可能であればそちらの対処でも可能です。費用対効果や実現性を見て対応をして下さい。

障害発生の時間と対象SQLの確認

エラーが発生した場合、アラートログ(alert_<インスタンス名>.log)および実行したクライアント側にORA-1555が出力されます。
アラートログにはORA-1555の記載された行の前後にトレースログの場所が記載されています。
最後に.trcが付与されたファイルです。（例：/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8774.trc）
こちらの.trcファイルを上から見ていくと、エラーになったSQLが見つかると思います。そこで発生した時間なども確認できますので、解析用に使用することができます。

対処例

対処方法1：SQLの時間を短縮する

エラーの発生したSQLを特定し、SQLのチューニングなどによりSQLの実行時間を短くすることでエラーを回避します。統計情報を取得してみたら治ったなどもたまに聞く話です。

対処方法2：表領域を大きくする

UNDO表領域又はUNDO保存期間を大きくすることで、保存期間を増やして長時間SQLでエラーが出ないように対応します。

undo_retention（保存期間）の増加にはSQL*Plusにログイン後、以下のコマンドを実行します。（SPFILEを想定）

$ sqlplus / as sysdba
SQL> -- 以下 SPFILE のパスが出力される場合は SPFILE を使用しています。
SQL> show parameter spfile
SQL> set line 120 pages 1000 trim on
SQL> col name for a15
SQL> col value for a20
SQL> col sid for a10
SQL>
SQL> -- 現在値の確認
SQL> select INST_ID, NAME, VALUE from gv$parameter where name in ('undo_retention');

   INST_ID NAME            VALUE
---------- --------------- --------------------
         1 undo_retention  900

SQL> -- SPFILE の内容の確認
SQL> select SID, NAME, VALUE from v$spparameter where name in ('undo_retention');

SID        NAME            VALUE
---------- --------------- --------------------
*          undo_retention

SQL> -- 値の変更
SQL> ALTER SYSTEM SET UNDO_RETENTION = <変更後の値> SCOPE=BOTH;

System altered.

SQL> -- 現在値が変更されていることの確認
SQL> select INST_ID, NAME, VALUE from gv$parameter where name in ('undo_retention');

   INST_ID NAME            VALUE
---------- --------------- --------------------
         1 undo_retention  <変更後の値>

SQL> -- SPFILE の内容が更新されていることの確認（再起動時に同じ値が使用されることを確認）
SQL> select SID, NAME, VALUE from v$spparameter where name in ('undo_retention');

   INST_ID NAME            VALUE
---------- --------------- --------------------
         1 undo_retention  <変更後の値>

PFILEを使用した変更については、以下を参照してください。
SPFILEとPFILEの概要と操作

表領域のサイズ増加にはSQL*Plusにログイン後、以下のコマンドを実行します。

SQL> col FILE_NAME for a70
SQL> -- ファイル名を確認
SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 as MBYTES,AUTOEXTENSIBLE
  2  from DBA_DATA_FILES;
SQL> ALTER DATABASE DATAFILE '<ファイル名>' RESIZE 300M;
SQL> ALTER DATABASE DATAFILE '<ファイル名>' AUTOEXTEND ON NEXT 16M MAXSIZE 600M;

値の検討（アドバイザの利用）

UNDOアドバイザによる見積もり

サイズの見積もりについてどの程度大きくすればよいかは、該当時間帯のAWRやOEM(Oracle Enterprise Manager)からアドバイザで確認することもできます。OracleとしてはOEMのUNDOアドバイザを推奨しています。(Oracle Database 12c付属のOracle Enterprise Manager Database ExpressでもUNDOアドバイザは確認可能です。OEMにてデータベースログイン後、UNDO管理詳細を参照してください。また、Diagnostic Packライセンスは不要であり、Standard Editionでも確認可能です。)

また、使用実績をベースに見積もるため、一通りの処理が流れたデータベースでないと、正確な見積もりが行われないため注意してください

OEMを利用したアドバイザの参照
https://docs.oracle.com/cd/E57425_01/121/ADMQS/GUID-5801E369-B27D-4DF0-B291-68C37D641C43.htm
Oracle Database 2日でデータベース管理者
12cリリース1 (12.1)
B71307-07
UNDOアドバイザを使用したUNDO表領域の最小サイズの計算

UNDOアドバイザによる見積もり(PL/SQLの使用)

見積もりに使用するスナップショットの期間を取得します。

SQL> set pagesize 1000 linesize 120 trim on
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
SQL> select SNAP_ID
  2       , INSTANCE_NUMBER
  3       , to_char(END_INTERVAL_TIME, 'yyyy/mm/dd hh24:mi:ss') as END_INTERVAL_TIME
  4       , to_char(STARTUP_TIME, 'yyyy/mm/dd hh24:mi:ss') as STARTUP_TIME
  5    from DBA_HIST_SNAPSHOT
  6   -- １日前からのスナップショット番号を取得
  7   where END_INTERVAL_TIME >= sysdate - 1
  8   order by snap_id;

分析したい期間のスナップショット番号を指定し、アドバイザによる分析を行います。

SQL> DECLARE
  2     tid    NUMBER;
  3     tname  VARCHAR2(30);
  4     oid    NUMBER;
  5  BEGIN
  6     -- タスクの作成を行います tname にはタスク名が入ります。
  7     DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
  8     -- タスクオブジェクト（アドバイザ入力データ）を作成します。 oid にはタスクオブジェクト ID が入ります。
  9     DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
 10     -- アドバイザの入力内容をセットします。 START_SNAPSHOT END_SNAPSHOT INSTANCE は適宜変更します。
 11     DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
 12     DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 183);
 13     DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 187);
 14     DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
 15     -- アドバイザの実行をします。
 16     DBMS_ADVISOR.execute_task(tname);
 17  END;
 18  /

PL/SQL procedure successfully completed.

アドバイザの分析結果を確認します。

SQL> column OWNER format a10
SQL> -- TASK_NAME が select された際に、 tname 変数として定義します。
SQL> column TASK_NAME format a15 new_value tname
SQL> select OWNER
  2       , TASK_ID
  3       , TASK_NAME
  4       , STATUS
  5       , CREATED
  6    from DBA_ADVISOR_TASKS
  7   where ADVISOR_NAME = 'Undo Advisor'
  8   order by CREATED;
SQL> 
SQL> -- 推奨事項の出力
SQL> column FINDING_NAME format a50
SQL> column MESSAGE format a50
SQL> select TASK_NAME
  2       , FINDING_NAME
  3       , MESSAGE
  4    from DBA_ADVISOR_FINDINGS
  5   where TASK_NAME = '&&tname';

TASK_NAME       FINDING_NAME                                       MESSAGE
--------------- -------------------------------------------------- --------------------------------------------------
TASK_nnn        normal, successful completion                      The undo tablespace is OK.

1 row selected.

SQL> 
SQL> -- 推奨事項の対応状況
SQL> select TASK_NAME
  2       , TYPE
  3       , RANK
  4       , PARENT_REC_IDS
  5       , BENEFIT_TYPE
  6       , ANNOTATION_STATUS
  7    from DBA_ADVISOR_RECOMMENDATIONS
  8   where TASK_NAME = '&&tname'
  9   order by RANK;

DBA_ADVISORから始まる表のADDMにかかわるものはStandard Edition では表示させることが出来ませんので注意してください。

参考情報（参考元マニュアル）
https://docs.oracle.com/cd/E57425_01/121/DBLIC/editions.htm#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87
Oracle Databaseライセンス情報
12cリリース1 (12.1)
B71334-02
1 ライセンス情報
1.2 Oracle Databaseのエディションおよび許可される機能
1.2.2 Oracle Databaseのエディションで許可される機能、オプションおよびManagement Pack

値の検討（SQLでの確認）

やはりSQL文で直接確認するほうが理解は深まるため、ここではSQL文での紹介を行います。
アドバイザで行う正確な値の出し方は一般公開されていない為、どんな内容から見積もるかというところまでの記載です。
UNDOの調整に必要な要素の理解として記載します。

UNDOの保存期間（UNDO_RETENTION）

保存期間の自動調整

UNDO表領域の調整にはV$UNDOSTAT又はDBA_HIST_UNDOSTATというビューを使用します。
V$UNDOSTATには10分間隔で過去4日間分のUNDO関連の統計情報が格納されています。ただし、インスタンスを再起動している場合は初期化されていますのでご注意ください。過去履歴はDBA_HIST_UNDOSTATに格納されています。

UNDOの保存期間は以下の3つを考慮します。

TUNED_UNDORETENTION（Oracleの内部調整）
UNDO_RETENTION初期化パラメータ（保証期間の指定（秒））
UNDO表領域へのRETENTION GUARANTEE付与（UNDO_RETENTION期間の保証）

自動拡張不可（AUTOEXTEND OFF）の設定をしている場合、UNDO表領域のサイズベースでUNDOの保存期間を調整します。
自動拡張可能（AUTOEXTEND ON）の設定をしている場合、SELECT文の実行時間の長さベースでUNDOの保存期間を調整します。これはUNDO表領域のMAXSIZEと現在のサイズが同じでも同一の動きとなります。
調整された結果はTUNED_UNDORETENTIONに反映されます。値はV$UNDOSTATで確認可能です。

$ sqlplus / as sysdba
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SQL> set line 120 pages 1000 trim on
SQL> -- BEGIN_TIME から END_TIME の期間のチューニング結果
SQL> select BEGIN_TIME,END_TIME,INSTANCE_NUMBER,TUNED_UNDORETENTION
  2    from DBA_HIST_UNDOSTAT
  3   order by BEGIN_TIME,INSTANCE_NUMBER;

BEGIN_TIME          END_TIME            INSTANCE_NUMBER TUNED_UNDORETENTION
------------------- ------------------- --------------- -------------------
2016/06/10 13:22:00 2016/06/10 13:32:00               1                1162
2016/06/10 13:32:00 2016/06/10 13:42:00               1                1720
2016/06/10 13:42:00 2016/06/10 13:52:00               1                2321
2016/06/10 13:52:00 2016/06/10 14:02:00               1                2667
2016/06/10 14:02:00 2016/06/10 14:12:00               1                3268
2016/06/10 14:12:00 2016/06/10 14:22:00               1                2119
2016/06/10 14:22:00 2016/06/10 14:32:00               1                2397
2016/06/10 14:32:00 2016/06/10 14:42:00               1                2545
....

但しLOBオブジェクトはUNDO_RETENTIONの値が使用され、自動チューニングが反映されません。

筆者のお勧めとしては自動拡張可能（AUTOEXTEND ON）とすることです。理由はMAXSIZEを大きめにして自動拡張としておくことで、UNDO表領域が不足した場合に、拡張結果でUNDOの不足容量が確認でき、ORA-01555が出にくくなるためです。

UNDO_RETENTION

自動拡張不可（AUTOEXTEND OFF）の場合、UNDO_RETENTION初期化パラメータはLOB以外無視されます。
自動拡張可能（AUTOEXTEND ON）の場合は、UNDO_RETENTIONは最低維持目標値となります。表領域のMAXSIZEを超えた場合はUNDO_RETENTIONを無視してロールバック・レコードが上書きされます。基本的に保証期間がSELECT文の実行時間の最大の長さに調整されるため、特に自動チューニング期間よりも前を指定するようなFLASHBACKクエリ、または想定外の長時間クエリが発生するような場合、UNDO_RETENTIONの値が役に立ちます。

保証期間の長さについては、DBA_HIST_UNDOSTATのMAXQUERYLENを参考にすることができます。MAXQUERYLENはBEGIN_TIMEからEND_TIMEの期間の中で最長のSELECT文の実行時間の長さを確認できます。

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SQL> set line 120 pages 1000 trim on
SQL> -- BEGIN_TIME から END_TIME の期間の最大 SELECT 文実行時間と対象の SQLID
SQL> select BEGIN_TIME,END_TIME,INSTANCE_NUMBER,MAXQUERYLEN,MAXQUERYSQLID
  2    from DBA_HIST_UNDOSTAT
  3   order by BEGIN_TIME,INSTANCE_NUMBER;

BEGIN_TIME          END_TIME            INSTANCE_NUMBER MAXQUERYLEN MAXQUERYSQLID
------------------- ------------------- --------------- ----------- -------------
2016/06/10 13:22:00 2016/06/10 13:32:00               1         322 0rc4km05kgzb9
2016/06/10 13:32:00 2016/06/10 13:42:00               1         880 cc58t2hkfpnaq
2016/06/10 13:42:00 2016/06/10 13:52:00               1        1480 cc58t2hkfpnaq
2016/06/10 13:52:00 2016/06/10 14:02:00               1        1827 cc58t2hkfpnaq
2016/06/10 14:02:00 2016/06/10 14:12:00               1        2428 cc58t2hkfpnaq
2016/06/10 14:12:00 2016/06/10 14:22:00               1        1279 8cddpr5wp7at1
2016/06/10 14:22:00 2016/06/10 14:32:00               1        1556 f3yfg50ga0r8n
2016/06/10 14:32:00 2016/06/10 14:42:00               1        1705 8cddpr5wp7at1
....

RETENTION GUARANTEE付与

UNDO表領域へのRETENTION GUARANTEE付与についてですが、こちらは自動拡張の状態にかかわらず、UNDO_RETENTIONの期間のデータが保証されるようになります。
もしUNDO表領域のサイズが足りなくなった場合、長時間SQLだけではなく、複数のDMLの失敗（保証によりロールバック・レコードの書き込みの失敗）が発生することとなるため、安易な設定は避けたほうが無難です。

UNDO表領域のサイズ

UNDO表領域のサイズについては、DBA_HIST_UNDOSTATのUNDOBLKSを参考にすることができます。例えばUNDOBLKSが（10分間のうち）最大となっているブロック生成数を基準に、SELECT文実行時間またはUNDO_RETENTIONの時間の分だけUNDOブロックが生成されたときのUNDOサイズにするなどとします。UNDOBLKSはBEGIN_TIMEからEND_TIMEの期間の中で生成されたブロック数を確認できます。

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SQL> set line 120 pages 1000 trim on
SQL> -- BEGIN_TIME から END_TIME の期間の UNDO ブロック生成数と最大 SELECT 文実行時間
SQL> select BEGIN_TIME,END_TIME,INSTANCE_NUMBER,UNDOBLKS,MAXQUERYLEN
  2    from DBA_HIST_UNDOSTAT
  3   order by BEGIN_TIME,INSTANCE_NUMBER;

BEGIN_TIME          END_TIME            INSTANCE_NUMBER   UNDOBLKS MAXQUERYLEN
------------------- ------------------- --------------- ---------- -----------
2016/06/10 13:22:00 2016/06/10 13:32:00               1       1105         322
2016/06/10 13:32:00 2016/06/10 13:42:00               1       1003         880
2016/06/10 13:42:00 2016/06/10 13:52:00               1       5370        1480
2016/06/10 13:52:00 2016/06/10 14:02:00               1       2116        1827
2016/06/10 14:02:00 2016/06/10 14:12:00               1        847        2428
2016/06/10 14:12:00 2016/06/10 14:22:00               1        107        1279
2016/06/10 14:22:00 2016/06/10 14:32:00               1        134        1556
2016/06/10 14:32:00 2016/06/10 14:42:00               1        141        1705
....

トランザクションなどが多い場合は、UNDO領域用のメタデータなどにオーバーヘッドがかかるため、その分も容量確保する必要があります。ただし、必要な見積もり値はUNDOアドバイザを参照することが最適値に近くなると思われます。

2016-06-27

Oracle Database

UNDO概要

対応バージョン Oracle Database 10.1 – 12.1 更新履歴日付更…

記事詳細

2016-06-25

Oracle Database

REDOログとUNDOレコードの違いについて

対応バージョン Oracle Database 10.1 – 12.1 更新履歴日付更…

記事詳細

2015-02-15

ORA-1000,ORA-01000の原因と対処

対応バージョン対応バージョン: Oracle Database 11.2 – 12.1…

記事詳細

ORA-1555,ORA-01555の原因と対処

対応バージョン

更新履歴

エラーメッセージ

発生原因

発生要因例

対処方法

ORA-1555回避のアプローチ

障害発生の時間と対象SQLの確認

対処例

対処方法1：SQLの時間を短縮する

対処方法2：表領域を大きくする

値の検討（アドバイザの利用）

UNDOアドバイザによる見積もり

UNDOアドバイザによる見積もり(PL/SQLの使用)

値の検討（SQLでの確認）

UNDOの保存期間（UNDO_RETENTION）

UNDO表領域のサイズ

関連記事

UNDO概要

REDOログとUNDOレコードの違いについて

ORA-1000,ORA-01000の原因と対処

お問い合わせ