Re: spfile

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 06 Nov 2004 10:43:03 +1100
Message-Id: <418c0fe9$0$32599$afc38c87@news.optusnet.com.au>

Mark D Powell wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:<418ad185$0$24942$afc38c87_at_news.optusnet.com.au>...

>> "Oradba Linux" <techiey2k3_at_comcast.net> wrote in message
>> news:1bAid.46596$HA.33011_at_attbi_s01...
>> > Using spfile and old fashioned rollback segments, once a bunch of new
>> > rollback segments are added how do you update the spfile
>> > so that they are online next time you recycle the database
>> 
>> 
>> It ought to be something like
>> 
>> alter system set rollback_segments='RBS01, RBS02, RBS03, RBS04'
>> scope=spfile;
>> 
>> ...ie, just a comma-separated list of segments.
>> 
>> But it is so long since I used rollback segments rather than undo, I
>> don't actually have a system I can test that on easily. So it might not
>> be that at all!
>> 
>> Just curious: why wouldn't you want to use automatic undo?
>> 
>> Regards
>> HJR

>
> Here are some reasons why some sites may not have switched to using
> undo tablespaces instead of manually managed RBS segments:
>
> 1- some sites have complained about 1555 errors under auto undo
> management that did not occur under their manual RBS management.
> There are apparently times where automatic undo gets very aggressive
> with releasing segments.

Why not increase UNDO_RETENTION, then?

> 2- under heavy load some sites have complained that new undo segments
> are allocated consuming the undo tablespace when unused segments were
> available and should have been reused.

That's what happens when you over-size your undo tablespace, of course.  

> 3- You run RAC and do not trust that Oracle has all the kinks worked
> out of the feature in version 9 or 9.2

You run RAC, but don't trust automatic undo???!!!?

Could I suggest such sites learn about risk management and/or prioritisation?

> And the most likely reason sites are not using the feature
> 4 - traditional RBS management is working well so the DBA has not got
> around to making the change because there is not real way to test it
> without actually using it in production due to load differences
> between test and production.

Grudgingly grant that one. But our OP has obviously not been too afraid to implement the spfile... and I remember the init.ora always "worked well".

:-)

> 5- If the DBA is willing to make the change to production, IT
> management does not want to take what they see as an unnecessary risk.
> In the past new features often brought new bugs with them and
> managment does not forget being burned.

I think my point is that as far as anything can be, automatic undo has (by 9i Release 2... I willingly admit things were different in release 1) had most of its quirks shaken out. I don't keep an eagle-eye on Metalink, so I could be wrong. But sticking with manual rollback has the same feel about it to me as the die-hard use of dictionary-managed tablespace.

Regards
HJR Received on Fri Nov 05 2004 - 17:43:03 CST