ORA-1555 Using Automatic Undo Management - How to troubleshoot

相关错误信息:
在用户的会话中报如下错误:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small
在alert.log中报如下错误:
ORA-01555 caused by SQL statement below (Query Duration=8212 sec, SCN: 0x0000.0088e8d2):
以上信息表明,在_SYSSMU9$回滚段上发生了ORA-1555错误,另外发生错误的查询费时8212秒.如果你看到是一个非常大的数值或0,这可能是BUG3301573导致的.如果是这种情况,我们将稍后讨论确定其查询时间的方法.
以下情况需要特殊考虑:
1 是否有LOB列.(如有,可以参考文档[url=https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=162345.1&blackframe=1]Note 162345.1[/url]: LOBS - Storage, Read-consistency and Rollback)
2 是否是RAC环境.如是则必须了解回滚段是属于哪个例程的.可以通过以下查询来获知:
select stat.inst_id, seg.segment_name, seg.tablespace_name
from dba_rollback_segs seg, gv$rollstat stat
where seg.segment_id = stat.usn
and seg.segment_name='';
eg.: seg.segment_name='_SYSSMU9$';

另外我们还需知道自己的配置和回滚段的使用情况:
查看回滚的配置:
SQL> show parameter undoNAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
undo_management                      string      AUTO
undo_retention                       integer     7200
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
查询回滚段的使用情况:
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
from gv$undostat
where begin_time between to_date('','MM/DD/YYYY HH24:MI:SS')
and to_date('','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;

以下是当配置自动回滚管理时的具体错误示例:
例1:undo_retention 设置太低了
                            # Unexpired  # Expired   ORA-1555 Out-Of-space  Max Query
   INST_ID BEGIN_TIME            Stolen     Reused      Error        Error     Length
   ------- ---------------- ----------- ---------- ---------- ------------ ----------
       ...
         1 08/28/2006 10:20           0          0          0            0       5852
         1 08/28/2006 10:30           0          0          0            0       6252
         1 08/28/2006 10:40           0          0          0            0       6852
         1 08/28/2006 10:50           0         32          0            0       7452
         1 08/28/2006 11:00           0          7          1            0       8212

The undo_retention was set to 7200 seconds and the Query was running for 8212 seconds
  before it failed with the ORA-1555. The output shows that we re-used expired extents.
  To resolve this problem the undo_retention period must be increased to be higher than
  the 'Query Duration' time.

例2:重做表空间太小
                         # Unexpired  # Expired   ORA-1555 Out-Of-space  Max Query
   INST_ID BEGIN_TIME            Stolen     Reused      Error        Error     Length
   ------- ---------------- ----------- ---------- ---------- ------------ ----------
       ...
         1 08/28/2006 10:20           0          0          0            0          1
         1 08/28/2006 10:30           0          0          0            0          1
         1 08/28/2006 10:40           0          0          0            0          1
         1 08/28/2006 10:50          23          0          0            0        272
         1 08/28/2006 11:00          67          0         1            1        843
  The output shows there was an 'Out-Of-Space' error and that we have stolen several unexpired   extents. The undo tablespace was not big enough to respect the undo_retention period, hence extents/blocks were stolen. The solution is to increase the undo tablespace size.
   Note 262066.1: How To Size UNDO Tablespace For Automatic Undo Management

例3 RAC环境
Example #3: RAC environment
  ----------
                            # Unexpired  # Expired   ORA-1555 Out-Of-space  Max Query
   INST_ID BEGIN_TIME            Stolen     Reused      Error        Error     Length
   ------- ---------------- ----------- ---------- ---------- ------------ ----------
         1 08/28/2006 14:58           0          0          0            0       8111  
         1 08/28/2006 15:08           0          0          0            0       8711
         1 08/28/2006 15:18           0          0          1            0       9245
         2 08/28/2006 15:04           0         13          0            0       1020
         2 08/28/2006 15:14          17         16          0            1       1020
         2 08/28/2006 15:24           0          0          0            0       1020
  The ORA-1555 occured in instance 1, however the problem relates to instance 2 where we
  encountered an Out-Of-Space error and as a result stole some unexpired extents.
  The solution is to increase the undo tablespace in instance 2.

Before increasing the undo tablespace, you should first verify the undo_retention parameter
and make sure it is set to a reasonable value. Setting the undo_retention to 10000 seconds
while the longest query on the system runs only for 300 seconds, causes that the before-images
are kept for far too long. To get an idea on how long queries are running, you can run:
  select inst_id, max(maxquerylen)
  from gv$undostat
  group by inst_id;
This needs to be captured when the system has been running for a while and is fully used.

 

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

转载于:http://blog.itpub.net/9375/viewspace-509794/