UNDO File Management

Undo file management :

-->It consist of set of sql statement.starts with insert,update,delete and ends with commit and rollback

The data will commit when we 

         1.commited 

         2.exit

         3.close a session

         4.DDL Operation

The data will rollback when :

     
         1. rollback
         2.during the instance failure

         3.when auser process fails
         4.Insufficient space


-->It is composed of one or more files containing undo segments.for each oracle instance ,you only have toallocate enough disk space for the workload in that instance in an undo tablespace.


->rollback segments are still used but are internallycreated and maintained and are called undo segments.
->with automatic undo management,you cannot CREATE DROPor alter undo segments
->undo segments have the same structure as normal rollback segments but they.
->support automatic creation
->use a modified policy compared to oracle8I
->support dynamic extents transfer.
->SMON shrinks undo segments when needed.


->If yo use the rollback segment method of managing undospace,you are said to be operating in the manual undo management mode.

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBS
UNDO_RETENTION=900


UNDO RETENTION:
->retention is specified in units of seconds.it is persistent and can survive system crashes .that is,
undo generated before an instance crash,is retained until its retention time has expired even across restarting the instance.
the oracle flashback features depend upon the availability of older undo information.

sys>>alter system set undo_retention=900;


STEPS to create and maintain the Undo tablespace:
1.create an undo tablespace:

sys>>create undo tablespace undo_02 datafile '/disk1/oradata/ORCL/undo_01.dbf' size 10m;

->An undo tablespace is a permanent,locally managed tablespace,read-write and in logging mode with default block size. values for
  MINIMUM EXTENT and default storage are system generated.

2.altering or resizing a datafile

sys>>alter database datafile '/disk1/oradata/ORCL/undo1.dbf' resize 10m;

3.Adding the undofile to undo tablespace

sys>>alter tablespace undotbs add datafile '/disk1/oradata/ORCL/undo01.dbf' size autoextend on;



SQL> alter system set undo_tablespace=UNDOTBS_1 scope=both;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     2700
undo_tablespace                      string      UNDOTBS_1


Set UNDO_RETENTION in the initialization parameter file.

    UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 2400;


SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.


->Different status of undo tablespace

   1.ONLINE
   2.OFFLINE
   3.PENDING OFFLINE

->Different status of undo segments :
    1.active
    2.unexpired
    3.expired

Retention Guarantee:

sys>>create undo tablespace undo_02 datafile '/disk1/oradata/ORCL/redolog1a.log'
                                                   size 10m retention guarantee;


Existing undo tablespace can also be made to comply with the quarantee by altering them ,as in
sys>>alter tablespace undo_02 retention gurantee;

Incase we dont want quarantee retention then

sys>>alter tablespace undo_02 retention nogurantee;

You can verify that the tablespace has guaranteed undo retention with :

sys>>select retention from dba_tablespaces where tablespace_name='undo_01';

To set undao tablespace  :

sys>>alter system undo tablespace=undo01;

show queries :
sys>>show parameter undo_retention;
sys>>show parameter und_tablespace;



for more information check the Data dictionary views:

DBA_UNDO_EXTENTS
DBA_SEGMENTS
USER_SEGMENTS
v$UNDOSTAT
v$TRANSACTION