How high can I set undo retention on Oracle 11gR2

Question

Is it viable to set a very high undo retention, as to allow flashback queries several weeks back in time?

Naturally, enough space must be available in the undo tablespaces to contain the amount of undo data required. Are there other limitations I should be aware of?

What happens if there is not enough undo space available? Will production be affected in any way, or is it just a matter of flashback and rollback being limited (snapshot too old etc).

Update:

With a typical undo generation of a little less than 1 GB per day per instance, and up to 64 GB worth of undo space per instance it sounds viable to run with a 30-day undo retention target. No?

Answer 1

Although I haven't tried it, I would tend to suspect that you'll run into problems when your UNDO_RETENTION exceeds the length of time that Oracle maintains its SCN to timestamp mapping. If memory serves, that is roughly 1 week (well, if memory serves, it was 1 week in the 10g days and I'm not aware of anything that would have changed that in 11.2). I would tend to suspect that Oracle would use that mapping to determine which UNDO segments are old enough that they should be purged and that you would start to encounter problems if you exceeded that limit.

On my 11.2 system, for example, I can convert 7 days ago to an SCN and then back to a timestamp but not 8 days ago

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 7) ) from dual
SQL> /

SCN_TO_TIMESTAMP(TIMESTAMP_TO_SCN(SYSTIMESTAMP-7))
---------------------------------------------------------------------------
06-NOV-12 01.06.47.000000000 PM

SQL> ed
Wrote file afiedt.buf

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
SQL> /
select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
                         *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

I would tend to suspect on my system, therefore, that if I tried to retain 8 days of UNDO that Oracle would generally have a problem complying. It would take a week to verify one way or the other but if I had to wager, I would tend to wager that trying to hold more than a week of UNDO would fail.

Even if you could hold the UNDO that long, formulating the flashback query would be a major challenge since you couldn't specify a timestamp. You could potentially maintain your own (more long-term) mapping between SCN and timestamp by writing an automated job that would capture both every few seconds and hold them for weeks or months at a time and then specify AS OF SCN in your flashback queries. I'd tend to expect that the older UNDO would be gone but it might work.

Answer 2

Very high is a relative term that depends on perspective. You could mean 1 hour, 1 month, 1 year, etc. Assuming you mean something between one minute and one month, the answer will depend on how much undo is being produced and how much storage you have available. I haven't seen anything higher than two weeks.

Oracle automatically manages the undo space and will dynamically decrease the retention below the undo retention setting if space constraints require it. The caveat to that is when the undo tablespace has guaranteed retention.

The Oracle Database Administrator's Guide covers all this information on one page. Here are some excerpts:

Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can optionally specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The exact impact this parameter on undo retention is as follows:

• The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size. See "Automatic Tuning of Undo Retention" for more information.

• For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. The UNDOTBS1 tablespace that is automatically created by DBCA is auto-extending.

...

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.