10--Managing Undo Data

 Managing Undo Data

Managing Undo Data

• There are two methods for managing undo data:

– Automatic Undo Management

– Manual Undo Management

• The term undo was known as rollback in previous

versions.

 

 

An undo segment is used to save the old value (undo data) when a process changes data in a database. It stores the location of the data and the data as it existed before being modified.

The header of an undo segment contains a transaction table where information about the current transactions using the undo segment is stored.

A serial transaction uses only one undo segment to store all of its undo data.

Many concurrent transactions can write to one undo segment.

 

 

Rollback是人工发起的，而recovery是由于实例故障，重启需要通过redo log进行恢复。Read的一致性可以基于用户甚至是同一用户的不同进程，也就是说，同一用户的两个登陆进程，一个进程的insert等动作并且未commit，那么该事务只能在该进程select到，另外一个进程是select不到的。

 

 

 

When the Oracle server begins executing a SELECT statement, it determines the current system change number (SCN) and ensures that any changes not committed before this SCN are not processed by the statement. Consider the case where a long-running query is executed at a time when several changes are being made. If a row has changes that were not committed at the start of the query, the Oracle server constructs a read-consistent image of the row by retrieving

the before image of the changes from the undo segment and applying the changes to a copy of the row in memory.

 

 

Types of Undo Segments

• SYSTEM: Used for objects in the SYSTEM tablespace

• Non-SYSTEM: Used for objects in other tablespaces:

– Auto mode: Requires an UNDO tablespace

– Manual mode:

Private: Acquired by a single instance

Public: Acquired by any instance

• Deferred: Used when tablespaces are taken offline

immediate, temporary, or for recovery

 

 

Automatic Undo Management: Concepts

• Undo data is managed using an UNDO tablespace.

• You allocate one UNDO tablespace per instance with

enough space for the workload of the instance.

• The Oracle server automatically maintains undo

data within the UNDO tablespace.

Undo segments are created with the naming convention:

_SYSSMUn$

 

Automatic Undo

Management: Configuration

• Configure two parameters in the initialization file:

– UNDO_MANAGEMENT

– UNDO_TABLESPACE

• Create at least one UNDO tablespace.

 

 

Automatic Undo Management:

Initialization Parameters

• UNDO_MANAGEMENT: Specifies whether the system

should use AUTO or MANUAL mode

• UNDO_TABLESPACE: Specifies a particular UNDO

tablespace to be used

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=UNDOTBS

 

UNDO_MANAGEMENT cannot be changed dynamically after the

database starts.

 

 

Automatic Undo Management:

UNDO Tablespace

Create the UNDO tablespace with the database by

adding a clause in the CREATE DATABASE command:

CREATE DATABASE db01

. . .

UNDO TABLESPACE undo1

DATAFILE '/u01/oradata/undoldb01.dbf' SIZE 20M

AUTOEXTEND ON

 

Or create it later by using the CREATE UNDO

TABLESPACE command:

CREATE UNDO TABLESPACE undo1

DATAFILE '/u01/oradata/undo1db01.dbf'

SIZE 20M;

 

More than one UNDO tablespace

may exist in the database, but only one UNDO tablespace can be active.

 

 

Automatic Undo Management:

Altering an UNDO Tablespace

• The ALTER TABLESPACE command can make

changes to UNDO tablespaces.

• The following example adds another data file to the

UNDO tablespace:

ALTER TABLESPACE undotbs

ADD DATAFILE '/u01/oradata/undotbs2.dbf'

SIZE 30M

AUTOEXTEND ON;

 

 

Automatic Undo Management:

Switching UNDO Tablespaces

• You can switch from using one UNDO tablespace to

another.

• Only one UNDO tablespace can be in assigned to a

database at a time.

• More than one UNDO tablespace may exist within an

instance, but only one can be active.

• Use the ALTER SYSTEM command for dynamic

switching between UNDO tablespaces.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

 

Automatic Undo Management:

Dropping an UNDO Tablespace

• The DROP TABLESPACE command drops an UNDO

tablespace.

DROP TABLESPACE UNDOTBS2;

 

• An UNDO tablespace can only be dropped if it is

currently not in use by any instance.

• To drop an active UNDO tablespace:

– Switch to a new UNDO tablespace.

– Drop the tablespace after all current transactions are

complete.

 

 

Automatic Undo Management:

Other Parameters

• UNDO_SUPPRESS_ERRORS parameter:

– Set to TRUE, this parameter suppresses errors while

attempting to execute manual operations in AUTO

mode.

• UNDO_RETENTION parameter:在undo表空间中保留的秒数。

– This parameter controls the amount of undo data to

retain for consistent read.

 

UNDO_RETENTION Parameter

Determines how long to retain undo data to provide for consistent reads. Retaining undo data allows for longer queries and also requires larger data files for the UNDO tablespace. The UNDO_RETENTION parameter, defined in seconds, can be set in the initialization file or modified dynamically with an ALTER SYSTEM command.

SQL> ALTER SYSTEM SET UNDO_RETENTION=900;

A value of 900 retains undo data for 15 minutes.

 

一个undo block只能归属一个事务。一个undo seg可以服务于多个事务。

 

Undo Data Statistics

SELECT end_time,begin_time,undoblks

FROM v$undostat;

This view displays a histogram of statistical data to show how well the database is working.

Each row in the view keeps statistics collected in the instance for a 10-minute interval.You can use this view to estimate the amount of undo space required for the current workload. The Oracle server uses this to tune undo usage in the system. This view is available in both auto mode and manual mode.

Although the time interval is normally 10 minutes, the most recent row will return the time since its interval started, usually less than 10 minutes.

 

 

Automatic Undo Management:

Sizing an UNDO Tablespace

Determining a size for the UNDO tablespace requires

three pieces of information:

• (UR) UNDO_RETENTION in seconds

• (UPS) Number of undo data blocks generated per

second

• (DBS) Overhead varies based on extent and file size

(db_block_size)

 

The number of undo blocks generated per second （ups）can be acquired from V$UNDOSTAT. The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds:

SQL> SELECT (SUM(undoblks) / SUM)

2 ((end_time - begin_time) * 86400)

3 FROM v$undostat;

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day.

The result of the query returns the number of undo blocks per second. This value must be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE. The following query calculates the number of bytes needed:

 

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"

2 FROM (SELECT value AS UR

3 FROM v$parameter

4 WHERE name = 'undo_retention'),

5 (SELECT (SUM(undoblks)/SUM

6 (((end_time-begin_time)*86400))) AS UPS

7 FROM v$undostat),

8 (SELECT value AS DBS

9 FROM v$parameter

10 WHERE name = 'db_block_size');

Bytes

----------

19106213

To convert bytes to megabytes, divide by 1,048,576 bytes. The result for this database is 18.22 MB.

 

Automatic Undo Management:

Undo Quota

• Long transactions and improperly written

transactions can consume valuable resources.

• With undo quota, users can be grouped and a

maximum undo space limit can be assigned to the

group.

• UNDO_POOL, a Resource Manager directive, defines

the amount of space allowed for a resource group.

• When a group exceeds its limit, no new transactions

are possible for the group, until undo space is freed

by current transactions which are either completing

or aborting.

 

The amount of undo data generated by a group can be limited by setting a value for UNDO_POOL: the default value is unlimited.

 

 

Obtaining Undo Segment Information

• Information about undo segments can be obtained

by querying the following views:

– DBA_ROLLBACK_SEGS

• Dynamic Performance Views

– V$ROLLNAME

– V$ROLLSTAT

– V$UNDOSTAT

– V$SESSION

– V$TRANSACTION

Information about undo segments that are offline can be seen only in DBA_ROLLBACK_SEGS.

The dynamic performance views show only undo segments that are online.

Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance.