Let's see the error ORA-01555 UNDO_RETENTION first:
ORA-01555: snapshot too old: rollback segment number string with name "string" too small
rollback records needed by a reader for consistent read are overwritten by other writers
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:
- Undo space reclaimation
- Uncommitted active undo
- Committed expired undo
- Committed unexpired undo
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.
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.
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.
It depends on whether guaranteed is enforced or not to determine the behavior.
- RETENTION GUARANTEE
- RETENTION NOGUARANTEE
The data is not a reclaimable candidate until expired, but you must declare GUARANTEE explicitly.
The data is a reclaimable candidate although it's not expired. This is the default behavior of undo management without declaration.
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.
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.
- Resize current datafile.
- Autoextend current datafile.
- Add a new datafile.
SQL> alter database datafile '/u01/oracle/rbdb1/undotbs01.dbf' resize 800m;
SQL> alter database datafile '/u01/oracle/rbdb1/undotbs01.dbf' autoextend on;
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.
- Raise UNDO_RETENTION whenever AUTOEXTEND is enabled.
- Enforce GUARANTEE on UNDO_RETENTION.
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.
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.