ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-1555 Using Automatic Undo Management - How to troubleshoot [ID 389554.1]

ORA-1555 Using Automatic Undo Management - How to troubleshoot [ID 389554.1]

原创 Linux操作系统 作者:spider0283 时间:2011-09-12 11:49:47 0 删除 编辑

修改时间 16-AUG-2011     类型 BULLETIN     状态 PUBLISHED 

Applies to:

Oracle Server - Enterprise Edition - Version: 7.3.3.0 and later   [Release: 7.3.3 and later ]
Information in this document applies to any platform.

Purpose

This article discusses the means to troubleshoot and diagnose the ORA-01555 "snapshot too old" error 
when using the AUM (Automatic Undo Management) feature and outlines the solutions for the different 
type of problems.

Scope and Application

For users experiencing the ORA-01555 error:
ORA-1555: snapshot too old (rollback segment too small) 

ORA-1555 Using Automatic Undo Management - How to troubleshoot

1. Collect the relevant information

The following message is reported in the user session

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

The alert.log will contain a message similar to:

  ORA-01555 caused by SQL statement below (Query Duration=8212 sec, SCN: 0x0000.0088e8d2):
 Tue Aug 22 10:58:43 2006
 SELECT * FROM SCOTT.BIGEMP e, SCOTT.BIGDEPT d

From this information we know that the ORA-1555 occurred on segment number 9 with name "_SYSSMU9$" and the failing statement. We know that the Query was running for '8212 sec' when the error occurred.

In some cases you will see a large number or a 0 for the 'Query Duration'.  Seeing 0 for the query duration is most likely because of internal bug 3301573 'ORA-1555 error may report nonsense query start time'

Note 3301573.8: Bug 3301573 - ORA-1555 error may report nonsense query start time 

If this is the case, then please note there are other ways of identifying the query duration time, which are discussed later on in this article.


Special considerations to be made

- Before-images of LOB data are stored in the segment itself, rather then the undo segment/tablespace. Therefore you first need to verify whether LOB columns are used.  This need to be done for every table involved in the problem code.  The list of tables can be retrieved from the SQL statement in the alert.log.

For example:

SQL> desc bigemp
SQL> desc bigdept

If there are no LOB columns as part of the problem, then proceed with the rest of this article, but if there is no other reason for the ORA-1555 then the solution is to increase the PCTVERSION of the LOB column(s).

This is discussed in detail in:

Note 162345.1: LOBS - Storage, Read-consistency and Rollback 

- Is the instance part of a RAC environment?  If this is the case then it is important to know to which instance the undo segment belongs. It is possible that instance 1 reports an ORA-1555 error but the undo segment is actually allocated to another instance.  We need to know on which instance to collect the additional information to troubleshoot the ORA-1555 error.
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$';

Please note that for RAC environments Oracle recommends to use the same undo configuration across the different instances.

2. What additional information do we need ?

- The undo configuration:

SQL> show parameter undo
 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -------------
 undo_management string AUTO
 undo_retention integer 7200
 undo_suppress_errors boolean FALSE
 undo_tablespace string UNDOTBS1

Please note that there are different ways of retrieving the undo parameter values. This is just one of them.

- The undo extent usage
This can be retrieved using the following query. Note that this also gives you the 'Query Duration' time
 up to the point the ORA-1555 occurred:

REM In this example, the errors happened at 
REM 11:25am on 30-APR-2011

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('04/30/2011 08:25','MM/DD/YYYY HH24:MI')
and to_date('04/30/2011 11:25','MM/DD/YYYY HH24:MI')
order by inst_id, begin_time;

NOTE: With automatic retention tuning (UNDO_MANAGEMENT=AUTO), you can adjust the script. below to include the TUNED_UNDORETENTION column as well column TUNED_UNDORETENTION heading "Tuned Undo|Retention" add TUNED_UNDORETENTION to the select list above.

3. Examples

The following are examples of an ORA-1555 with automatic undo management. Please read the undo algorithm used in:

Note 269814.1: ORA-01555 Using Automatic Undo Management - Causes and Solutions

to get a better understanding of the following examples.


Example #1: undo_retention too low
----------

# 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.

Example #2: undo tablespace too small
----------

# 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
  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 occurred 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.

Remarks

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 the before-images to stay on hand 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.

 

References


Note 10630.1 - ORA-01555: "Snapshot too old" - Overview
Note 40689.1 - ORA-01555: "Snapshot too old" - Detailed Explanation



显示相关信息 相关的


产品
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
关键字
DYNAMIC; ORA-1555; TROUBLESHOOT; UNDO_RETENTION; DYNAMIC ADV DIAGNOSTIC TOOLS
错误
ORA-1555; 01555 ERROR; 1555 ERROR

返回页首返回页首

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

上一篇: NFSEN NETFLOW系统
请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    627863