ORA-01555 発生時の対処方法
*以下は、サポート契約締結中のお客様に2015年7月号として配信しているサポートレターより一部抜粋して掲載しています。
ORA-01555 発生時の対処方法
ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号 xxx、
名前"yyy"が小さすぎます
上記のエラーメッセージが出力されたが、どのように対処すればよいのかという内容で弊社製品サポートにお問合せいただくことが多々あります。本エラーは発生頻度も高く、サポートレターの読者の方の中でも、今後本エラーに悩まされる方がいらっしゃるかと思います。
従って今号では、ORA-01555 が発生した場合にスムーズに対応できるよう本エラーの主な原因・対処方法についてご案内いたします。ご興味のある方は是非ご一読くださいませ。
ORA-01555 の発生原因について
ORA-01555 は、読み取り一貫性のために必要な UNDO データが上書きされたことにより発生します。それは、一般的に以下 2 点が原因で発生いたします。
- UNDO 表領域のサイズが不足していること
- 該当 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 領域のサイズをご検討いただければと思います。
(オラクル事業部 サポートセンター 齋藤)