Re: Rollback/Undo in 9i

Can I suggest just a *little* caution here. I've just finished working at a site where we had probably several hundred databases using system managed undo, aum, whatever you want to call it. In the main, it worked fine as mentioned by others. However, on one of our most critical, biggest (> 4Tb) dbs, we ran into a month's worth of real pain when both our undo$ table and some undo blocks became corrupted. We then had ongoing problems with sessions dieing with ORA-00600 errors related to the initial problems. The process of trying to fix all this went on and on and more than once we were told to make sure that we were on the latest release of 9.2 to avoid some bugs in smu.

The problems were finally solved with a combination on setting an event to stop offlining and onlining of undo segments and a clean-up of undo$. So, I'm definitely not saying don't use this feature, just that it caused me a ton of pain for a few weeks. I suspect the high throughput of this particular db was a factor too.

I wish I could give you more details on the TARs, but I'm a contractor and I finished at that site a week or two ago, but here's a section I kept for my reference :-

"However, BDE asked me to check whether offlining and onlining of undo segments was happening. I looked at the alert.log and noticed that undo segments are being created, online, offlined and dropped almost every day. This is what SMON does when you use AUM depending on the load. However, this offlining and onlining of undo segments could "sometimes" cause undo segment corruption. So please set event 10511 when you bounce the database next time.

This event doesn't prevent space management of undo segments, but it keeps
them from being offlined. This event is set in the parameter file for the database:

event="10511 trace name context forever""

Please don't take this as a recommendation to set this event, I'm just trying to illustrate that on that particular db, we ran into a bug that "could "sometimes" cause undo segment corruption". Even then, Oracle's BDE engineer said this was a theory because they couldn't recreate the problem at Oracle. However, setting this event did stop the instance from crashing, so it was a big relief!

Remember I also said that this happened on one database out of many. Just happened to be one of out most critical :-(

Hope this adds something to your thoughts.

Cheers,

Doug

DW wrote:
> Greetings...
>
> I have a number of instances running under 9i (9.2.0.4, about to
patch
> to 9.2.0.6).
>
> My organization wants to investigate using undo instead of the
rollback
> segments that we currently use.
>
> I'm looking for a discussion about 'migrating' to undo versus
rollback:
> recommended process, gotchas, and backing out afterwards.
>
> Are there any articles anyone can think of which discuss this?
>
> Thanks!
>
> DW
Received on Mon Apr 11 2005 - 03:42:54 CDT