Re: Autoextend for rollback

Yes, it is beautiful to see two points of view -- logically presented without a lot of emotion!

How would you guys modify your views if you were using raw devices? Is it even possible to allocate just part of a raw device and then use autoextend?

What if you were using RMAN for backups? RMAN does not backup unused space. What IS unused space for this purpose in the rollback and temp tablespaces?

What if the RMAN backups used Veritas NetBackup over TCP/IP (100MBS ethernet) to shared tape drives?

Pete Sharman wrote:

> Don't you just love it when two people say the exact opposite of each other!
>
> Tom's approach allows more freedom, but still controls the maximum size a tablespace
> can get to. It works if you're away frequently like Tom sounds to be. In my days
> as a DBA, though, I was away infrequently and liked to keep closer control. It's
> horses for courses I suppose.
>
> In some ways, the way I set things up was to do enable two things:
>
> 1. Stopping the rogue processes as I mentioned earlier
> 2. Managing space utilization more tightly. Unlike Tom, I didn't have that much
> disk space to play around with, and there were often multiple databases sharing
> disks. Not an ideal situation, but one that I had no choice over.
>
> Have you run into any issues where rogue processes chew up lots of space, run out of
> space and then chew up lots more resources freeing up that space? For example,
> undoing a long running transaction that runs out of rollback segment space.
>
> Pete
>
> Thomas Kyte wrote:
>
> > On Fri, 18 Jun 1999 09:55:37 -0700, you wrote:
> >
> > >Don't know whether this counts as thoughts, dark corners, nooks and crannies
> > >or conventional wisdom, but I've always advocated not using auto extend,
> > >particularly for rollback segment tablespaces and temporary tablespaces.
> > >The reasoning is pretty obvious. Rogue processes can really blow things
> > >away if you do use it. At least without autoextend they only fill the
> > >tablespace.
> > >
> > >HTH.
> > >
> > >Pete
> > >
> >
> > Just to muddy the waters a bit.
> >
> > I love to use autoextend on temp and rollback. Love it.
> >
> > I have my temp on a disk that could go upto 2gig. I don't want to back up 2gig.
> > I created temp at 25meg, autoextend on, next 25meg maxsize 2000meg. If I didn't
> > have autoextend -- i would have preallocated 2gig to temp (and i would back it
> > up). Now, I preallocate 25meg, let it grow as needed upto 2gig and before I
> > backup -- i resize it back down (the file, i physically make the file smaller).
> >
> > Same with rollback. I use the autoextend sort of like 'optimal'. My optimal
> > size for rollback is 25meg. I put each rollback into its own tablespace and
> > each tablespace has its own datafile. I let the next on the the autoextend
> > match the next on the rollback segment. I maxed out the rollback segments as
> > some logical maxsize (100meg on my particular system). Before I backup, i go
> > around and drop each rollback segment that has actually grown and resize the
> > datafile and recreate the rollback segment to fill the datafile to 25meg again.
> > I backup less data.
> >
> > I always use MAXSIZE on the datafiles to prevent rogue processes from filling up
> > everything.
> >
> > The main benefit (to me, a frequently absent dba due to travel) is that if
> > someone has a large transaction -- we can usually satisfy it by letting their
> > rollback segment grow on disk. They don't have to pick a rollback segment big
> > enough for them -- all of them have the POTENTIAL to grow (as long as we don't
> > run out of disk space). They don't have to find a rollback segment in a
> > tablespace with enough room to grow rather, any rollback segment potentially has
> > the ability to grow to some maxsize. If they exceed maxsize -- they don't get
> > to finish (no rogues)... I don't have enough space for all of my rollbacks to
> > get to maxsize but I have enough space for many of them too. this lets them go
> > that size as needed. I just periodically run a script (dbms_job would be great
> > for this) to resize them back down...
> >
> > There is a flip side to every coin I guess...
> >
> > >Kenneth C Stahl wrote:
> > >
> > >> Has anyone ever used auto extend for tablespaces that only contain
> > >> rollback segments?
> > >>
> > >> For example, let's say I create a tablespace called RBS which will only
> > >> hold rollback segments and give it a size of 15M. I then create ten
> > >> rollback segments with INITIAL 100K NEXT 100K OPTIMAL 200K MINEXTENTS 2
> > >> MAXEXTENTS 249. Quite obviously, the first time that I have a
> > >> transaction which causes a rollback segment to grow towards 249 extents
> > >> I'll get the "snapshot too old" error message and my transaction will
> > >> rollback. However, if I give the tablespace the ability to auto extend
> > >> then the tablespace will grow as needed. I realize the risk here is that
> > >> if I have multiple transactions that are all causing the rollback
> > >> segments to grow to many extents that I'll end up with a collossal
> > >> tablespace but that is something I could always monitor and adjust as
> > >> needed - but at least I wouldn't be beeped in the middle of the night
> > >> when batch processing is going on and some job aborts because I ran out
> > >> of space in the RBS tablespace.
> > >>
> > >> Thoughts? Dark corners? Nooks and crannies? Convential wisdom?
> > >>
> > >> Ken
> >
> > --
> > See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> > Current article is "Fine Grained Access Control", added June 8'th
> >
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries Reston, VA USA
> >
> > Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> --
>
> Regards
>
> Pete
Received on Fri Jun 18 1999 - 22:18:42 CDT