How high can I set undo retention on Oracle 11gR2

oracleoracle-11g-r2oracle-database-applianceoracle-rac

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?

Best Answer

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.

Related Question