【Bug】ORA-01555 When Max Query Length Is Less Than Undo Retention

ORA-01555 When Max Query Length Is Less Than Undo Retention [ID 1131474.1]

涉及的版本！

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2

Information in this document applies to any platform.

Symptoms

This issue was observed on Oracle 11.1.0.6 64-bit on a Solaris 64-bit server.It also occurs in 10gR2

现象：

- A job is consistently giving ORA-1555 errors.

- Increasing the undo_retention value has not stopped the errors.

- The ORA-1555 occurs even if there are no other jobs updating the objects reported in the failed SQL.

- The datafiles for the undo tablespace have autoextend turned on, and the file size is less than MAXBYTES.

- A look at the v$undostat shows that the query length is less than both the undo_retention and the tuned_undoretention

To find the max query length and the tuned_undo retention, run this 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.

The max query length will be less than undo_retention, and less than tuned_undoretention.

Note: When tuned_undoretention is used, the undo_retention becomes a minimum setting.

Here is an example of the output of the above 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 1313 seconds, while the tuned_undoretention was 2225 seconds.

There were also no out-if-space errors.

我们的环境里：

ops$admin@jrdw2>select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION

2 from gv$undostat

3 where begin_time between to_date('10/09/2011 03:10','MM/DD/YYYY HH24:MI')

4 and to_date('10/09/2011 04:50','MM/DD/YYYY HH24:MI')

5 order by inst_id, begin_time;

                 # Unexpired # Expired  ORA-1555 Out-Of-space MaxQuery  Tuned Undo

INST_ID BEGIN_TIME Stolen      Reused   Error     Error       Length    Retention

----------        ----------- --------- -------- ----------  ---------- ----------

1 10/09/2011 03:13 0           0        0          0          1183      52010

1 10/09/2011 03:23 0           0        0          0          759       51895

2 10/09/2011 04:25 0           0        0          0          614       124019921

2 10/09/2011 04:35 0       0        1          0          1215  124112694

2 10/09/2011 04:45 0           0        0          0          595       124040364

20 rows selected.

原因：

This is the same issue as 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 Bug 8231583 is unresolved.

措施：

方法一：Until the bug is resolved, one workaround is to create a new undo tablespace and switch to the new.

For example:

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.

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

方法二：disabling auto tuning by setting "_undo_autotune" = false

after creating a new undo tablespace has avoided future occurrences of the problem condition at some database locations. 

Until a consistent, reproducible test case can be developed in-house this problem remains unresolved.