Re: ROLLBACK_SEGMENTS definition

Hi Howard,

I thought the bug only affected Release 2 ?

Can you please forward me the details, access to metalink in not that easy for me at the moment :(

Thanks

Richard
"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:agg1ql$2cf$1_at_lust.ihug.co.nz...
>
> "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 - 21:04:56 CDT