ORA-01555: snapshot too old when using flashback query

Last week, a fellow DBA needed help with the error ORA-01555 using the flashback query feature in oracle 10g. On his database, the automatic undo management feature was enabled.

He wanted to obtain a table’s data from 12 hours ago because of a user’s mistake.
The source of the issue was that the UNDO tablespace was inappropriately sized and that the UNDO_RETENTION parameter value was set to the default value of 900(15 minutes). UNDO_RETENTION Reference

Since the database is an “OLTP” system with hundreds of transactions per minutes, the undo tablespace did not have the needed data to flashback the table to the desired time.
The only solution was to restore the whole database to the last valid backup on another server and then export the desired information. The process took an afternoon, but could have taken 5 minutes if the proper settings where in place.

The ORA-01555 error can be caused when the UNDO_RETENTION parameter is improperly set. And the UNDO_RETENTION parameter is only honored if the current undo tablespace has enough space. ORA-01555 Using Automatic Undo Management – Causes and Solutions

Here is a script estimate_undo.txt to help you size the undo tablespace appropriately with the desired UNDO_RETENTION parameter value.

sys@ORCL> @estimate_undo

Current UNDO_RETENTION value
—————————————————————–
900

Specify the desired UNDO_RETENTION in seconds. Ex: 86400=24 hours
Enter value in seconds: 86400

Using 86400 seconds of UNDO_RETENTION

Megs needed for UNDO
——————–
9444

Current max Undo Size in MB
——————————
5000

UNDO Size in MB FILE_NAME
—————— ——————————————————
2500 /OraData1/oradata/orcl/undotbs01.dbf
2500 /OraData2/oradata/orcl/undotbs02.dbf

In this case, the undo tablespace needs to be enlarged by 4.5 GB.
The undo_retention parameter needs to be set to 86400.

Alter database datafile ‘/OraData1/oradata/orcl/undotbs01.dbf’ resize 4750m;
Alter database datafile ‘/OraData2/oradata/orcl/undotbs02.dbf’ resize 4750m;
Alter system set undo_retention = 86400 scope=both;

The Flashback operations could even be 100% guaranteed by specifying the RETENTION GUARANTEE clause of the undo tablespace. Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

The UNDO Advisor can also help with the undo retention settings and for the sizing of the UNDO tablespace. Chris Foot has more info about the Undo Advisor here; his article also explains how the ORA-01555 error maintains the read consistency.

No additional Oracle license is required for using the UNDO Advisor; but I recommend you read this Metalink Note.

In Oracle 11g, there is a new feature called “Flashback Data Archive” that captures historical data that can be retained for as long as the business demands.

Yuri van Buren has a good example of this new feature on his blog: Blogging about 11g – Part 3 – Flashback Data Archive a.k.a. Total Recall

More info on Flashback Data Archive:

Flashback Data Archive Whitepaper

Total Recall

References:

How To Size UNDO Tablespace For Automatic Undo Management

Recovering Table data Using the Flashback Table Feature

ORA-01555 “Snapshot too old” – Detailed Explanation