RE: Undo Tablespace question

Paul,

The transition from RBS to UNDO is pretty easy

I would suggest retaining the same size UNDO as you have RBS at the = moment, especially if you have no special (longer) retention period = requirements.
The init.ora (or hopefully spfile) should be amended to remove the = following lines
TRANSACTIONS_PER_ROLLBACK_SEGMENT and ROLLBACK_SEGMENTS.=20

The following parameters should be added UNDO_MANAGEMENT=3DAUTO
UNDO_SUPRESS_ERROR=3DTRUE (to stop errors occuring if the commands = referring to rollback segments are used (online/offline/use etc) UNDO_TABLESPACE (multiple UNDO tablespace can be created (as normal T/S = - use LMT) but only 1 can be 'active'

Drop the RBS tablespace once all sessions using it have been removed (it = can always be re-created if necessary)

Experiment with the flashback_query facility, you might not need it but = it is a pretty neat thing and well worth playing with.

The main view to use for monitoring information is V$UNDOSTAT where you = can determine the maximum query length and the number of undo blocks = used amongst other things.

In my view this is one of the easiest of the 9i features to implement = and you should not have many problems

HTH=20 John

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Paul Vincent Sent: Friday, September 17, 2004 9:16 AM To: oracle-l_at_freelists.org
Subject: Undo Tablespace question

Now that we've finally got some 9i databases, I'm quite keen to switch =
=3D

from Manual to Automatic Undo Management. It all looks pretty =3D straightforward, but I do have a couple of questions that the "How To" =
=3D

guides don't really address...

1. If I don't have any requirement for long-term retention of Undo data =
   =3D
   after transactions commit, so that the default retention of 900 seconds =
   =3D
   is quite sufficient, then what multiplier should I use when calculating =
   =3D
   the size of my Undo Tablespace relative to the size of the old RBS =3D tablespace? Say I currently have 500Mb RBS tablespace, then should a =3D 500Mb undo tablespace be adequate, or should I allow double (1Gb) or =3D more?
2. What monitoring practices are advisable to check the adequacy of the =
   =3D
   undo tablespace provision? Can anyone point me at any webpages with =3D articles about monitoring AUM activity, for instance?
3. Once I've switched from Manual to Auto, does the RBS tablespace then =
   =3D
   become completely superfluous? Can it be dropped? And what of the =3D "rollback_segments" initialisation parameter? Do I remove this from the =
   =3D
   parameter file, or is it best left there "just in case"?

Before I go diving in, in cavalier manner, I always like to check for =
=3D

"gotchas"!

Regards,

Paul Vincent
DBA
University of Central England

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l