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
*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
*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)
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
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;
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;
SQL> select segment_name, tablespace_name from dba_rollback_segs;