Troubleshooting ORA-01555 - Snapshot Too Old: Rollback Segment Number "String" With Name "String" Too Small (Doc ID 1580790.1)
APPLIES TO:
Oracle Fusion Global Human Resources Cloud Service - Version 11.1.10.0.0 to 11.1.10.0.0 [Release 1.0]
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Oracle Database - Standard Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database - Standard Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.
PURPOSE
This document provides troubleshooting steps to resolve ORA-01555 / ORA-22924. It also discusses known issue and bugs related ORA-01555 / ORA-22924.
本文档提供了解决ORA-01555 / ORA-22924的故障排除步骤。它还讨论了与ORA-01555 / ORA-22924相关的已知问题和错误
TROUBLESHOOTING STEPS
1) Check Error Messages: 检查错误消息
Check alert log (or the log where the error is reported) to determine 1555 error message as we have some different types of 1555 error messages:
检查alert log（或报告错误的日志）以确定1555错误消息，因为我们有一些不同类型的1555错误消息
a) Identify segment name: 标识细分名称
--> notice that segment name is null "" or/and ORA-22924: snapshot too old
In this case 1555 error is reported while accessing UNDO data stored inside LOB segment. ORA-01555 for LOB segment is caused by one of the following reasons :
在这种情况下，访问LOB段中存储的UNDO数据时报告错误1555。LOB段的ORA-01555是由于以下原因之一引起的
1. LOB segment corruption: LOB段损坏
To check the LOB table corruption, review the following documents : 要检查LOB表损坏，请查看以下文档
Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1)
Export Fails With ORA-2354 ORA-1555 ORA-22924 and How To Confirm LOB Segment Corruption Using Export Utility? (Doc ID 833635.1)
ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. (Doc ID 452341.1)
2. If no LOB corruption found, so Issues with Retention/Pctversion values : 如果未发现LOB损坏，则Retention/Pctversion值的问题
You may need to increase Retention/Pctversion. Check following document carefully : 您可能需要增加Retention/Pctversion。仔细检查以下文件
LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)
- ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small
notice that segment name is existing "_SYSSMU107_1253191395$" which mean undo data inside UNDO tablespace.
请注意，段名称是现有的"_SYSSMU107_1253191395$"，这表示undo数据在UNDO表空间中
In this case, 1555 error is reported while accessing UNDO data at UNDO tablespace which will discuss how to troubleshoot in this document.
在这种情况下，访问UNDO表空间上的UNDO数据时报告1555错误，它将在本文档中讨论如何进行故障排除
b) Identify Query Duration: 确定查询持续时间
Failed query duration is logged in some 1555 error messages which showed at alert log file or any application log.
查询持续时间失败会记录在 alert log 文件或任何应用程序日志中显示的一些1555错误消息中
ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7):
- If query duration = 0 or few seconds, check the following document :
ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds (Doc ID 1131474.1)
- If query duration equal to a value closer or larger than undo_retention value proceed for following analysis.
- 如果查询持续时间大于或等于undo_retention值，请进行以下分析
2) Check Undo Datafiles :
select tablespace_name, file_id, sum(bytes)/1024/1024 a, sum(maxbytes)/1024/1024 b, autoextensible from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' ) group by file_id, tablespace_name, autoextensible order by tablespace_name;
If you are using non-autoextensible UNDO data files, this can lead to high calculation of tuned_undoretntion and hence high allocation of undo space especially with large UNDO data files.
如果您使用的是非自动扩展的UNDO数据文件，则这可能导致 tuned_undoretntion 的计算量增加，从而导致undo空间的大量分配，尤其是对于大型的UNDO数据文件
To avoid this make sure that the UNDO data files are autoextensible even you have enough free space .
为避免这种情况，请确保即使您有足够的可用空间，UNDO数据文件也可以自动扩展
注意：强烈建议不要在undo tablespace中同时具有可自动扩展和不可自动扩展的撤消数据文件，因为这会导致 TUNED_UNDORETENTION 计算错误。
3) TUNED_UNDORETENTION :
SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat; SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;
In case you have large value for TUNED_UNDORETENTION, this can be caused by two reasons :
如果 TUNED_UNDORETENTION 值很高，可能是由于两个原因引起的
1. As described in step 2), Using non-autoextensible UNDO data files which can be resolved by one of the following :
1. 如步骤2)中所述，使用非自动扩展的UNDO数据文件，这些文件可以通过以下其中一种方法来解析
- Set _smu_debug_mode=33554432 (recommended 推荐)
With this setting, TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace.
使用此设置，不会基于固定大小的 undo tablespace 的百分比来计算 TUNED_UNDORETENTION
Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
而是将其设置为最大值(MAXQUERYLEN secs + 300) and UNDO_RETENTION
- set autoextensible= YES for all UNDO data files 为所有UNDO数据文件设置autoextensible = YES
- set "_undo_autotune" = false and undo_retention=max(maxquerylen)
- set SET "_highthreshold_undoretention"=max(maxquerylen)+1
2. Long running queries which can raise tuned_undoretention to very high values.
2. 长时间运行的查询可能会将 tuned_undoretention 提升到非常高的值
You need to tune these queries to avoid retain UNDO data for more time inside UNDO tablespace.
您需要调整这些查询，以避免将UNDO数据保留在UNDO表空间中更多的时间
To identify these long queries, run the following :
要识别这些长查询，请运行以下命令
select MAXQUERYSQLID, MAXQUERYLEN from DBA_HIST_UNDOSTAT order by MAXQUERYLEN desc; select MAXQUERYID, MAXQUERYLEN from v$UNDOSTAT order by MAXQUERYLEN desc;
4) High utilization of ACTIVE/UNEXPIRED extents : ACTIVE/UNEXPIRED extents 的高利用率
SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;
Excessive allocation of ACTIVE/UNEXPIRED extents can be caused by one of following reasons :
可能由于以下原因之一导致 ACTIVE/UNEXPIRED extents的过度分配
- large value of undo_retention or TUNED_UNDORETENTION as described above.
- 如上所述，undo_retention或TUNED_UNDORETENTION的值较大
- large UNDO data generation at certain point of time which can be identified by run following query :
- 在特定时间生成大量的UNDO数据，可以通过运行以下查询来识别
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; select BEGIN_TIME , UNDOBLKS from DBA_HIST_UNDOSTAT order by UNDOBLKS desc ; select BEGIN_TIME , UNDOBLKS from v$UNDOSTAT order by UNDOBLKS desc;
- large dead transaction(s) rollback
- Using flashback data archive
For more details:
How To Check the Usage of Active Undo Segments in AUM (Doc ID 1337335.1)
5) UNDO_RETENTION :
We recommend to set undo_retention to at least the average of maximum queries length as follow and increase it if 1555 error still reported :
我们建议将undo_retention至少设置为最大查询长度的平均值，如下所示，如果仍然报告1555错误，则将其增加
select avg(maxquerylen) from DBA_HIST_UNDOSTAT; select avg(maxquerylen) from v$UNDOSTAT;
6) 1555 error during export : 导出期间1555错误
To diagnose resolve 1555 error during export check following document :
要在导出期间诊断解决1555错误，请检查以下文档
Troubleshooting ORA-01555/ORA-01628/ORA-30036 during export and import (Doc ID 1579437.1)
7) How to find the complete SQL statement caused ORA-1555 : 如何查找导致ORA-1555的完整SQL语句
If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from :
如果错误ORA-1555之后数据库没有重新启动，则可以从以下语句获取该语句
select SQL_TEXT from V$SQL where SQL_ID='<sql id from the error message>';
If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from :
如果数据库在错误ORA-1555之后重新启动，并且在重新启动之前收集了AWR快照，那么可以从以下语句获取该语句：
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='<sql id from the error message>';
REFERENCES
NOTE:1131474.1 - ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds
NOTE:787004.1 - Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922
NOTE:735366.1 - Run Out Of Space On UNDO Tablespace Using DataPump Import/Export
NOTE:846079.1 - LOBs and ORA-01555 troubleshooting
NOTE:833635.1 - Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility
NOTE:1579437.1 - Troubleshooting ORA-01555/ORA-01628/ORA-30036 During Export and Import
NOTE:452341.1 - ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption.
NOTE:467872.1 - Explaining ORA-1555 Error