SEO

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

"DW" <dwhittier@shaw.ca> wrote in message
news:1112741138.994413.28340@g14g2000cwa.googlegro ups.com...
> 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
>

create an undo tablespace same size as your rollback tablespace - turn undo
on and voila - never look back. It works and works well, no gotchas (i hate
that word) and no need to back out

You might want to look at the docs for flashback, you may want to
increase the size to have a reasonable keep time.

jg
--
@home.com is bogus.
Stop reading this post.

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

Moving to UNDO is a no-brainer. Not once in the last 1555 or so days of
working with UNDO have I ever considered going back.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

We use a 72-hour undo retention time. This allows for reasonable time
to do a flashback query if necessary. Like Daniel said, it's a
no-brainer. Just having flashback alone is worth it to me. It saved
me a lot of work one day when a bug in one of our apps cause a "DELETE
FROM xxx" to be executed on one of our tables. Being able to flashback
the data to another table (clone of original) and then grab what I
needed was a lot faster than having to restore the table, etc.

Jesse

Hm... got the tablespace made, got the AUM set to Auto - would you
recommend eliminating the rollback tablespaces altogether?

DW

"DW" <dwhittier@shaw.ca> wrote in message
news:1112813654.675043.140180@z14g2000cwz.googlegr oups.com...
> Hm... got the tablespace made, got the AUM set to Auto - would you
> recommend eliminating the rollback tablespaces altogether?
>
> DW
>

don't need them, delete it get your space back

DW wrote:

> Hm... got the tablespace made, got the AUM set to Auto - would you
> recommend eliminating the rollback tablespaces altogether?
>
> DW

Yes with one exception. Do not remove the SYSTEM segent in
the SYSTEM tablespace.

Even in 10g with UNDO one should see something like this:
SQL> select segment_name, tablespace_name
2 from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ---------------
SYSTEM SYSTEM
_SYSSMU1$ UNDOTBS1
_SYSSMU2$ UNDOTBS1
_SYSSMU3$ UNDOTBS1
_SYSSMU4$ UNDOTBS1
_SYSSMU5$ UNDOTBS1
_SYSSMU6$ UNDOTBS1
_SYSSMU7$ UNDOTBS1
_SYSSMU8$ UNDOTBS1
_SYSSMU9$ UNDOTBS1
_SYSSMU10$ UNDOTBS1

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ---------------
_SYSSMU11$ UNDOTBS1
_SYSSMU12$ UNDOTBS1
_SYSSMU13$ UNDOTBS1
_SYSSMU14$ UNDOTBS1
_SYSSMU15$ UNDOTBS1
_SYSSMU16$ UNDOTBS1
_SYSSMU17$ UNDOTBS1
_SYSSMU18$ UNDOTBS1
_SYSSMU19$ UNDOTBS1
_SYSSMU20$ UNDOTBS1
_SYSSMU21$ UNDOTBS1

SYS still needs its RBS for the data dictionary.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Thanks for the clarification!

DW

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