Ошибка ora 01555

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.

Resolving The Problem

If you are running Oracle 9i or higher, you should be using an undo tablespace with automated undo management instead of a rollback tablespace. The use of an undo tablespace will prevent most of these errors.

ORA-01547 failed to allocate extent of size nnn in tablespace xxx

There is not enough room in the tablespaces file(s) to add an extent of nnnblocks.

Increase the size of the rollback tablespace by one of the following two methods. Both can be done
while users are active.

1. Add a datafile.

(a) Connect to ORACLE as SYSTEM via SQLPLUS and execute the following to see
the list of tablespaces and the files they own:

SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME,FILE_NAME;

(b) Add a datafile to the rollback tablespace (RBS on UNIX, ROLLBACK_DATA on NT or NLM).
Use the same path as the rollback tablespace files above, but a distinct filename. Use
a size of nnn megabytes for the new file.

ALTER TABLESPACE rollback_tablespace_name ADD DATAFILE
‘full_path_and_filename‘ SIZE nnnM;

2. Turn on AUTOEXTEND for one of the rollback datafiles (ORACLE 7.2.x or later only)

Connect to ORACLE as SYSTEM via SQLPLUS and execute the following to see
the list of tablespaces and the files they own:

SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME,FILE_NAME;

Turn on AUTOEXTEND and limit maximum size to nnn megabytes.

ALTER DATABASE DATAFILE ‘existing_rollback_file_path_and_name‘
AUTOEXTEND ON MAXSIZE nnnM;

ORA-01555 snapshot too old (rollback segment too small)

This generally occurs when a long running query shares a rollback segment with an
active UPDATE, INSERT, or DELETE. Instead of adding a new extent to the rollback
segment, ORACLE overwrites one of the extents used by the SELECT in the belief that
it has not been referenced recently enough to still be needed.

The usual fix for this is to recreate your rollback segments with more and/or larger
initial extents. You may also need to increase the size of the rollback tablespace
as per the ORA-01547 error above.

To recreate rollback segments, execute the following statements for each rollback
segment. The value for minextents should be large enough so that minextents times
initial is larger than the size of the table for which the SELECT error occurred.

Connect to ORACLE as SYSTEM via SQLPLUS and execute the following:

ALTER ROLLBACK SEGMENT name OFFLINE;

DROP ROLLBACK SEGMENT name;

CREATE ROLLBACK SEGMENT name TABLESPACE ts_name
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS nn );

ALTER ROLLBACK SEGMENT name ONLINE;

If you have active users, you will find that in-use rollback segments will not go offline
immediately, but will go into a PENDING-OFFLINE state. You must then wait until
the segment goes offline to DROP and CREATE it. You can check on the status
by typing:

SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

ORA-01556 maximum number of extents exceeded

The database attempted to allocate more than MAXEXTENTS to the rollback segment.
The default value of MAXEXTENTS is dependent on your operating system’s disk blocksize.
Usually 121 or 249 on unix and 121 on NT/Novell.

You should recreate your rollback segments with a larger INITIAL and NEXT value.

To check the current values, connect to ORACLE as SYSTEM via SQLPLUS
and execute the following:

SELECT SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT FROM
DBA_ROLLBACK_SEGS;

The sizes will be in bytes. Execute the ALTER/DROP/CREATE/ALTER sequence
as for the ORA-01555 error above to recreate the rollback segments with larger extents.

If you are running version 7.3 or higher, you can also increase the MAXEXTENTS value:

ALTER ROLLBACK SEGMENT name DEFAULT STORAGE
(MAXEXTENTS larger_value);

The above command must be repeated for each rollback segment.

ORA-01562 failed to extend rollback segment

This is normally followed by another error. Follow the procedure for the subsequent
error message. You may need to examine the database alert log to determine
the subsequent error.

The location of the alert log can be determined by running the following statement
from the ORACLE SYSTEM login:

SELECT VALUE FROM V$PARAMETER WHERE NAME
= ‘background_dump_dest’;

ORA-01552 cannot use system rollback segment for non-system tablespace …

Your public rollback segments are offline, or perhaps were never defined.

Check the status of your rollback segments by connecting to ORACLE as SYSTEM and
typing:

SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

You will likely see that all except SYSTEM are OFFLINE. Put them online with the command:

ALTER ROLLBACK SEGMENT name ONLINE;

There is a good chance that you do not have a rollback_segments parameter in your
initxxx.ora file, or it has been commented out. You should have something like:

rollback_segments = (name1,name2,…,namen)

Listing each of your non-system rollback segments. This will cause them to be brought
online automatically at each startup (instead of having to use ALTER commands).

If the SELECT statement above shows NO rollback segments other than system, you will
need to create them (see commands for error ORA-01555 above) and also add the
rollback_segments parameter to your initxxx.ora file.

There are many reasons for this error. In this post I am providing all the possible scenarios and related Oracle Notes/Links.

Concepts/Definitions

The ORA-1555 errors can happen when a query is unable to access enough undo to build
a copy of the data at the time the query started. Committed «versions» of blocks are
maintained along with newer uncommitted «versions» of those blocks so that queries can
access data as it existed in the database at the time of the query. These are referred to as
«consistent read» blocks and are maintained using Oracle undo management.

See Document 40689.1 — ORA-1555 «Snapshot too old» — Detailed Explanation for more about
these errors.