What is the difference between Rollback and Undo Tablespace? OTN Forum By user user503050

There might be confusion while undo and rollback segment terms are used interchangeably in db world. It is due to the compatibility issue of oracle.

Undo

Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

  • Roll back transactions when a ROLLBACK statement is issued
  • Recover the database
  • Provide read consistency
  • Analyze data as of an earlier point in time by using Flashback Query

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Undo vs Rollback

Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.

To see the undo management mode and other undo related information of database-

SQL> show parameter undo

NAME                               TYPE      VALUE
———————————— ———–   ——————————
undo_management         string     AUTO
undo_retention                integer   900
undo_tablespace              string     UNDOTBS1

Since the advent of Oracle9i, less time-consuming and suggested way is—using Automatic Undo Management—in which Oracle Database creates and manages rollback segments (now called “undo segments”) in a special-purpose undo tablespace. Unlike with rollback segments, we don’t need to create or manage individual undo segments—Oracle Database does that for you when you create the undo tablespace. All transactions in an instance share a single undo tablespace. Any executing transaction can consume free space in the undo tablespace, and when the transaction completes, its undo space is freed (depending on how it’s been sized and a few other factors, like undo retention). Thus, space for undo segments is dynamically allocated, consumed, freed, and reused—all under the control of Oracle Database, rather than manual management by someone.

Switching Rollback to Undo

1. We have to create an Undo tablespace. Oracle provides a function (10g and up) that provides information on how to size new undo tablespace based on the configuration and usage of the rollback segments in the system.

DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB ;= DBMS_UNDO_ADV.RBU_MIGRATION;
end;
/

CREATE UNDO TABLESPACE UNDOTBS
DATAFILE ‘/oradata/dbf/undotbs_1.dbf’
SIZE 100M AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED RETENTION NOGUARANTEE;

Note: In undo tablespace creation, “SEGMENT SPACE MANAGEMENT AUTO” can not be set

2.Change system parameters

SQL> alter system set undo_retention=900 scope=both;
SQL> alter system set undo_tablespace=UNDOTBS scope=both;
SQL> alter system set undo_management=AUTO scope=spfile;
SQL> shutdown immediate
SQL> startup

UNDO_MANAGEMENT is a static parameter. So database needs to be restarted.

Regards,

Francisco Munoz Alvarez

Advertisements

Oracle ACE Director and President of LAOUC, NZOUG and CLOUG. Organizer of LA and APAC OTN Tours,

Posted in Oracle FAQ, Questions, Rollback, Undo
2 comments on “What is the difference between Rollback and Undo Tablespace? OTN Forum By user user503050
  1. Oracle NZ – Francisco Munoz Alvarez » What is the difference between Rollback and Undo Tablespace? OTN Forum By user user503050…

    There might be confusion while undo and rollback segment terms are used interchangeably in db world. It is due to the compatibility issue of oracle….

  2. Axel Posada says:

    Muy bonito, bastante claro.

    Hay algún consejo para optimizar el tablespace de Undo? Tamaño de bloque en especial? Configuración de discos? Existe la posibilidad de que las operaciones a nivel de base, presente algún tipo de lentitud asociada con esta tablespace, es decir, puede llegar a convertirse en un “Wait Event”?

    Saludos!

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

%d bloggers like this: