Re: ROLLBACK_SEGMENTS definition

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:DwMW8.31665$Hj3.95560_at_newsfeeds.bigpond.com...
> Hi Howard,
>
> I thought the bug only affected Release 2 ?

That's my understanding too.

Guess which version I'm on????? ;-)

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

Not really, Richard.... I'm ensconced in the study right now, and the details of how I get into Metlink are written on a sticky bit of yellow paper attached to my monitor at work.

Oops.

Perhaps some kind soul will oblige us both?

Regards
HJR
>
> 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:19:32 CDT