Let us we discuss about undo management / undo tablespace in oracle 11g before we need know

what is undo ? why we need undo ? how is is helpful for Dba's  

What is undo?

-Oracle database maintaining the information  that is used to rollback or undo the changes to the database and it keeps the transaction record before they  are  committed  .

-Oracle needs this information to rollback or undo the changes to the database.

-These records are called rollback or undo records

Uses

*Rollback transactions - rollback the  uncommitted transactions

*Recover the database - during database recovery undo records are used to undo to uncommitted transactions are applied from redolog to datafiles

*Read consistency

*Recover from logical corruption using flashback features

-It  was introduced from  oracle 9i before that  previous versions "rollback segments"  to manage undo

Types of undo management

(Note:Oracle strongly recommends that you use undo tablespace  to manage undo rather that rollback segments)

undo1  

Automatic Undo management Mode:

In initialization parameter to  UNDO_MANAGEMENT=AUTO  while starts db its automatic undo management mode.By default it is in manual  undo management mode  undo_tablespace

-It's  a optional dynamic parameter,can be change online -By default this tablespace is created on database creation

-by mentioned this  tablespace is undo_tablespace=undotbs to find out all  undo tablespace

SQL> select tablespace_name, contents from dba_tablespaces where contents = 'UNDO';

to view current undo

SQL> show parameter undo_tablespace

or

SQL> select VALUE from v$parameter where NAME='undo_tablespace';

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions.

The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.

we can see in parameter file

UNDO_RETENTION=900

Here by default value is 900 calculation of undo_retention size

undo_retention=Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

to change the undo_retention

SQL> alter system set UNDO_RETENTION=2400;

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

If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space (if retention is not guaranteed).

This action can potentially cause some queries to fail with the ORA-01555 "snapshot too old" error message.

Create undo tablespace

SQL> CREATE UNDO TABLESPACE undotbs_02 DATAFILE '$ORACLE_BASE/oradata/TEST/undo02.dbf' SIZE 2M REUSE AUTOEXTEND ON RETENTION NOGUARANTEE;

Note: we can create more than one undo tablespace but only one  can active at one time

Alter undo tablespace

SQL> ALTER UNDO TABLESPACE undotbs_02 ADD DATAFILE '$ORACLE_BASE/oradata/TEST/undo02.dbf' SIZE 2M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

 Rename datafile

SQL>Alter database RENAME FILE 'old_full_path' TO 'new_full_path';

Offline / Online datafile

SQL> ALTER TABLESPACE undotbs offline;
SQL> ALTER TABLESPACE undotbs online;

Dropping Undo Tablespace

SQL>Drop tablespace undots1;

Switching Undo Tablespaces

SQL> Alter SYSTEM SET UNDO_TABLESPACE = undotbs_02;

 

 To view space information
 
    1) V$UNDOSTAT - Estimate the current over load
    2) V$ROLLSTAT -  For automatic undo management mode
   3) V$TRANSACTION -  Contains undo segment information
   4) DBA_UNDO_EXTENTS - Shows the status and size of each extent in the undo tablespace
   5)WRH$_UNDOSTAT -  Contains statistical snapshots of V$UNDOSTAT information
   6)WRH$_ROLLSTAT - Contains statistical snapshots of V$ROLLSTAT information
 
Undo segments in the database
 
SQL> select segment_name, tablespace_name from dba_rollback_segs;