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: Question about Undo segments

Re: Question about Undo segments

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: 28 Apr 2002 11:15:52 -0700
Message-ID: <aahe8o02qhn@drn.newsguy.com> 





In article <c42168e7.0204280827.47e90793_at_posting.google.com>,
irfan_pk_at_hotmail.com says...


>

>"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message

>news:<aafd03$9ki$1_at_lust.ihug.co.nz>...

>> Undo segments are just rollback segments with a new name. Therefore, what

>> was true for rollback segments remains true for undo segments.

>

>But I have seen there are both RBS and Undo Tablespaces in 9i. Which one is 

>used for rollback. 

>Is data written in both the tablespaces?




Must admit I haven't tried it with both the ROLLBACK_SEGMENTS parameter and the
UNDO_TABLESPACE parameter in the init.ora file, so let's test it and see what
happens when you try to start the database in that way:





NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
spfile                               string      %ORACLE_HOME%\DATABASE\SPFIL
                                                 ORACLE_SID%.ORA





Had a look in the database directory and yes there's an spfile there, so I'm
using an spfile.  Next check the undo parameters:



SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1





So I'm using automatic undo management with an undo tablespace.  What are the
rollback parameters set to?



SQL> show parameter rollb


NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
fast_start_parallel_rollback         string      LOW
max_rollback_segments                integer     37
rollback_segments                    string
transactions_per_rollback_segment    integer     5





OK, so no rollback_segment parameter defined.  Do I have an RBS tablespace?



SQL> select tablespace_name from dba_tablespaces;



TABLESPACE_NAME





SYSTEM



UNDOTBS1



TEMP



DRSYS



INDX



ODM



TOOLS



USERS



XDB



OEM_REPOSITORY


10 rows selected.



No, so let's create our own RBS tablespace for this test:



SQL> create tablespace rbs datafile 'd:\oracle\oradata\ora92\rbs01.dbf' size




Tablespace created.



No create a rollback segment there:



SQL> create rollback segment rbs01 tablespace rbs;
create rollback segment rbs01 tablespace rbs
*


ERROR at line 1:


ORA-30019: Illegal rollback Segment operation in Automatic Undo mode



Ahah! Not allowed to do that.  So let's turn off AUM (automatic undo
management):



SQL> alter system set undo_tablespace='';



System altered.



SQL> alter system set undo_management=manual;
alter system set undo_management=manual

                 *




ERROR at line 1:


ORA-02095: specified initialization parameter cannot be modified



Bummer, not a dynamic parameter, need to change the spfile and restart (note I'm
using startup force 'cause I'm lazy and don't want to type two commands.  Not
recommended for general use):



SQL> alter system set undo_management=manual scope=spfile;



System altered.



SQL> startup force


ORACLE instance started.



Total System Global Area  143727476 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes


Database mounted.


Database opened.



Now try to create a rollback segment:



SQL> create rollback segment rbs01 tablespace rbs;
create rollback segment rbs01 tablespace rbs
*


ERROR at line 1:


ORA-01552: cannot use system rollback segment for non-system tablespace 'RBS'



Oh no, not that old pain in the posterior!  Let's create a dummy rollback
segment:



SQL> create rollback segment dummy;



Rollback segment created.



Now try creating the rollback segment in the RBS tablespace:



SQL> create rollback segment rbs01 tablespace rbs;
create rollback segment rbs01 tablespace rbs
*


ERROR at line 1:


ORA-01552: cannot use system rollback segment for non-system tablespace 'RBS'



Oops still not using dummy.  Let's fix that first:



SQL> alter system set rollback_segments=dummy scope=spfile;



System altered.



SQL> startup force


ORACLE instance started.



Total System Global Area  143727476 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes


Database mounted.


Database opened.



Now swap to using our RBS01 rollback segment:



SQL> alter system set rollback_segments=rbs01 scope=spfile;



System altered.



SQL> startup force


ORACLE instance started.



Total System Global Area  143727476 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes


Database mounted.


ORA-01092: ORACLE instance terminated. Disconnection forced



Duh!  I didn't succeed with creating it earlier.  Swap back to using dummy and
create the RBS01 rollback segment.



SQL> alter system set rollback_segments=dummy scope=spfile;
alter system set rollback_segments=dummy scope=spfile
*


ERROR at line 1:


ORA-03114: not connected to ORACLE



Hmm. not connected since disconnect was forced above.  Reconnect:
SQL> connect / as sysdba


Connected to an idle instance.



Dang!  Idle instance so I can't use the alter system command.  But I can get
around this by creating a PFILE from the SPFILE and editing that:



SQL> create pfile from spfile;



File created.



Break out to another window and edit the pfile judiciously (set the
*.rollback_segments=dummy parameter).  Now I need to recreate the spfile so I
can use that



SQL> create spfile from pfile;



File created.



SQL> startup force


ORACLE instance started.



Total System Global Area  143727476 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes


Database mounted.


Database opened.



Now create the RBS01 rollback segment in the RBS tablespace:



SQL> create rollback segment rbs01 tablespace rbs;



Rollback segment created.



OK, now reset the rollback_segments parameter to use my RBS01 rollback segment
and restart the database:



SQL> alter system set rollback_segments=rbs01 scope=spfile;



System altered.



SQL> startup force


ORACLE instance started.



Total System Global Area  143727476 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes


Database mounted.


Database opened.



WHat are our parameters eet to now?



SQL> show parameter rollback


NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
fast_start_parallel_rollback         string      LOW
max_rollback_segments                integer     37
rollback_segments                    string      RBS01
transactions_per_rollback_segment    integer     5


SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string





So we're in manual undo management mode using the RBS01 rollback segment.  Let's
see what happens when we change undo_management to AUTO but DON'T set the undo
tablespace:



SQL> alter system set undo_management=auto;
alter system set undo_management=auto

                 *




ERROR at line 1:


ORA-02095: specified initialization parameter cannot be modified



Dang!  Another parameter that's not dynamic.  Reset it and restart the database:



SQL> alter system set undo_management=auto scope=spfile;



System altered.



SQL> startup force


ORACLE instance started.



Total System Global Area  143727476 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes


Database mounted.


Database opened.



Now what are our parameters set to?



SQL> show parameter rollback


NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
fast_start_parallel_rollback         string      LOW
max_rollback_segments                integer     37
rollback_segments                    string      RBS01
transactions_per_rollback_segment    integer     5


SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1





Ahah!  We still have rollback_segments set to rbs01, but because we set
UNDO_MANAGEMENT to AUTO, Oracle searched for an undo tablespace and found it. 
Hence the setting for UNDO_TABLESPACE.  So what are we using?  Undo segments or
rollback segments?  Check V$ROLLNAME:



SQL> select name from v$rollname;



NAME





SYSTEM


_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$





11 rows selected.



So there's your answer.  Oracle ignores the ROLLBACK_SEGMENTS parameter if the
UNDO_MANAGEMENT parameter is set to AUTO.



SQL>



>

>> 

>> Therefore, it's your server process that modifies undo buffers. They get

>> flushed to disk as dirty buffers whenever DBWn takes it into its head to do

>> any osrt of disk writing. The redo for them is the same as it is for any

>> modification to any buffer: the before and after image.

>> 

>> In short, there's nothing magic about them -and if you know how rollback

>> blocks are dealt with, that's how undo blocks are managed, too.

>> 

>> Regards

>> HJR


>> 

>> 

>> "Jargular" <zclai_at_yahoo.com> wrote in message

>> news:8ae4ae9e.0204270719.22c0f769_at_posting.google.com...

>> > Hi,

>> >

>> > Does anybody know what is the process that writes to the Undo

>> > segments?  under what condition the Undo buffers are flushed into Undo

>> > segments? how are the changes in Undo buffers recorded in the online

>> > Redologs? I seached Oracle9i documentation, it seems that they are not

>> > detailed in the docs. Anyone can help?

>> >

>> > really appreciated.

>> >

>> > Jargular

>> > zclai_at_yahoo.com




HTH.  Additions and corrections welcome.



Pete



SELECT standard_disclaimer, witty_remark FROM company_requirements;
Received on Sun Apr 28 2002 - 13:15:52 CDT












Original text of this message













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