why undo_management=auto

aman.oracle.dba

unread,

Dec 21, 2006, 6:34:59 AM12/21/06

to

Hello ALL,

according to
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96521/undo.htm#9114
the use of rollback segments for managing undo space will be deprecated
in a future release.
WHY....................
what are the main advantages to keep separate undo_tablespace in
database, when I can make one separate tablespace for rollback segments
also.

Charles Hooper

unread,

Dec 21, 2006, 7:39:14 AM12/21/06

to

Google Search:
http://www.google.com/search?hl=en&q=snapshot+too+old

Documentation:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm

In short, undo offers less management hassle to deal with, when making
certain that the rollback segments (all of them) are sufficiently sized
to handle all possible combinations of transactions in order to reduce
the chances of receiving Snapshot Too Old errors.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

sybrandb

unread,

Dec 21, 2006, 7:51:36 AM12/21/06

to

The main advantage of UNDO segments is they make ongoing rollback
maintenance a thing of the past. They are there to make your work
easier (or more redundant), so you need to ask less ' I didn't read the
docs' questions.

--
Sybrand Bakker
Senior Oracle DBA

DA Morgan

unread,

Dec 21, 2006, 3:30:17 PM12/21/06

to

Not that far into the future ... as of 10g they are gone.

Why?

Partially to put an end to ORA-01555, partially because DBAs have proven
themselves incapable of managing rollback segments, and partially
because the new algorithm improves many aspects of database existance
and makes possible some aspects of flashback technology.

It would seem that Oracle has taken the same attitude toward rollback as
they have taken toward temp ... we can do it better than you can. <g>
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Charles Hooper

unread,

Dec 21, 2006, 4:22:34 PM12/21/06

to

Are you sure that they are gone?

Oracle Database Administrator's Guide 10g Release 2, Page xlv:
"Deprecation of use of rollback segments (manual undo management mode)
Manual undo management mode has been deprecated and is no longer
documented in this book. Use an undo tablespace and automatic undo
management instead.
See Chapter 10, Managing the Undo Tablespace"

Page 10-2
"You set the UNDO_MANAGEMENT initialization parameter to AUTO to enable
automatic
undo management. A default undo tablespace is then created at database
creation. An
undo tablespace can also be created explicitly. The methods of creating
an undo
tablespace are explained in "Creating an Undo Tablespace" on page
10-6."

"UNDO_MANAGEMENT If AUTO, use automatic undo management. The default is
MANUAL."

It appears that in 10g R2, rollback segments are deprecated, no longer
documented in the Administrator's Guide, yet are still the default
based on the default value of UNDO_MANAGEMENT. Did I read something
wrong?

Maxim Demenko

unread,

Dec 21, 2006, 5:19:57 PM12/21/06

to Charles Hooper

Charles Hooper schrieb:

Indeed, even in 10g there may be need sometimes for a rollback segments.
See my comment on the old Laurent Schneider blog -
http://laurentschneider.blogspot.com/2006/08/tablespace-maintenance-tasks.html
Interestlingly, just tested on fresh 10.2.0.3 move hash partition with
clob column - no way with undo management auto (i.e. ORA-22877) , with
rollback segments works like a charm.

Best regards

Maxim

DA Morgan

unread,

Dec 21, 2006, 5:56:12 PM12/21/06

to

Well I know there is still one rollback segment in the system tablespace
(10.2.0.2):

desc dba_rollback_segs

SELECT segment_name, owner, tablespace_name
FROM dba_rollback_segs;

So perhaps I should have been more precise and stated they are
deprecated. But it would seem a DBA heck-bent on making a mess of
things probably still can.

I just went into the docs:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref413
and found an error I just reported. Where what is written is: "By
default, this parameter is set to MANUAL."

Andy Hassall

unread,

Dec 21, 2006, 6:30:15 PM12/21/06

to

On Thu, 21 Dec 2006 14:56:12 -0800, DA Morgan <damo...@psoug.org> wrote:

>I just went into the docs:
>http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref413
>and found an error I just reported. Where what is written is: "By
>default, this parameter is set to MANUAL."

What makes you think this is a documentation error?

--
Andy Hassall :: an...@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

DA Morgan

unread,

Dec 21, 2006, 9:14:06 PM12/21/06

to

Andy Hassall wrote:
> On Thu, 21 Dec 2006 14:56:12 -0800, DA Morgan <damo...@psoug.org> wrote:
>
>> I just went into the docs:
>> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref413
>> and found an error I just reported. Where what is written is: "By
>> default, this parameter is set to MANUAL."
>
> What makes you think this is a documentation error?

Your question got me to thinking and now I'm more confused than ever
(and that takes a bit of doing).

Here's what the Oracle doc says:
"The UNDO_MANAGEMENT initialization parameter determines whether an
instance starts in automatic undo management mode which stores undo in
an undo tablespace. By default, this parameter is set to MANUAL. Set
this parameter to AUTO to enable automatic undo management mode."

This would indicate that undo tablespace is NOT the default and requires
manual intervention.

And this query:

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 21 17:58:50 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> col name format a30
SQL> col value format a30
SQL> SELECT name, value, isdefault
2 FROM gv$parameter
3 WHERE name LIKE '%undo%';

NAME VALUE ISDEFAULT
------------------------------ ------------------------------ ---------
undo_management AUTO FALSE
undo_tablespace UNDOTBS1 FALSE
undo_retention 3600 FALSE

SQL>

Would tend to confirm that AUTO is not the default.

Except that I just built a 10g database and there is no question that
the value defaulted to AUTO and the database has an UNDO tablespace.

And this doc:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams220.htm#sthref897
states:
"In manual undo management mode, undo space is allocated externally as
rollback segments."

Anybody out there running 10gR2 with rollback segments and not undo?
I've certainly never seen it. There must be something I'm missing so,
I hope, someone can complete the picture are resolve the apparent paradox.

DA Morgan

unread,

Dec 21, 2006, 9:16:14 PM12/21/06

to

Andy Hassall wrote:
> On Thu, 21 Dec 2006 14:56:12 -0800, DA Morgan <damo...@psoug.org> wrote:
>
>> I just went into the docs:
>> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref413
>> and found an error I just reported. Where what is written is: "By
>> default, this parameter is set to MANUAL."
>
> What makes you think this is a documentation error?

Well if I was confused before I'm even more so now ...

Metalink Note: 240746.1
Date: 02-OCT-2006

Mandatory setting:
=================
UNDO_MANAGEMENT=AUTO /* Defaults to MANUAL in 10gR2*/

How can the "Mandatory setting" not be the default?

aman.oracle.dba

unread,

Dec 22, 2006, 4:46:30 AM12/22/06

to

I am sorry if I was not able to make you guys to understand what was my
question. Actually my question is like this.
My is my database is fine. There is no ORA-01555 or RBS tablespace is
not small. But what else a dba doesn't have to do if he uses
undo_management=AUTO in daily routine. How it will make my job easior
somewhere ? As it is producation database of bank, flashback has no use
in my case.

Andy Hassall

unread,

Dec 22, 2006, 1:13:22 PM12/22/06

to

On Thu, 21 Dec 2006 18:14:06 -0800, DA Morgan <damo...@psoug.org> wrote:

>Andy Hassall wrote:
>> On Thu, 21 Dec 2006 14:56:12 -0800, DA Morgan <damo...@psoug.org> wrote:
>>
>>> I just went into the docs:
>>> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref413
>>> and found an error I just reported. Where what is written is: "By
>>> default, this parameter is set to MANUAL."
>>
>> What makes you think this is a documentation error?
>
>Your question got me to thinking and now I'm more confused than ever
>(and that takes a bit of doing).
>
>Here's what the Oracle doc says:
>"The UNDO_MANAGEMENT initialization parameter determines whether an
>instance starts in automatic undo management mode which stores undo in
>an undo tablespace. By default, this parameter is set to MANUAL. Set
>this parameter to AUTO to enable automatic undo management mode."
>
>This would indicate that undo tablespace is NOT the default and requires
>manual intervention.
>
>And this query:
>

>SQL> SELECT name, value, isdefault
> 2 FROM gv$parameter
> 3 WHERE name LIKE '%undo%';
>
>NAME VALUE ISDEFAULT
>------------------------------ ------------------------------ ---------
>undo_management AUTO FALSE
>undo_tablespace UNDOTBS1 FALSE
>undo_retention 3600 FALSE
>

>Would tend to confirm that AUTO is not the default.

Yep.

Also try the following; start up the database with the undo_management
parameter missing completely - you'll find it's MANUAL.

>Except that I just built a 10g database and there is no question that
>the value defaulted to AUTO and the database has an UNDO tablespace.

What did you create it with? DBCA defaults to setting it to AUTO, but this is
a non-default value from Oracle's point of view.

DA Morgan

unread,

Dec 22, 2006, 1:24:17 PM12/22/06

to

If you are not spending any time managing rollback, and you are not
using flashback, it will likely change nothing that you do.

It is possible to use 10gR2 just the same way one used 7.3.4 ... but
that doesn't mean you should have stayed at v7.

DA Morgan

unread,

Dec 22, 2006, 1:33:24 PM12/22/06

to

Andy Hassall wrote:

> What did you create it with? DBCA defaults to setting it to AUTO, but this is
> a non-default value from Oracle's point of view.

I hate to say it but I suspect this explanation is most likely.

<22 minutes passes ...>

That's it. The default is only the "default" if you don't use Oracle's
own tool to create the database.

Next time someone challenges me to criticize Oracle about something I
hope this leaps into my mind. I'm getting tired of having to whine about
the Database Team not bringing their default installation in line with
the compliance scores created by the Grid Control. <g>