Managing UNDO TABLESPACE
Before commit, Oracle Database keeps records of actions of transaction because Oracle needs this information to rollback or Undo the Changes.
What is the main Init.ora Parameters for Automatic Undo Management?
UNDO_MANAGEMENT:
The default value for this parameter is MANUAL. If you want to set the database in an automated mode, set this value to AUTO. (UNDO_MANAGEMENT = AUTO)
UNDO_TABLESPACE:
UNDO_TABLESPACE defines the tablespaces that are to be used as Undo Tablespaces. If no value is specified, Oracle will use the system rollback segment to startup. This value is dynamic and can be changed online (UNDO_TABLESPACE = <Tablespace_Name>)
UNDO_RETENTION:
The default value for this parameter is 900 Secs. This value specifies the amount of time, Undo is kept in the tablespace. This applies to both committed and uncommitted transactions since the introduction of FlashBack Query feature in Oracle needs this information to create a read consistent copy of the data in the past.
UNDO_SUPRESS_ERRORS:
Default values is FALSE. Set this to true to suppress the errors generated when manual management SQL operations are issued in an automated management mode.
How to Creating UNDO Tablespaces?
UNDO tablespaces can be created during the database creation time or can be added to an existing database using the create UNDO Tablespace command
Scripts at the time of Database creation:
CREATE DATABASE <DB_NAME>
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE ‘<DISK>:Directory<FILE_NAME>.DBF’ SIZE 204800K REUSE
AUTOEXTEND ON NEXT 20480K MAXSIZE 32767M
UNDO TABLESPACE "<UNDO_TABLESPACE_NAME>"
DATAFILE ‘<DISK>:DIRECTORY<FILE_NAME>.DBF’ SIZE 1178624K REUSE
AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 (<DISK>:DIRECTORY<FILE_NAME>.LOG’) SIZE 5024K,
GROUP 2 (‘<DISK>:DIRECTORY<FILE_NAME>.LOG’) SIZE 5024K,
GROUP 3 (<DISK>:DIRECTORY<FILE_NAME>.LOG’) SIZE 5024K;
Scripts after creating Database:
CREATE UNDO TABLESPACE "<UNDO_TABLESPACE_NAME"
DATAFILE ‘<DISK>:DIRECTORY<FILE_NAME>.DBF’ SIZE 1178624K REUSE
AUTOEXTEND ON;
How to Dropping an Undo Tablespace?
You cannot drop Active undo tablespace. Means, undo tablespace can only be dropped if it is not currently used by any instance. Use the DROP TABLESPACE statement to drop an undo tablespace and all contents of the undo tablespace are removed.
Example:
DROP TABLESPACE <UNDO_TABLESPACE_NAME> including contents;
How to Switching Undo Tablespaces?
We can switch form one undo tablespace to another undo tablespace. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.
Step 1: Create another UNDO TABLESPACE
CREATE UNDO TABLESPACE "<ANOTHER_UNDO_TABLESPACE>"
DATAFILE ‘<DISK>:Directory<FILE_NAME>.DBF’ SIZE 1178624K REUSE
AUTOEXTEND ON;
Step 2: Switches to a new undo tablespace:
alter system set UNDO_TABLESPACE=<UNDO_TABLESPACE>;
Step 3: Drop old UNDO TABLESPACE
drop tablespace <UNDO_TABLESPACE> including contents;
IMPORTANT:
The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed. When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.
The switch operation does not wait for transactions in the old undo tablespace to commit. If there is any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).
If the parameter value for UNDO TABLESPACE is set to ” (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This can be used, for example, to unassign an undo tablespace in the event that you want to revert to manual undo management mode.
The following example unassigns the current undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = ”;
How to Monitoring Undo Space?
The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for undo space consumption, transaction concurrency, and length of queries in the instance.
The following example shows the results of a query on the V$UNDOSTAT view.
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,
MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT;
Responses