Every Oracle database requires a mechanism to undo transactional changes. All the changes made by the dml transactions can be done undone using rollback segments until oracle 8i. Oracle has introduced an advanced feature called Automatic undo tablespace management from oracle version 9.0.0. In contrast to the Roll back segment management, Oracle automatically manages all the operations like adding the segments, shrinking the segments, naming the segments, managing them, making the best possible use of undo space.
All the operations specified below involves an undo tablespace.
1. Revert back changes done with a rollback command.
2. Providing read consistency to Oracle sessions.
3. analyzing or retrieving the data at an earlier point of time using a flash back query.
4. Recover the database.
Creation of Undo tablespace :
You can create an undo tablespace at the time of creation of the database with ‘CREATE DATABASE’ command or later on you can add an UNDO tablespace with ‘CREATE UNDO TABLESPACE’ COMMAND. If Automatic undo management is enabled, and if no Single undo tablespace available, Oracle makes use of SYSTEM tablespace for undo management which is not recommended. Oracle also creates a warning in alert.log file, if it uses the system tablespace for UNDO management.
For automatic undo tablespace management by oracle, there should be atleast one active UNDO tablespace per instance.
Undo tablespace creation example by using the create database command.
CREATE database strinix
controlfile reuse
datafile ‘/u01/oradata/strinix/system_01.dbf’ size 300M
undo tablespace undo_tbs_`
datafile ‘/u02/oradata/strinix/undo_tbs_01.dbf’ size 600M
logfile
group 1
(’/u10/oradata/strinix/redo_g1m1.log’) size 25M,
. . . . . . . . . . . . . . . .
;
When creating A new database, if Undo TS is specified but no ‘undo tablespace’ in ‘CREATE DATABASE’ command, the database creation fails with the following errors.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30045: No undo tablespace name specified
Undo tablespace creation example by using the create undo tablespace command.
CREATE undo tablespace undo_tbs
datafile ‘/u02/oradata/strinix/undo_tbs_01.dbf’ size 600M;
Drop UNDO TABLESPACE:
You can drop an undo tablespace similar to other tablespaces.
Drop undo tablespace example :
DROP TABLESPACE undotbs_01;
The above statement would work only if your undo tablespace is not being used by the system currently. If an UNDO TABLESPACE consists of pending transactions, DROP TABLESPACE statement will fail.
You can force undo tablespace to be dropped by the following command :
DROP TABLESPACE …..INCLUDING CONTENTS
Undo tablespace Characteristics:
- No Permanent objects can be created in UNDO TABLESPACE (ORACLE WILL THROW ORA-30022) error.
- You can change the data file size , add data files to the undo tablespace similiar like other tablespaces.
- Changes are permitted in data file properties.
- You can alter the tablespace for online Backups.
- You cannot offline an active undo tablespace.
- You cannot change extent sizes of an undo tablespace.
Automatic Undo segments:
The names of Automatic Undo segments are system generated. They are in the form of _SYSSMUn$ (n is the undo segment number, usn). The number of Initial undo segments created depends upon the SESSIONS parameter. More automatic undo segments can be brought online, or created, as needed, provided undo space is available. One transaction per undo segment is desired reach.
Dynamic Extents Transfer:: Reusing expired (or unexpired) undo extents from other undo segments
Shrinking of Undo Segments: Every 12 hours SMON shrinks idled undo segments.Foreground processes signal SMON to shrink undo segments when more undo space is needed
UNDO RETENTION :
UNDO RETENTION specifies the duration of undo information to be held in the undo tablespace before overwriting. if a transaction is committed, there is no need to hold the undo information, but to provide the read consistency oracle tries to hold the undo information atleast for the specified duration. But holding of the undo information is not guranteed. If sufficient space is not available in undo tablespace, Oracle can overwrite the undo information.If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the ora-01555 : snapshot too old error message.
UNDO RETENTION period is specified in seconds. The default value for undo retention period is 900 seconds. Maximum value for Undo retention is 4294967295 SECONDS. For a fixed size undo tablespace the parameter “undo_retention“ is ignored. Obviously it would not be a best practice to keep an undo tablespace in an unlimited size. The other way is to fix the maximum size of a tablespace and enable the autoextend option.
An example of autoextending a tablespace fixing the maximum size
CREATE undo tablespace undo_tbs
datafile ‘/u02/oradata/strinix/undo_tbs_01.dbf’ AUTOEXTEND ON NEXT 10M maxsize 800M;
You can also change UNDO_RETENTION PERIOD immediately by using the following command.
ALTER SYSTEM SET UNDO_RETENTION = 3600;
Initilization parameters required for Automatic undo management:
UNDO_MANAGEMENT = <auto|manual>.
If UNDO_MANAGEMENT is specified as auto in initilizatin file, Oracle automatic undo management is enabled. if Manual is specified for this parameter , then the traditional Rollback segment are used for undo management. If Manual is specified for UNDO_MANAGEMENT ,below undo parameters will be ignored.
UNDO_TABLESPACE = <ts_name>
UNDO_TABLESPACE specifies the tablespace name which should be used as undo tablespace.
UNDO_RETENTION = <seconds|900>
UNDO RETENTION period is specfied in seconds.
UNDO_SUPPRESS_ERRORS = <false|true>
The defualt value for UNDO_SUPPRESS_ERRORS is false. It Reports an error if any manual management operation is done related to automatic undo segments. TRUE: Reports success for all such operations without actually carrying them out.
Switching of Undo tablespace:
An undo tablespace can be changed dynamically by using the following command:
alter system set undo_tablespace = <New Undo TS Name>;