RE: Undo Tablespace question
- From: "Ron Rogers" <RROGERS@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <John.Hallas@xxxxxxxxxxxxxxxxx>
- Date: Fri, 17 Sep 2004 08:28:43 -0400
Paul,
In addition to what John supplied, be cautious of setting the
RETENTION period. If the time
is set to long and you perform a major data load the UND_TBS will grow
as needed. Once it has
grown the only method I know of to shrink it is to create another
UNDO_TBS1 and change the
parameter in the init/spfile to point to the new tablespace.
Ron
>>> "Hallas, John, Tech Dev" <John.Hallas@xxxxxxxxxxxxxxxxx> 09/17/2004
5:17:39 AM >>>
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Paul Vincent
Sent: Friday, September 17, 2004 9:16 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
