ORA 1555 "snapshot too old (rollback segment too small)" BUG: 3060261

昨天帮一个朋友发现的

ORACLE 9204

[@more@]

The information in this article applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.3 to 9.2.0.7
This problem can occur on any platform.
Oracle Enterprise Edition Version 9.2

Errors

ORA 1555 "snapshot too old (rollback segment too small)"

Goal

To assist with identifying and understanding a known issue:

Bug 3060261: ENQUEUE_TIME AND START_TIME AHEAD OF SYSDATE

Fix

Bug 3060261: ENQUEUE_TIME AND START_TIME AHEAD OF SYSDATE
Several time related columns may refer to a time in the future. In particular this can affect:

V$TRANSACTION.START_TIME
(QUEUE_TABLE).ENQ_TIME
V$UNDOSTAT.BEGIN_TIME

The times can get further and further out the longer the instance is running. The incorrect times can cause various problems such as:
AQ messages not dequeued (as time is in the future)
Undo extents may not be expired

Rediscovery Information:

Check if the START_TIME column of V$TRANSACTION or the ENQ_TIME column of a queue table is chronologically later than sysdate.
The init.ora parameter UNDO_RETENTION is very high.
The output from v$transaction shows that the time is ahead of sysdate:

SQL> select start_time,to_char(sysdate,'mm/dd/yy hh24:mi:ss') from v$transaction
where to_date(start_time,'mm/dd/yy hh24:mi:ss') > sysdate;

Workaround: None


Problem is seen with respect to the difference in values between start_time in v$transaction and sysdate from dual value. We pick the sysdate info by calling an Oracle routine and there call another routine to get time from the system.
The problem is in an Oracle routine where the current time in the SGA is incremented; the algorithm is faulty because multiple processes can be doing this simultaneously. As a result, the current time keeps getting further and further out in the future. This routine was originally meant to be called only by the LGWR, but foregrounds also call it now - in fact, there are calls to it from about 32 different source files. This routine was not designed to handle these calls and did not do any latching.

This has been resolved and has backports available on ARU for Oracle version 9.2.0.4 and is fixed in Oracle v 10.1.0.2

References

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7318139/viewspace-989089/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7318139/viewspace-989089/