Undo Management

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 11 Jun, 2022
  • 0 Comments
  • 6 Mins Read

Undo Management

Undo Management In Oracle

What Is Undo? Every 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 Oracle Flashback Query * Recover from logical corruptions using Oracle Flashback features * 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.

When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace. This is not recommended in normal circumstances, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace.

  If the database contains multiple undo tablespaces, you can optionally specify at startup that you want to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter, as shown in this example:

 UNDO_TABLESPACE = undotbs_01 

Undo Retention Period :

When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.

         Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter.

You set the undo retention period by setting the UNDO_RETENTION initialization parameter. This parameter specifies the desired minimum undo retention period in seconds.

The current undo retention period may be automatically tuned to be greater than UNDO_RETENTION, or, unless retention guarantee is enabled,

less than UNDO_RETENTION if space is low.

To set the undo retention period:

Do one of the following:

Set UNDO_RETENTION in the initialization parameter file.

UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

 ALTER SYSTEM SET UNDO_RETENTION = 2400;

The effect of an UNDO_RETENTION parameter change is immediate, but it can only be honored if the current undo tablespace has enough space.

 CREATE UNDO TABLESPACE Statement The CREATE UNDO TABLESPACE statement is the same as the CREATE TABLESPACE statement, but the UNDO keyword is specified. The database determines most of the attributes of the undo tablespace, but you can specify the DATAFILE clause. This example creates the undotbs_02 undo tablespace with the AUTOEXTEND option: 
  CREATE UNDO TABLESPACE undotbs_02 DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;
You can create more than one undo tablespace, but only one of them can be active at any one time. Altering an Undo Tablespace Undo tablespaces are altered using the ALTER TABLESPACE statement. However, since most aspects of undo tablespaces are system managed, you need only be concerned with the following actions: Adding a datafile Renaming a datafile Bringing a datafile online or taking it offline Beginning or ending an open backup on a datafile Enabling and disabling undo retention guarantee These are also the only attributes you are permitted to alter. If an undo tablespace runs out of space, or you want to prevent it from doing so, you can add more files to it or resize existing datafiles. The following example adds another datafile to undo tablespace undotbs_01: 

ALTER TABLESPACE undotbs_01

ADD DATAFILE '/u01/oracle/rbdb1/undo0102.dbf' AUTOEXTEND ON NEXT 1M

MAXSIZE UNLIMITED; 

Dropping an Undo Tablespace

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tablespace undotbs_01:

  DROP TABLESPACE undotbs_01;   

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails. However, since DROP TABLESPACE drops an undo tablespace even if it contains unexpired undo information (within retention period), you must be careful not to drop an undo tablespace if undo information is needed by some existing queries.

Switching Undo Tablespaces

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablespace:

  ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02; 

Assuming undotbs_01 is the current undo tablespace, after this command successfully executes, the instance uses undotbs_02 in place of undotbs_01 as its undo tablespace.

If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:

The tablespace does not exist

The tablespace is not an undo tablespace

The tablespace is already being used by another instance (in a RAC environment only)

The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed.

Migrating to Automatic Undo Management

If you are currently using rollback segments to manage undo space, Oracle strongly recommends that you migrate your database to automatic undo management. Oracle Database provides a function that provides information on how to size your new undo tablespace based on the configuration and usage of the rollback segments in your system. DBA privileges are required to execute this function:

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

The following dynamic performance views are useful for obtaining space information about the undo tablespace:

  1. V$UNDOSTAT :

    Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.

  2. V$ROLLSTAT : For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace.
  3. V$TRANSACTION : Contains undo segment information.
  4. DBA_UNDO_EXTENTS : Shows the status and size of each extent in the undo tablespace.
  5. DBA_HIST_UNDOSTAT : Contains statistical snapshots of V$UNDOSTAT information. 

The following example shows the results of a query on the V$UNDOSTAT view.

[php]
SELECT TO_CHAR(BEGIN_TIME, ‘MM/DD/YYYY HH24:MI:SS’) BEGIN_TIME,
TO_CHAR(END_TIME, ‘MM/DD/YYYY HH24:MI:SS’) END_TIME,
UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS “MAXCON”
FROM v$UNDOSTAT WHERE rownum < = 144;

 

PaSSword File - orapwd Utility in Oracle
ORA-12514