ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds (Doc ID 1131474.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Symptoms:
- A job or process is consistently giving ORA-1555 errors.
- job or process持续出现ORA-1555错误
- Increasing the undo_retention value has not stopped the errors.
- 增加undo_retention值并未停止错误
- The ORA-1555 occurs even if there are no other jobs/processes updating the objects reported in the failed SQL.
- 即使没有其他 jobs/processes 更新失败SQL中报告的对象，也会发生ORA-1555
- The datafiles for the undo tablespace have autoextend turned on, and the file size is less than MAXBYTES.
- undo tablespace的数据文件已启用自动扩展，并且文件大小小于MAXBYTES
- A look at the v$undostat shows that the query length is less than both the undo_retention and the tuned_undoretention
- 查看 v$undostat 显示查询长度小于undo_retention和tuned_undoretention两者
To find the max query length and the tuned_undo retention, run this SQL.
--要查找最大查询长度和tuned_undo retention，请运行此SQL set pagesize 25
set linesize 100
column UNXPSTEALCNT heading "# Unexpired|Stolen"
column EXPSTEALCNT heading "# Expired|Reused"
column SSOLDERRCNT heading "ORA-1555|Error"
column NOSPACEERRCNT heading "Out-Of-space|Error"
column MAXQUERYLEN heading "Max Query|Length"
select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION
from gv$undostat
order by inst_id, begin_time; Then look for the timestamp at which the ORA-1555 occurred.
--然后查找发生ORA-1555的时间戳
The max query length will be less than undo_retention, and less than tuned_undoretention.
--max query length 将小于 undo_retention，并且小于tuned_undoretention
Note: When tuned_undoretention is used, the undo_retention becomes a minimum setting.
--注意：使用tuned_undoretention时，undo_retention成为最小设置。
Here is an example of the output of the above SQL 这是上述SQL输出的示例
Unexpired Expired ORA-1555 Out-Of-space Max Query
INST_ID BEGIN_TIME Stolen Reused Error Error Length TUNED_UNDORETENTION
------- ---------------- ---- ------ ----- ------------ ---------- ------------
1 06/14/2010 20:51 0 0 0 0 719 2000
1 06/14/2010 21:01 0 0 1 0 1323 2225 <--ORA-1555 here
1 06/14/2010 21:11 0 0 0 0 699 2000
1 06/14/2010 21:21 0 0 0 0 1303 2205
1 06/14/2010 21:31 0 0 0 0 678 2000
We can see that there were no stolen unexpired extents, and the length of the query was 1323 seconds, while the tuned_undoretention was 2225 seconds.
我们可以看到没有未到期的扩展数据段，查询的长度为1323秒，而tuned_undoretention为2225秒。
There were also no out-of-space errors. 也没有空间不足的错误
CAUSE
The issue can be caused by two reasons: 该问题可能是由于两个原因引起的
1. Indexes/table mismatch 索引/表不匹配
OR
2. Similar issue is reported as Bug 8231583 Abstract: ORA-1555 WHEN QUERY LENGTH < TUNED RETENTION
2. 相似的问题报告为 Bug 8231583 Abstract: ORA-1555 WHEN QUERY LENGTH < TUNED RETENTION
The bug has been closed at this point without finding a root issue. The problem is not easy to reproduce consistently.
该错误目前已关闭，没有找到根本问题。问题不容易一致地重现。
SOLUTION
1. If the 1555 error is consistently reported against a certain table each time, so this most like is caused by Indexes/table mismatch and you need to drop/recreate (not rebuild) all table indexes.
1. 如果每次都针对某个表始终报告1555错误，那么这很像是由 Indexes/table 不匹配引起的，您需要 drop/recreate (not rebuild) 所有表索引
Similar issue described in Note: 977902.1.
2. Bug 8231583 reported on this issue is closed due to the lack of reproducibility.
2. 由于缺乏可重复性，因此已报告关于此问题的错误8231583
There are a few workarounds identified for this issue. One option is to create a new undo tablespace and switch to the new Undo tablespace.
有一些解决此问题的方法。一种选择是创建一个新的undo tablespace并切换到新的Undo tablespace
CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON; ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
The old undo can only be dropped after all transactions have cleared from the undo tablespace.
只有在undo tablespace中清除了所有事务之后，才能删除旧的undo
Please refer to the Oracle Database Administrator's manual for creating a new undo, switching to a new undo tablespace, and dropping the undo tablespace.
Oracle Database Administrator's Guide
10g Release 2 (10.2)
Chapter 10 Managing the Undo Tablespace
Oracle Database Administrator's Guide
11g Release 2 (11.2)
Chapter 15 Managing Undo
Section: Managing Undo Tablespaces
As a second option, disable auto tuning by setting 第二种选择是通过设置disable auto tuning
"_undo_autotune" = false
after creating a new undo tablespace has avoided future occurrences of the problem condition at some database locations.
创建新的undo tablespace后，避免了将来在某些数据库位置出现问题情况。
Until a consistent, reproducible test case can be developed in-house this problem remains unresolved.
在内部开发出一致，可重复的测试用例之前，这个问题仍然没有解决。
3. Afterwords, If this is still consistently reproducible in your database then to progress this with development, check Note: 761128.1 to get diagnostic information needs to be collected at the time the ORA-01555 error occurs to be able to engage development.
3. 后记，如果仍然可以在您的数据库中始终复制此代码，则要继续进行开发，请检查注：761128.1以获取诊断信息，以便在发生ORA-01555错误时能够进行开发。
REFERENCES
NOTE:461480.1 - FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)
NOTE:877613.1 - AUM Common Analysis/Diagnostic Scripts
BUG:18602141 - ORA-1555 QUERY FAILING BEFORE UNDO_RETENTION IS REACHED
BUG:19014280 - ACTIVE DG: LGWR BLOCKS SEVERAL SESSIONS DURING GLOBAL FLUSH
BUG:18899974 - ORA-600 [KCBGTCR_13] ON ACTIVE DATAGUARD
BUG:8231583 - ORA-01555 WHEN QUERY LENGTH < TUNED RETENTION
BUG:8231583 - ORA-01555 WHEN QUERY LENGTH < TUNED RETENTION
NOTE:240746.1 - 10g NEW FEATURE on AUTOMATIC UNDO RETENTION