Undo Tablespace and Datafile Management

Concept and Use of Undo Table space Data

Undo tablespace stores commited as well as uncommited data(information)

Uncommited undo information use to rollback the uncommited transactions

Commited undo information is to use for read-consitent image and flashback queries.

              

    
     Read-consistent provide results that are consistent  with the data as of the      time  of query started. flashback queries are queries that purposely ask for a  version  of the data as it existed at some time in the past. as long as undo  information  for that past time still exists, flashback queries can complete  successfully.

    Use for recovery of failed transactions.

Real Time Scenarios

Steps for Set Parameter related to undo Tablespace

Alter System Set undo_management =AUTO Scope=spfile;

Alter system set undo_Tablespace=UNDOTBS1;

Alter system set undo_retention=1000;

Criteria

•  The undo tablespace has the autoextend option enabled.
•  You want to set undo retention for LOBs.
•  Guarantee undo retention.

Retention Guarantee

Notes:--> for a fixed-size undo tablespace, the system automatically tunes for the maximum possible undo retention period for the basis of undo tablespace size and usage history, it ignores UNDO_RETENTION unless retention gurantee is enabled.

Retention gurantee is a tablespace attribute rather than an initialization parameter.

  Required:- Because the default undo behaviour is to overwrite commited   transactions that have not yet expired rather than to allow an active transactions  to fail  because of lack of undo space. This Behaviour can be changed by guranteeing retention. The retention gurantee is only apply to undo tablespace.

Each transaction assigned to only one undo segment. and Each undo segment can service more than one transaction at a time.SQL> Alter tablespace undotbs1 retention gurantee;

Change the Behavior of undo tablespace to retention no guarantee

sql> Alter tablespace undotbs1 retention nogurantee;

Change the Behavior of undo tablespace to retention Guarantee

sql> Alter tablespace undotbs1 retention gurantee;

Steps to Change Default Undo Tablespace

sql> create undo tablespace undotbs2 datafile '+data(datafile)' size 10m autoextend on next 10m maxsize 20m extent management local;

sql> alter system set undo_tablespace=UNDOTBS2;

sql> drop tablespace undotbs1 including contents and datafiles; -- if it is not dropped then its current in use or because of retention_interval.

Interview Question’s Answers

 ü  Undo Segments are always owned by sys.

 ü  Undo data are written to only one undo tablespace at a time.

 ü  Undo tablespace is recoverd only when an instance is in mount stage.

 ü  You can see which transaction are assigned to which undo segment by checking V$TRANSACTION    Dynamic performance view.

 ü  Undo information is divided into 3 Categories.

1.   Uncommitted undo information: Supports a currently running transaction, and it is required if a user wants to roll back or if the transaction has failed. 

   Uncommitted undo information is never overwritten.

2. Committed undo information: Is no longer needed to support a running transaction, but it is still needed to meet the undo retention interval. It is also knownas “unexpired” undo information. Committed undo information is retained when possible without causing an active transaction to fail because of lack of space.

3 Expired undo information: Is no longer needed to support a running transaction.Expired undo information is overwritten when space is required by an active transaction.

Notes:- Undo advisor provides an estimate of the undo tablespace size required to satisfy the given undo retention.