How to Resolve ORA-01555 UNDO_RETENTION

ORA-01555 UNDO_RETENTION

Let's see the error ORA-01555 UNDO_RETENTION first:

Description

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause

rollback records needed by a reader for consistent read are overwritten by other writers

Action

If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

Before we drill down to a deeper topic, we need to clarify some concepts:

1. Undo space reclaimation

SMON will keep all undo as long as possible. It will do nothing about reclaimable candidates until the space is in pressure. When time comes, it will choose from the oldest to the nearest in order to reclaim.

In fact, lazy reclaimation is very popular algorithm on swiping unused but occupied resources. For example, we won't clean the snow on the roof every time it falls until it looks too heavy.

2. Uncommitted active undo

It is never overwritten or cleaned even though the undo space is full. Once undo space is full with uncommitted undo, the later DML will fail.

3. Committed expired undo

In terms of UNDO_RETENTION, committed expired undo is an undo data which ages over the limit defined by UNDO_RETENTION. It's a no-doubt candidate to be overwritten or cleaned.

4. Committed unexpired undo

It depends on whether guaranteed is enforced or not to determine the behavior.

• RETENTION GUARANTEE

The data is not a reclaimable candidate until expired, but you must declare GUARANTEE explicitly.

• RETENTION NOGUARANTEE

The data is a reclaimable candidate although it's not expired. This is the default behavior of undo management without declaration.

5. Rolled back undo

It's an expired undo which will become a reclaimable candidate once the rolling back is complete.

Raising the value of UNDO_RETENTION seems a good idea to solve the problem, but you may be disappointed in some conditions. You may check my post for more explanations: UNDO_RETENTION, How and Why

In this post, I will talk about ORA-01555 UNDO_RETENTION from two different perspectives to find the ways to reduce such error. You may judge the situation before actually doing it.

Solutions

The solutions depend on what condition that your database is able to provide.

When Resizing UNDO is Possible

UNDO_RETENTION is just an honored value to your queries in the default condition which is RETENTION NOGUARANTEE. Even more, undo space could be full before queries reach the time limit of UNDO_RETENTION and you will get ORA-01555 very early. Therefore, raising UNDO_RETENTION is useless, you need to enlarge the space if you can.

1. Resize current datafile.
SQL> alter database datafile '/u01/oracle/rbdb1/undotbs01.dbf' resize 800m;

2. Autoextend current datafile.
SQL> alter database datafile '/u01/oracle/rbdb1/undotbs01.dbf' autoextend on;

3. Add a new datafile.
SQL> alter tablespace undotbs add datafile '/u01/oracle/rbdb1/undotbs02.dbf' autoextend on maxsize unlimited;


Further reading: How Big a Data File Can Be?

Now, the undo data will be kept as long as the space is enough for new transactions.

When Resizing UNDO is NOT Possible

If your UNDO tablespace cannot be resized or auto extended in this moment, then what you can do is very limited to none. My strategy is to compromise new transactions instead of queries so as to avoid ORA-01555. Which means, we raise and guarantee UNDO_RETENTION can be reached, no matter what.

1. Raise UNDO_RETENTION whenever AUTOEXTEND is enabled.
SQL> alter system set undo_retention = 14400 scope=both sid='*';


UNDO_RETENTION is kind a soft limit which is no guarantee. If the space is no longer extendable for new undo, the oldest committed undo will be sacrificed. So we have to make it GUARANTEE.

2. Enforce GUARANTEE on UNDO_RETENTION.
SQL> alter tablespace undotbs retention guarantee;


To reverse the setting, you can do this:

SQL> alter tablespace undotbs retention noguarantee;


The database will never touch unexpired undo with RETENTION GUARANTEE even if new DML transactions fail.

Therefore, a better practice is that you should ensure the undo space is enough to extend before enabling RETENTION GUARANTEE, otherwise, new DML transactions will fail due to a full undo.

More concepts can be found at Oracle website: Managing Undo

Just remember, keep your undo data in the UNDO tablespace as long as possible is the key to reduce ORA-01555.