Troubleshooting ORA-01555

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:  标识细分名称

ORA-01555: snapshot too old: rollback segment number  with name "" too small

--> 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数据文件也可以自动扩展

NOTE: It is strongly not recommended to have both autoextensible and non-autoextensible undo data files in the undo tablespace as will be lead to TUNED_UNDORETENTION mis-calculation.

注意：强烈建议不要在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