Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Understanding Rollback Segment Sizes

Re: Understanding Rollback Segment Sizes

From: steven_nospam at Yahoo! Canada <steven_nospam_at_yahoo.ca>
Date: 18 Jan 2007 08:31:14 -0800
Message-ID: <1169137874.106270.180870@l53g2000cwa.googlegroups.com>

MTNorman wrote:
> Hi Steve, I think others have correctly identified better alternative
> choices, so I'm going to take a shot at answering the original
> questions.
>
> 1) The second datafile being at 500m instead ot 1000m is probably an
> error - assuming you are using some type of reverse engineering tool.
> Most likely the files were all 500m at some point and 3 out of 4 have
> been manually extended to 1000m. They could be all the same size or
> you could include the space in one datafile in a locally managed
> tablespace (LMT). The four data files could have orignally spread the
> IO load across multiple disks, were needed because of an OS max file
> size limitation, or made use of multiple freelists for the tablespace.
> Since you are creating the four files in the same file system, they are
> not spreading the IO load and LMT avoids freelist issues and your OS
> probaly doesn't have a 2G max file size limit anymore.
>
> 2&3) You are correct that with the current settings the rollback
> segment will only reach 500m each or 2000m total. Remember when you
> are looking at the usage you are only seeing one moment in time. The
> optimial setting means even if the rollback segments had consumed 58%
> of the space a couple of hours ago, after skrinking back to the
> "optimal" size, only 30% is being used now. Not having enough space
> causes query and process failures. Unless you are running out of
> storage, you are probably better off not to reducing the available RBS
> resources. If you have the tablespace size equal to the _current_
> total maximum rollback size, then changing any rollback setting or
> adding an additional rollback segment requires you remembering to
> adjust the tablespace. If you don't, then on some later day...
> problems.
>
> Changing the RBS setup, whether moving from manual RBS to automatic
> UNDO or reducing storage resources, introduces an "untested"
> configuration change. If you don't feel you can move to UNDO at this
> point in your project, you probably shouldn't be considering reducing
> the RBS tablespace size either.
>
> Regards,
> Margaret

Thanks much Margaret,

At least I was understanding the older RBS setups right. Essentially with the current setup, my maximum RBS size for each was 500MB or 2GB total. As you mentioned, if I add more later, I would also need to increase the file system size. But this is true of ALL our filesystems...We have to increase them whenever we add an additional data file for the DATA tablespace.

We are on an AIX 5 system and I believe there is a limitation of 2GB file sizes, although we used to also have a limitation on file system sizes (eliminated with enhanced file systems - jfs2). I think the original plan was to spread the load across multiple disks and put one RBS on each spindle, but since we have them all in one FS, we really dont have control at that level unless we separate them out.

My thought was to change the three 1GB and one 500MB data files in the RBS to use only two 2GB data files, and adjust the maxextents so I would make more use of the space that was reserved.

However, I just received word down from management that I can go ahead and use the Undo tablespace method as long as I document it, updating our install procedures, and we will see how things go with the test phase of this install. (Essentially, I will be able to test because the migration from 8i to 9i on this new server requires a 2-4 week testing phase before the actual LIVE migration.)

Thx to all for the help again.

Steve Received on Thu Jan 18 2007 - 10:31:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US