Those of us that have worked with Oracle databases over the years have seen new features come along that relieved some of the more challenging aspects of database administration (i.e. things that drove us crazy).  One of the new features we were happy to see was automatic undo management in Oracle 9i release 1.  This new feature made the challenge of managing rollback segments obsolete. In the days before automatic undo management, the DBA not only had to create the rollback tablespace, but also size and manage the rollback segments it contained.

For newer DBA’s, these terms may not be familiar, so a little background may be in order.  Before the undo tablespace, there was the rollback tablespace.  This was where Oracle maintained changed, but uncommitted data for read consistency, much like the undo tablespace.  The rollback tablespace was comprised of rollback segments which needed to be manually created by the DBA.  The number and size of these segments was at the discretion of the DBA.  If the number or size of the rollback segments was insufficient for the amount of database activity, then contention for the rollback segment data blocks was a real, and common, occurrence.

Here is a typical database creation script from the Oracle 8i release 3 (8.1.7) documentation:

CREATE DATABASE rbdb1
    CONTROLFILE REUSE
    LOGFILE '/u01/oracle/rbdb1/redo01.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo02.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo03.log' SIZE 1M REUSE,
            '/u01/oracle/rbdb1/redo04.log' SIZE 1M REUSE
    DATAFILE '/u01/oracle/rbdb1/system01.dbf' SIZE 10M REUSE 
      AUTOEXTEND ON
      NEXT 10M MAXSIZE 200M 
    CHARACTER SET WE8ISO8859P1;

CREATE ROLLBACK SEGMENT rb_temp STORAGE (INITIAL 100 k NEXT 250 k);

-- Alter temporary system tablespace online before proceding
ALTER ROLLBACK SEGMENT rb_temp ONLINE;

-- Create additional tablespaces ...
-- RBS: For rollback segments
-- USERs: Create user sets this as the default tablespace
-- TEMP: Create user sets this as the temporary tablespace
CREATE TABLESPACE rbs
    DATAFILE '/u01/oracle/rbdb1/rbs01.dbf' SIZE 5M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE users
    DATAFILE '/u01/oracle/rbdb1/users01.dbf' SIZE 3M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE temp
    DATAFILE '/u01/oracle/rbdb1/temp01.dbf' SIZE 2M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;

-- Create rollback segments.  
CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb4 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;

-- Bring new rollback segments online and drop the temporary system one
ALTER ROLLBACK SEGMENT rb1 ONLINE;
ALTER ROLLBACK SEGMENT rb2 ONLINE;
ALTER ROLLBACK SEGMENT rb3 ONLINE;
ALTER ROLLBACK SEGMENT rb4 ONLINE;

ALTER ROLLBACK SEGMENT rb_temp OFFLINE;
DROP ROLLBACK SEGMENT rb_temp ;

As you can see, you had to create and online one system rollback segment in the system tablespace before you could create the other tablespaces including rollback with its inclusive rollback segments.  You then had to bring the rollback segments you created online.  The temporary rollback segment was taken offline and removed at the end for housekeeping.

Rollback segments still exist in the newer versions of the Oracle database, but they are automatically created and managed by internal processes.  Additional segments are automatically created as needed.

Here is the output from a 9i release 1 database.  The sys-owned rollback segment takes the place of the rb_temp rollback segment in the 8i database creation script present during the creation of the database.  The difference here is that it is created automatically as part of the database creation process.  The remaining rollback segments were created automatically when the undo tablespace was created.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE    9.0.1.1.1       Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production


SQL> select segment_name, owner from dba_rollback_segs;

SEGMENT_NAME                   OWNER
------------------------------ ------
SYSTEM                         SYS
_SYSSMU1$                      PUBLIC
_SYSSMU2$                      PUBLIC
_SYSSMU3$                      PUBLIC
_SYSSMU4$                      PUBLIC
_SYSSMU5$                      PUBLIC
_SYSSMU6$                      PUBLIC
_SYSSMU7$                      PUBLIC
_SYSSMU8$                      PUBLIC
_SYSSMU9$                      PUBLIC
_SYSSMU10$                     PUBLIC


Except for the segment naming convention, things look very similar in a version 11.2.0.4.0 database

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select segment_name, owner from dba_rollback_segs;

SEGMENT_NAME                   OWNER
------------------------------ ------
SYSTEM                         SYS
_SYSSMU10_1197734989$          PUBLIC
_SYSSMU9_1650507775$           PUBLIC
_SYSSMU8_517538920$            PUBLIC
_SYSSMU7_2070203016$           PUBLIC
_SYSSMU6_1263032392$           PUBLIC
_SYSSMU5_898567397$            PUBLIC
_SYSSMU4_1254879796$           PUBLIC
_SYSSMU3_1723003836$           PUBLIC
_SYSSMU2_2996391332$           PUBLIC
_SYSSMU1_3724004606$           PUBLIC

When automatic undo management was first introduced, you could choose between MANUAL or AUTO.  The manual setting was the default and I suspect this had to do with not wanting to force this new feature on anyone.  It wasn’t until 11g release 1 that the AUTO setting became the default for this parameter.  These days, I don’t know of anyone who still uses manual redo management, although it is still available as of 12c release 2.   Although there are occasional ORA-01555 (snapshot too old) errors encountered with the use of auto undo management, this is one feature that I think was worth the wait.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s