ORA-01555 発生時の対処方法

*以下は、サポート契約締結中のお客様に2015年7月号として配信しているサポートレターより一部抜粋して掲載しています。

ORA-01555 発生時の対処方法

ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号 xxx、
名前"yyy"が小さすぎます

上記のエラーメッセージが出力されたが、どのように対処すればよいのかという内容で弊社製品サポートにお問合せいただくことが多々あります。本エラーは発生頻度も高く、サポートレターの読者の方の中でも、今後本エラーに悩まされる方がいらっしゃるかと思います。
従って今号では、ORA-01555 が発生した場合にスムーズに対応できるよう本エラーの主な原因・対処方法についてご案内いたします。ご興味のある方は是非ご一読くださいませ。

ORA-01555 の発生原因について

ORA-01555 は、読み取り一貫性のために必要な UNDO データが上書きされたことにより発生します。それは、一般的に以下 2 点が原因で発生いたします。


  1. UNDO 表領域のサイズが不足していること
  2. 該当 SQL の実行に長時間を要すること

ORA-01555 の対応策について

ORA-01555 の発生はほとんどの場合偶発的な UNDO データの上書きであるため、通常ですとエラーが発生した処理を再実行すると、正常に処理が行われます。
処理を再実行しても同様のエラーが発生する場合は、対処が必要となりますが、大抵の場合、UNDO_RETENTION の設定値を増やすこと、UNDO ファイルのサイズ拡張で事象を回避できます。

以下に事象発生から対処までの一例をご案内します。

事象:以下の SQL を発行し、ORA-01555 エラーが発生した

SQL> alter session set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
SQL> create table sample (COL1 number not null, COL2 date);

表が作成されました。

SQL> begin
for r in 1..70000 loop
insert into sample values(r,SYSDATE);
end loop;
end;
/
SQL> commit;

PL/SQLプロシージャが正常に完了しました。

SQL> begin
for r in 1..70000 loop
update sample set col2=SYSDATE where col1=r;
commit;
end loop;
end;
/

PL/SQLプロシージャが正常に完了しました。

(別セッションにて)

SQL> SET TRANSACTION READ ONLY;
SQL> select count(*) from sample;

行1でエラーが発生しました。:
ORA-01555: スナップショットが古すぎます:
ロールバック・セグメント番号21、名前"_SYSSMU21_1687109957$"が小さすぎます

検証環境 : Oracle Database 11.2.0.4

対応手順(1): 現在の UNDO_RETENTION の設定値を確認する

SQL> show parameter undo_retention

NAME TYPE VALUE
--------------------- -------------- -----------------
undo_retention integer 900
★→現在の UNDO 保存期間は 900(15 分)であることを確認します。

対応手順(2): アラートログより該当エラーを調査する

view /u01/app/oracle/diag/rdbms/o11204/o11204/trace/alert_o11204.log

アラートログより抜粋

ORA-01555 caused by SQL statement below (SQL ID: 79up0sy4h1r1r, 
Query Duration= 1 sec, SCN: 0x0000.002e3ce4):
select * from sample

Query Duration は、エラーが発生した SQL 処理に要する時間を示します。同様のエラーを発生させないためにはこの値よりも UNDO_RETENTION を大きく設定するのが第一条件です。今回は Query Duration の値 (1) が UNDO_RETENTION よりも小さいため、UNDO_RETENTION の設定に問題がないことがわかります。

対応手順(3): 現在の UNDO データファイルのサイズを変更する

以下の SQL で現在の UNDO データファイルのサイズを確認します。

SQL> select name,bytes from v$datafile where NAME like '%undo%';

NAME BYTE 
------------------------------------------ -----------
/u01/app/oracle/oradata/o11204/undotbs.dbf 10485760
★→ 10MB であることを確認します。

以下の SQL で現在の UNDO データファイルの自動拡張設定を確認します。

SQL> select file_name,bytes,autoextensible,maxbytes from dba_data_files
2 where LOWER(file_name) like '%undo%';

FILE_NAME BYTES AUTOEXTEN MAXBYTES
------------------------------------------- --------- --------- ----------
/u01/app/oracle/oradata/o11204/undotbs.dbf 10485760 YES 10485760
★→自動拡張は ON、最大サイズ (maxbytes) が 10MB であることを確認します。

この場合、ファイルサイズが maxbytes と同じ値であるため、まずファイルの最大サイズを拡張し、次に現在のファイルサイズを拡張します。現在のファイルサイズより大きく設定する必要があるので、今回は現在のサイズの 3 倍である 30M に設定します。

※ファイルサイズの見積もりについては、後述の「◆ UNDO 領域のサイズ見積について」をご参照ください。

SQL> alter database datafile '/u01/app/oracle/oradata/o11204/undotbs.dbf'
autoextend on maxsize 30M ;

データベースが変更されました。

SQL> alter database datafile '/u01/app/oracle/oradata/o11204/undotbs.dbf'
resize 30M;

データベースが変更されました。

対応手順(4): ORA-01555 が発生した SQL を再実行する

SQL> begin
for r in 1..70000 loop
update sample set col2=SYSDATE where col1=r;
commit;
end loop;
end;
/

PL/SQLプロシージャが正常に完了しました。

(別セッションにて)

SQL> SET TRANSACTION READ ONLY;
SQL> select count(*) from sample;
COUNT(*)
----------
70000 ←★今回はエラーにならずに結果が返ってきました。

ただ、以下の場合、UNDO 領域の拡張により対処できないケースがございます。

  • LOB セグメントを使用している場合
  • READ ONLY トランザクションを使用している場合
  • UNDO セグメントが削除・破損されている場合
  • データベース・リンクを使用している場合

UNDO 領域のサイズ見積について

UNDO 領域のサイズを適切に見積もるには、UNDO アドバイザをご利用ください。UNDOアドバイザは Enterprise Manager(EM) または DBMS_ADVISOR よりアクセスすることができますが、EM でのご利用をお勧めいたします。EM から使用する場合「自動 UNDO 管理」ページよりご確認いただけます。

UNDO アドバイザは、以下の事項が確認できます。

  • 必要な UNDO 表領域の最小サイズ
  • UNDO 表領域の推奨サイズ

上記の情報をご参考に UNDO 領域のサイズをご検討いただければと思います。

(オラクル事業部 サポートセンター 齋藤)

ORA-01555 発生時の対処方法