===========================================================
Which Operations are Allowed or Prohibited on RBS with or without AUM?
===========================================================
Doc ID: Note:135217.1
Subject: Which Operations are Allowed or Prohibited on RBS with or without AUM?
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 08-FEB-2001
Last Revision Date: 07-AUG-2003
PURPOSE
-------
This bulletin shows the errors you would encounter when you attempt to use
operations on rollback segments whether you are in Automatic Undo Management
(AUM) mode or not.
It explains the reasons for the restrictions applied on rollback segment
management.
The error messages always specify which mode the operations takes place in:
* Automatic Undo Management
* Manual Undo Management
SCOPE & APPLICATION
-------------------
For DBAs who are used to managing the rollback segments of a database manually
and who now have to consider the existence of AUM (Automatic Undo Management).
Which operations are allowed or prohibited on rollback segments when you are
using or not using AUM?
Different types of errors
-------------------------
Imagine you have two UNDO tablespaces available in the database, and therefore
a number of system-generated rollback segments.
You also have at least a user-generated rollback segment called SYSTEM and
eventually some other user-generated rollback segments in non UNDO tablespaces.
SQL> create undo tablespace UNDO_RBS1
2 datafile ‘undorbs1.dbf‘ size 100m;
Tablespace created.
SQL> select TABLESPACE_NAME, CONTENTS,
2 EXTENT_MANAGEMENT, ALLOCATION_TYPE,
3 SEGMENT_SPACE_MANAGEMENT
4 from dba_tablespaces where contents=‘UNDO‘;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------- ---------- --------- ------
RBS UNDO LOCAL SYSTEM MANUAL
UNDO_RBS1 UNDO LOCAL SYSTEM MANUAL
SQL> select owner,segment_name,tablespace_name, status
2 from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ -------------- --------------- ----------------
PUBLIC _SYSSMU1$ RBS ONLINE
PUBLIC _SYSSMU2$ RBS ONLINE
PUBLIC _SYSSMU3$ RBS ONLINE
PUBLIC _SYSSMU5$ RBS ONLINE
PUBLIC _SYSSMU7$ RBS ONLINE
PUBLIC _SYSSMU9$ RBS ONLINE
PUBLIC _SYSSMU10$ RBS ONLINE
PUBLIC _SYSSMU8$ RBS ONLINE
PUBLIC _SYSSMU6$ RBS ONLINE
PUBLIC _SYSSMU4$ RBS ONLINE
SYS SYSTEM SYSTEM ONLINE
SYS RS01 SYSTEM OFFLINE
PUBLIC _SYSSMU11$ UNDO_RBS1 OFFLINE
PUBLIC _SYSSMU12$ UNDO_RBS1 OFFLINE
You try the following operations and receive errors:
-----------------------------------------------------------
Example 1: Create a rollback segment in an UNDO tablespace
-----------------------------------------------------------
UNDO_MANAGEMENT=AUTO
SQL> create rollback segment undo_rs1 tablespace undo_rbs1;
create rollback segment undo_rs1 tablespace undo_rbs1
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
----------------------------------------------------------
Example 2: Alter a rollback segment of an UNDO tablespace
----------------------------------------------------------
UNDO_MANAGEMENT=AUTO
SQL> alter rollback segment "_SYSSMU11$" online;
alter rollback segment "_SYSSMU11$" online
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
SQL> alter rollback segment "_SYSSMU8$" offline;
alter rollback segment "_SYSSMU8$" offline
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
--------------------------------------------------------------
Example 3: Create a rollback segment in a non UNDO tablespace
--------------------------------------------------------------
UNDO_MANAGEMENT=AUTO
SQL> create public rollback segment rs02 tablespace system;
create public rollback segment rs02 tablespace system
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
------------------------------------------------------------------
Example 4: Online a rollback segment created in an UNDO tablespace
------------------------------------------------------------------
UNDO_MANAGEMENT=MANUAL
Create a rollback segment in an UNDO tablespace and set it online:
SQL> create rollback segment undo_rs1 tablespace undo_rbs1;
Rollback segment created.
SQL> alter rollback segment undo_rs1 online;
Rollback segment altered.
UNDO_MANAGEMENT=AUTO
Start the database in AUM mode. Any rollback segment previously created in the
active UNDO tablespace is automatically set OFFLINE and cannot be set back online.
SQL> alter rollback segment undo_rs1 online;
alter rollback segment undo_rs1 online
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
But you can drop them:
SQL> drop rollback segment undo_rs1;
Rollback segment dropped.
=>
All cases of error ORA-30019 are a result of choosing to manage rollback
segments in AUTOMATIC mode, setting the init.ora parameter to
UNDO_MANAGEMENT=AUTO
----------------------------------------------------------------------------
|The RDBMS prohibits any manual operation on ROLLBACK SEGMENTS of any type |
|of tablespace, except dropping them. |
----------------------------------------------------------------------------
--------------------------------------------------------------
Example 5: Alter an UNDO tablespace to set it OFFLINE
--------------------------------------------------------------
UNDO_MANAGEMENT=MANUAL
SQL> select TABLESPACE_NAME, CONTENTS, STATUS
2 from dba_tablespaces where contents=‘UNDO‘;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
RBS UNDO OFFLINE
UNDO_RBS1 UNDO ONLINE
SQL> alter tablespace undo_rbs1 offline;
alter tablespace undo1 offline
*
ERROR at line 1:
ORA-01546: tablespace contains active rollback segment ‘UNDO_RS1‘
Since you can create rollback segments in UNDO tablespaces, if you are in
manual mode, the rollback segments created in UNDO tablespaces are made
available for the transactions. You cannot therefore offline the tablespace
they reside in without first offlining any rollback segments in the tablespace.
SQL> alter rollback segment undo_rs1 offline;
Rollback segment altered.
SQL> alter tablespace undo_rbs offline;
Tablespace altered.
--------------------------------------------------------------------------
Example 6: Alter a system-managed rollback segment of an UNDO tablespace ONLINE
--------------------------------------------------------------------------
UNDO_MANAGEMENT=MANUAL
SQL> select tablespace_name,segment_name, status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ -------------
SYSTEM SYSTEM ONLINE
RBS _SYSSMU1$ OFFLINE
RBS _SYSSMU2$ OFFLINE
RBS _SYSSMU3$ OFFLINE
RBS _SYSSMU4$ OFFLINE
RBS _SYSSMU5$ OFFLINE
RBS _SYSSMU6$ OFFLINE
RBS _SYSSMU7$ OFFLINE
RBS _SYSSMU8$ OFFLINE
RBS _SYSSMU9$ OFFLINE
RBS _SYSSMU10$ OFFLINE
UNDO_RBS1 _SYSSMU11$ OFFLINE
SQL> alter rollback segment "_SYSSMU11$" ONLINE;
alter rollback segment "_SYSSMU11$" ONLINE
*
ERROR at line 1:
ORA-30017: segment ‘_SYSSMU1$‘ is not supported in MANUAL Undo Management mode
If you have set UNDO_MANAGEMENT to MANUAL, any system-generated undo segments
that were previously created in AUTO mode cannot be manually altered.
*** ********************************
*** In all cases: AUM or MANUAL mode
*** ********************************
The only operations you can perform with system-generated undo segments
are CREATE/DROP UNDO tablespace:
SQL> drop rollback segment "_SYSSMU11$";
drop rollback segment "_SYSSMU11$"
*
ERROR at line 1:
ORA-30025: DROP segment ‘_SYSSMU11$‘ (in undo tablespace) not allowed
SQL> drop tablespace UNDO_RBS1 including contents and datafiles;
Tablespace dropped.
SQL> create undo tablespace undo_rbs1
2 datafile ‘/sme/app/oracle/product/9iRel10/oradata/V9r10/undorbs1.dbf‘
3 size 200k;
Tablespace created.
RELATED DOCUMENTS
-----------------
[NOTE:135090.1] Managing Rollback/Undo Segments in AUM (Automatic Undo
Management)
[NOTE:135053.1] How to Create a Database with Automatic Undo Management
Subject: Which Operations are Allowed or Prohibited on RBS with or without AUM?
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 08-FEB-2001
Last Revision Date: 07-AUG-2003
PURPOSE
-------
This bulletin shows the errors you would encounter when you attempt to use
operations on rollback segments whether you are in Automatic Undo Management
(AUM) mode or not.
It explains the reasons for the restrictions applied on rollback segment
management.
The error messages always specify which mode the operations takes place in:
* Automatic Undo Management
* Manual Undo Management
SCOPE & APPLICATION
-------------------
For DBAs who are used to managing the rollback segments of a database manually
and who now have to consider the existence of AUM (Automatic Undo Management).
Which operations are allowed or prohibited on rollback segments when you are
using or not using AUM?
Different types of errors
-------------------------
Imagine you have two UNDO tablespaces available in the database, and therefore
a number of system-generated rollback segments.
You also have at least a user-generated rollback segment called SYSTEM and
eventually some other user-generated rollback segments in non UNDO tablespaces.
SQL> create undo tablespace UNDO_RBS1
2 datafile ‘undorbs1.dbf‘ size 100m;
Tablespace created.
SQL> select TABLESPACE_NAME, CONTENTS,
2 EXTENT_MANAGEMENT, ALLOCATION_TYPE,
3 SEGMENT_SPACE_MANAGEMENT
4 from dba_tablespaces where contents=‘UNDO‘;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------- ---------- --------- ------
RBS UNDO LOCAL SYSTEM MANUAL
UNDO_RBS1 UNDO LOCAL SYSTEM MANUAL
SQL> select owner,segment_name,tablespace_name, status
2 from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ -------------- --------------- ----------------
PUBLIC _SYSSMU1$ RBS ONLINE
PUBLIC _SYSSMU2$ RBS ONLINE
PUBLIC _SYSSMU3$ RBS ONLINE
PUBLIC _SYSSMU5$ RBS ONLINE
PUBLIC _SYSSMU7$ RBS ONLINE
PUBLIC _SYSSMU9$ RBS ONLINE
PUBLIC _SYSSMU10$ RBS ONLINE
PUBLIC _SYSSMU8$ RBS ONLINE
PUBLIC _SYSSMU6$ RBS ONLINE
PUBLIC _SYSSMU4$ RBS ONLINE
SYS SYSTEM SYSTEM ONLINE
SYS RS01 SYSTEM OFFLINE
PUBLIC _SYSSMU11$ UNDO_RBS1 OFFLINE
PUBLIC _SYSSMU12$ UNDO_RBS1 OFFLINE
You try the following operations and receive errors:
-----------------------------------------------------------
Example 1: Create a rollback segment in an UNDO tablespace
-----------------------------------------------------------
UNDO_MANAGEMENT=AUTO
SQL> create rollback segment undo_rs1 tablespace undo_rbs1;
create rollback segment undo_rs1 tablespace undo_rbs1
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
----------------------------------------------------------
Example 2: Alter a rollback segment of an UNDO tablespace
----------------------------------------------------------
UNDO_MANAGEMENT=AUTO
SQL> alter rollback segment "_SYSSMU11$" online;
alter rollback segment "_SYSSMU11$" online
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
SQL> alter rollback segment "_SYSSMU8$" offline;
alter rollback segment "_SYSSMU8$" offline
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
--------------------------------------------------------------
Example 3: Create a rollback segment in a non UNDO tablespace
--------------------------------------------------------------
UNDO_MANAGEMENT=AUTO
SQL> create public rollback segment rs02 tablespace system;
create public rollback segment rs02 tablespace system
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
------------------------------------------------------------------
Example 4: Online a rollback segment created in an UNDO tablespace
------------------------------------------------------------------
UNDO_MANAGEMENT=MANUAL
Create a rollback segment in an UNDO tablespace and set it online:
SQL> create rollback segment undo_rs1 tablespace undo_rbs1;
Rollback segment created.
SQL> alter rollback segment undo_rs1 online;
Rollback segment altered.
UNDO_MANAGEMENT=AUTO
Start the database in AUM mode. Any rollback segment previously created in the
active UNDO tablespace is automatically set OFFLINE and cannot be set back online.
SQL> alter rollback segment undo_rs1 online;
alter rollback segment undo_rs1 online
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
But you can drop them:
SQL> drop rollback segment undo_rs1;
Rollback segment dropped.
=>
All cases of error ORA-30019 are a result of choosing to manage rollback
segments in AUTOMATIC mode, setting the init.ora parameter to
UNDO_MANAGEMENT=AUTO
----------------------------------------------------------------------------
|The RDBMS prohibits any manual operation on ROLLBACK SEGMENTS of any type |
|of tablespace, except dropping them. |
----------------------------------------------------------------------------
--------------------------------------------------------------
Example 5: Alter an UNDO tablespace to set it OFFLINE
--------------------------------------------------------------
UNDO_MANAGEMENT=MANUAL
SQL> select TABLESPACE_NAME, CONTENTS, STATUS
2 from dba_tablespaces where contents=‘UNDO‘;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
RBS UNDO OFFLINE
UNDO_RBS1 UNDO ONLINE
SQL> alter tablespace undo_rbs1 offline;
alter tablespace undo1 offline
*
ERROR at line 1:
ORA-01546: tablespace contains active rollback segment ‘UNDO_RS1‘
Since you can create rollback segments in UNDO tablespaces, if you are in
manual mode, the rollback segments created in UNDO tablespaces are made
available for the transactions. You cannot therefore offline the tablespace
they reside in without first offlining any rollback segments in the tablespace.
SQL> alter rollback segment undo_rs1 offline;
Rollback segment altered.
SQL> alter tablespace undo_rbs offline;
Tablespace altered.
--------------------------------------------------------------------------
Example 6: Alter a system-managed rollback segment of an UNDO tablespace ONLINE
--------------------------------------------------------------------------
UNDO_MANAGEMENT=MANUAL
SQL> select tablespace_name,segment_name, status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ -------------
SYSTEM SYSTEM ONLINE
RBS _SYSSMU1$ OFFLINE
RBS _SYSSMU2$ OFFLINE
RBS _SYSSMU3$ OFFLINE
RBS _SYSSMU4$ OFFLINE
RBS _SYSSMU5$ OFFLINE
RBS _SYSSMU6$ OFFLINE
RBS _SYSSMU7$ OFFLINE
RBS _SYSSMU8$ OFFLINE
RBS _SYSSMU9$ OFFLINE
RBS _SYSSMU10$ OFFLINE
UNDO_RBS1 _SYSSMU11$ OFFLINE
SQL> alter rollback segment "_SYSSMU11$" ONLINE;
alter rollback segment "_SYSSMU11$" ONLINE
*
ERROR at line 1:
ORA-30017: segment ‘_SYSSMU1$‘ is not supported in MANUAL Undo Management mode
If you have set UNDO_MANAGEMENT to MANUAL, any system-generated undo segments
that were previously created in AUTO mode cannot be manually altered.
*** ********************************
*** In all cases: AUM or MANUAL mode
*** ********************************
The only operations you can perform with system-generated undo segments
are CREATE/DROP UNDO tablespace:
SQL> drop rollback segment "_SYSSMU11$";
drop rollback segment "_SYSSMU11$"
*
ERROR at line 1:
ORA-30025: DROP segment ‘_SYSSMU11$‘ (in undo tablespace) not allowed
SQL> drop tablespace UNDO_RBS1 including contents and datafiles;
Tablespace dropped.
SQL> create undo tablespace undo_rbs1
2 datafile ‘/sme/app/oracle/product/9iRel10/oradata/V9r10/undorbs1.dbf‘
3 size 200k;
Tablespace created.
RELATED DOCUMENTS
-----------------
[NOTE:135090.1] Managing Rollback/Undo Segments in AUM (Automatic Undo
Management)
[NOTE:135053.1] How to Create a Database with Automatic Undo Management
