ORA-01555 error with aum (Automatic Undo Management)-cause and solution ., Aumora-01555

ORA-01555 error with aum (Automatic Undo Management)-cause and solution ., Aumora-01555

ORA-01555 error encountered when using aum (Automatic Undo Management)-cause and solution.

Reference Original:
ORA-01555 Using Automatic Undo Management-Causes and Solutions (Doc ID 269814.1)

Applicable:
Oracle Database-Enterprise Edition-Version 9.0.1.0 and later
Information in this document applies to any platform.

Purpose:
This article discusses when using the Automatic Undo Management (AUM) feature,
A query returns various scenarios of the ora-01555 "snapshot too old (rollback segment too small)" error and solutions to this error

Range:
This article allows you to see the following errors:
ORA-1555: snapshot too old (rollback segment too small)

Details:
Checked for relevance on 26-Mar-2012

There are many reasons for ora-01555 errors.
When you use the old rollback segments management methods such as in oracle 8i or earlier, the cause and solution are in Document 1005107.6 and Document 45895.1.

These causes and resolutions still apply to oracle 9i and later, which should be taken into account when resolving ora-01555 errors.
This document focuses on ora-01555 errors when using the AUM feature in oracle 9i or later.


UNDO_RETENTION

Ora-01555 errors may occur if the UNDO_RETENTION parameter is set improperly. Retention is specified in seconds. This parameter determines the low threshold of undo retention (threshold ).
Oracle tries its best to retain undo, at least until the time specified by this parameter. The UNDO_RETENTION parameter is used only when The current undo tablespace is sufficient (is only honored ).
If an active transaction requires undo space and the undo tablespace does not have free space, oracle starts to recycle the unexpired undo space. This causes some queries to fail and reports a ora-01555 error.

When a demand arises, a new extent will be allocated from the undo tablespace, but we will try to postpone this allocation behavior.

In 10.2.x and later versions, the order of using extent is as follows:

1. When the undo data is being written to an undo segment, if the undo data has reached the end of the current extent, and the next extent includes the expired undo,
Then the new undo data (the undo data generated by the current transaction) will use the (wrap into) expired undo instead of capturing the free extent from the undo tablespace free extent pool.
The new extent space will not be allocated.


2. If you need to expand the undo segment, we will first look for the free space in the undo tablespace, instead of extending the datafile, regardless of whether the AUTOEXTEND attribute of the datafile is set.
Note: In some cases, this step is broken and the bug is fixed in version 9205 or later.

3. If we imply (hint) to contract any undo segment to release the space, then we will do it and check whether the released space meets our needs.

4. Next, we will first find the offline segment as the expired extents that can be reused. If we know that all offline segments have no space, we will skip this step.

5. Can we steal any expired extent from other online segments?

6. If there is any automatically extended datafile in the tablespace, we will expand the data file in the tablespace to allocate space. Skip this step if there is no automatically extended datafile in the tablespace

7. If retention is guaranteed, we cannot automatically tune undo, and an error will be thrown.

8. We try to use a low UNDO_RETENTION value to confirm:
Can we release some space through doing any hinted shrinking of segments without violating any retention guarantee?

9. If we still have no space and are guaranteeing retention (are guaranteeing retention), an error will be thrown.

10. We use auto tuned and cannot guarantee it ). We started to steal UNEXPIRED extents from the offline segment.

11. Try to allocate the UNEXPIRED space allocated from the undo segment to the current transaction (transaction)

12. Try to steal UNEXPIRED space from other online segments.
Note: In a particular and rare case, oracle may not reuse (reuse) unexpired extents and then reports a ORA-30036 error


Solution:

1. The undo tablespace is too small to increase the size of the undo tablespace. The undo tablespace should be large enough to meet the undo data generated by the storage activity firm and to maintain the honor the undo retention setting.

2. optimized the value of the UNDO_RETENTION parameter. This is very important for running large queries in the system. The value of this parameter must be at least equal to the maximum query time.
As long as the data has been running for a period of time, the value of this parameter can be determined by querying the V $ UNDOSTAT View:
 
SQL> select max (maxquerylen) from v $ undostat;

The V $ UNDOSTAT view retains the udno statistics at intervals of 10 minutes,
This view displays statistical data by instance, such as begin time and end time. For each instance, the statistical value is a unique interval.

This view includes the following:

Column name Meaning
Start Time of the BEGIN_TIME Interval check
End Time of the END_TIME Interval check
UNDOTSN undo tablespace number
Total number of undo blocks used by UNDOBLKS during the interval
Total number of TXNCOUNT transactions during the interval
MAXQUERYLEN maximum query duration during the interval (in seconds)
Maximum number of transactions in MAXCONCURRENCY during the interval
UNXPSTEALCNT ----- number of attempts when unexpired blocks is stolen from other undo segments to meet space requirements.
UNXPBLKRELCNT removes the number of unexpired blocks used for other transactions from undo segment.
UNXPBLKREUCNT Number of unexpired undo blocks that are reused by transactions (reuse)
EXPSTEALCNT number of attempts when expired extents is stolen from other undo segments to meet space requirements.
Number of EXPBLKRELCNT expired extents, which are stolen from other undo segments to meet Space Requirements
EXPBLKREUCNT ----- Number of expired undo blocks reused in the same undo segment.
The number of times SSOLDERRCNT ora-1555 errors occurred during the interval.
The number of NOSPACEERRCNT Out-of-Space errors.


When the UNXPSTEALCNT Column holds a non-zero value through the EXPBLKREUCNT column, this is a sign of Space pressure.
If the column SSOLDERRCNT is non-zero, UNDO_RETENTION is not set correctly.
If the column NOSPACEERRCNT is non-zero, there is a very serious space problem.

3. There is a guaranteed undo retention option in oracle10g. When this option is enabled, the database will never overwrite unexpired undo data.
For example, the existence time of undo data is less than that of undo retention.
Ensure that the undo tablespace is large enough to meet guarantee requirement.
 
You can enable guarantee option,
To enable this function, specify the retention guarantee clause for the undo tablespace when creating the undo tablespace.
Or use the alter tablespace statement:
SQL> alter tablespace undotbs1 retention guarantee;
 
4. the required space depends on the number of undo blocks required in a specific period. It is linearly related to UNDO_RETENTION.
Use the formula mentioned in Document 262066.1 to calculate the size of the undo tablespace.

Note: For oracle 10gR2, there is an undo advisor in Enterprise Manager. With Enterprise Manager, you can manage undo

A) From the Database Control home page, click Administration. The Administration property page appears.
B) In the Database Configuration section, click Undo Management. You can use the Undo Management page to view the following about your undo configuration:
■ Name and size of undo tablespace
■ Auto-extend tablespace setting
■ Auto-tuned undo retention period
■ Minimum retention period
See the Oracle Database 2 Day DBA manual for more details.