Re: ROLLBACK_SEGMENTS definition

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:i1LW8.31610$Hj3.95626_at_newsfeeds.bigpond.com...
> Thank goodness for 9i !!

What, you mean the version of 9i that has a known bug with automatic UNDO, and therefore Metalink advises that the current workaround is to use manual rollback segments??????

Oh... *that* 9i!!

Regards
HJR
>
> (Still think your wrong, so there :)
>
> Richard
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:GY3W8.23$N%4.127_at_news.oracle.com...
> >
> > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > news:E36V8.27768$Hj3.85533_at_newsfeeds.bigpond.com...
> > >
> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > news:ag2nt6$90n$1_at_lust.ihug.co.nz...
> > > >
> > > > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > > > news:S55V8.27742$Hj3.85775_at_newsfeeds.bigpond.com...
> > > > > Hi Howard,
> > > > >
> > > > > Oh thank goodness, we disagree on something (that sound you hear
is
> me
> > > > > jumping around the room whilst ripping up my airline ticket to
> > Tasmania
> > > :)
> > > > >
> > > > > Public vs. Private Rollback Segments
> > > > >
> > > > > If I have 8 rollback segments I want to put online (because that's
> > what
> > > > > tuning the system has ultimately determined is most appropriate),
> what
> > > are
> > > > > my options ? List them in the ROLLBACK_SEGMENTS parameter (note
only
> > the
> > > > one
> > > > > Hrishikesh :) which lets face it is not too hard, or use a bit of
> > > > > mathematics (admittedly not most strongest point) and set the
> > > TRANSACTIONS
> > > > > and TRANSACTIONS_PER_ROLLBACK_SEGMENT appropriately.
> > > > >
> > > > > At this point, not a huge amount of difference I admit.
> > > > >
> > > > > But what if I specifically want my huge rollback segment online
that
> > my
> > > > > large transactions have need with the SET TRANSACTION USE command.
> How
> > > can
> > > > I
> > > > > guarantee that it's one of the selected public rollback segments
> that
> > go
> > > > > online with your suggestion. You can't.
> > > > >
> > > >
> > > > You *can* mix public and private segments, you know ;-) If you've
got
> > this
> > > > sort of mixed-load requirement, then get Oracle to bring the 'oltp'
> ones
> > > > online automatically, and bring the special humungous ones online
> > > > yourself/via the init.ora.
> > > >
> > >
> > > Yes you can but that further complicates the issue. Now you have to
put
> > some
> > > rollback segments in one parameter and modify your calculations in the
> > other
> > > two (and remember my maths is not that good).
> >
> >
> > Not really. If this is a mixed load, the bulk of your (public) rollback
> > segments will come online automatically -and remember you don't need to
be
> > good at maths. Just stick transactions incredibly high and
> > transactions_per_rollback_segment very low (1 springs to mind as a
> suitable
> > value). That forces all the segments you've created online, without too
> much
> > fuss. If there are insufficient public rollback segments, it makes no
> > difference: the ones you;ve got are brought online anyway, and no errors
> are
> > generated.
> >
> > That leaves rollback_segments. The whole point of this is that it's
mixed
> > load -so you have one or two extremely large segments for bulk work.
It's
> > not going to be a killer to list one or two special segments in that
> > parameter.
> >
> > This isn't very hard work.
> >
> > >
> > > > > What if I have a number of tablespaces for my rollback segments
> > (perhaps
> > > > for
> > > > > the above reason, perhaps for others) and I want a number from a
> > > > particular
> > > > > tablespace or none from another etc. to go online, how can I
control
> > > this
> > > > > with public. You can't.
> > > > >
> > > >
> > > > Then sort your tablespaces out! You should have made one rollback
> > segment
> > > > tablespace comprised of a number of datafiles, spread around the
> place,
> > so
> > > > that the I/O is evenly distributed no matter which ones Oracle wants
> to
> > > > bring online.
> > >
> > > True. But during maintenance operations on rollback segments, I may
> > > specifically not want rollback segments to be use which I can't easily
> > > control through public.
> > >
> >
> > Nope. Don't understand a word of that. You can online and offline public
> > rollback segments, which have just as friendly names as private ones.
You
> > lose no management control over them for maintenance operations, just
> > because they are public. The only difference is, you don't have to worry
> > about naming each and every one of them before they are brought online
at
> > startup.
> >
> > > >
> > > > > Public rollback segments in my way of thinking only really make
> sense
> > in
> > > > an
> > > > > OPS environment, but even then I would prefer the manageability
> > > available
> > > > > with private.
> > > > >
> > > >
> > > > I think they make a huge amount of sense in any 'standard'
> environment.
> > As
> > > > soon as you've got peculiar requirements, then yes, you need extra
> > > > management control. The beauty is that you can have both, as needed.
> > >
> > > I'm a control freak and I really like to know which rollback segments
> > (often
> > > all of them, that's why I created them ) are actually online.
> >
> > All of 'em. If you created 100 of them, bring them all online. Might as
> > well; they take up disk space just the same. The only difference is, you
> > have to name each of the 100, I don't.
> >
> > >Using the
> > > ROLLBACK_SEGMENTS parameter is the easiest and most flexible way to do
> > this.
> >
> > Er, I would have thought v$rollstat might have been more flexible, just
as
> > informative, and hardly any more difficult than opening up an init.ora.
> >
> > > My database environment has increased in usage, another rollback
segment
> > is
> > > warranted, I create it, I put it online, I update the
ROLLBACK_SEGMENTS
> > > parameter, I stop worry about it. Nice and simple (just like me).
> > >
> >
> > My database environment has increased in usage, another rollback segment
> is
> > warranted, I create it as a public segment, I stop worrying about it.
Even
> > simpler (just like me).
> >
> > Regards
> > HJR
> >
> >
> >
> > > >
> > > > > Not a flame (take the jacket off), just a slight scorch with my
> > > cigarette
> > > > > lighter :)
> > > > >
> > > >
> > > > You don't smoke! Owwww!!!
> > >
> > > True, but I need it to light the barbie on these coolish evenings in
> > > (otherwise sunny) Canberra. Oh, come on it didn't hurt that much :)
> > >
> > > Cheers
> > >
> > > Richard
> > >
> > > >
> > > > Regards
> > > > HJR
> > > >
> > > > > Regards
> > > > >
> > > > > Richard
> > > > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > > > news:ag2g4q$tht$1_at_lust.ihug.co.nz...
> > > > > >
> > > > > > "Hrishikesh Mehendale" <hrishikesh_mehendale_at_persistent.co.in>
> wrote
> > > in
> > > > > > message news:ag1eaf$idt$1_at_news.vsnl.net.in...
> > > > > > > Hi,
> > > > > > >
> > > > > > > I'm having a weird (?) problem, as follows:
> > > > > > >
> > > > > > > In my init<sid>.ora file, I have specified the following
lines:
> > > > > > >
> > > > > > > ROLLBACK_SEGMENTS = RBS1
> > > > > > > ROLLBACK_SEGMENTS = RBS2
> > > > > > >
> > > > > > > When I shutdown/startup the database, both (RBS1, RBS2) come
> > online.
> > > > > > >
> > > > > > > However, if the lines are changed to either of
> > > > > > >
> > > > > > > rollback_segments = RBS1
> > > > > > > ROLLBACK_SEGMENTS = RBS2
> > > > > > >
> > > > > > > ------ or -------
> > > > > > >
> > > > > > > ROLLBACK_SEGMENTS = RBS1
> > > > > > > rollback_segments = RBS2
> > > > > > >
> > > > > > > only the second segment (RBS2) comes online (case sensitive
??)
> > > > > > >
> > > > > >
> > > > > > Shouldn't be. Sure there isn't another line (even a blank one)
> > between
> > > > > them?
> > > > > >
> > > > > > If there isn't, then I suppose you ought to get the 'discoverer
of
> > the
> > > > > > obscure bug of the month' award. But it's not really Oracle's
> > fault...
> > > > the
> > > > > > standard advice is that if one parameter is repeated elsewhere
in
> > the
> > > > > > init.ora, then the second version of it prevails. So in fact,
> what's
> > > > wrong
> > > > > > is your *first* example where you appear to have two versions of
> > > > > > ROLLBACK_SEGEMENTS and they are *both* respected.
> > > > > >
> > > > > > In other words, the standard way of referencing multiple
rollback
> > > > segments
> > > > > > is "rollback_segments=(X,Y,Z)", on one line.
> > > > > >
> > > > > > Frankly (and I don my flame-prrof suit here), I've given up
> worrying
> > > > about
> > > > > > all the rollback segment nonsense (and I'm not talking about
9i's
> > > > > automatic
> > > > > > undo, either). I just stick the word 'public' into every 'create
> > > > rollback
> > > > > > segment' statement, and watch them bring themselves online at
> > startup
> > > > > time,
> > > > > > without referencing them in the init.ora at all:
> > > > > >
> > > > > > create public rollback segment R01 tablespace RBS;
> > > > > >
> > > > > > I've found no difference between a public and a private rollback
> > > segment
> > > > > in
> > > > > > day-to-day usage, except the convenience of being brought online
> > > > > > automatically. The only thing to watch is setting the
TRANSACTIONS
> > and
> > > > > > TRANSACTIONS_PER_ROLLBACK_SEGMENT init.ora parameters, which
> affect
> > > how
> > > > > many
> > > > > > of the blighters get brought online at startup. But they are
> single
> > > > > paramete
> > > > > > rs, taking simple arguments each. So it shouldn't be too hard to
> > > script
> > > > > > something to adjust them.
> > > > > >
> > > > > > Regards
> > > > > > HJR
> > > > > >
> > > > > >
> > > > > >
> > > > > > > However, doing this:
> > > > > > >
> > > > > > > rollback_segments = RBS1
> > > > > > > rollback_segments = RBS2
> > > > > > >
> > > > > > > brings both segments online.
> > > > > > >
> > > > > > >
> > > > > > > I'm facing this problem on Oracle 8.1.7 EE for Solaris
> (8.1.7.1.0)
> > > and
> > > > > for
> > > > > > > Linux (8.1.7.0.1).
> > > > > > >
> > > > > > > Any ideas on (a) what I'm doing wrong (b) how to solve this?
> > > > > > > My problem is I have to add extra rollback segment(s) to a
> > possibly
> > > > > > > existing declaration in the init<sid>.ora file through a
script.
> > > > > > >
> > > > > > >
> > > > > > > Thanks
> > > > > > > Hrishikesh
> > > > > > >
> > > > > > > --
> > > > > > > Hrishikesh Mehendale
> > > > > > > Member of Technical Staff
> > > > > > > Persistent Systems, Pune.
> > > > > > > http://www.persistent.co.in/
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Tue Jul 09 2002 - 20:15:34 CDT