Managing the UNDO TABLESPACE in Oracle database 11gR2
Posted by Mir Sayeed Hassan on February 12th, 2018
Managing the UNDO TABLESPACE in Oracle database 11gR2
In an Oracle DB the undo tbs is used for maintaining the information that is used to rollback or any undo changes to the database.
The Undo records are more used as shown below:
- Rollback the transactions when a ROLLBACK statement is issued
- Recover the database
- Provide read consistency
- Analyze data as of an earlier point in time by using Flashback Query
- Recover from logical corruptions using Flashback features
- Now the Oracle recommends that you use undo tbs to manage undo rather than rollback segments.
By default the undo tbs will be created while creation of the database, In case if its not created you can create it later as shown below
In my testdb, the undo tbs is created while creation as shown below
sys@TESTDB>;select * from v$tablespace; TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- ------------------- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES ----- It’s created 4 USERS YES NO YES 3 TEMP NO NO YES
Or
sys@TESTDB>;select name from V$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/prim/system01.dbf /u01/app/oracle/oradata/prim/sysaux01.dbf /u01/app/oracle/oradata/prim/undotbs01.dbf ----- It’s created /u01/app/oracle/oradata/prim/users01.dbf /u01/app/oracle/oradata/prim/example01.dbf
In case if the above undo tbs does not exist, create as shown below & update in pfile
sys@TESTDB>;create tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/prim/undotbs01.dbf' size 500m autoextend on;
[oracle@ogg-test1 dbs]$ cd $ORACLE_HOME/dbs
[oracle@ogg-test1 dbs]$ vi inittestdb.ora UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE= UNDOTBS1
Verify:
sys@TESTDB>;show parameter UNDO_MANAGEMENT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO
sys@TESTDB>;show parameter UNDO_TABLESPACE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1
Altering UNDO Tablespace
In case if the Undo tbs is full, you can resize the existing datafile or add a new datafile into it
Resize undo tbs:
sys@TESTDB>;alter database datafile '/u01/app/oracle/oradata/prim/undotbs02.dbf' resize 700m; Database altered.
Add new undo datafile
sys@TESTDB>;alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/prim/undotbs02.dbf' size 100m; Tablespace altered.
Dropping an Undo Tablespace
sys@TESTDB>;drop tablespace UNDOTBS1; drop tablespace UNDOTBS1 * ERROR at line 1: ORA-30013: undo tablespace 'UNDOTBS1' is currently in use As you can see the error while dropping the undo tbs, the currently used undo tbs cannot be dropped To drop this, you need to create the new one & switch the undo tbs into it, then drop
sys@TESTDB>;create tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/prim/undotbs2.dbf' size 100m; Tablespace created.
Note: we can create more than one undo tablespace but only one can active at one time
Switching Undo Tablespaces
sys@TESTDB>;ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2' sid='TESTDB'; System altered.
Switch from using one undo tablespace to another, because the UNDO_TABLESPACE initialization parameter is a dynamic parameter so the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.
Offline/Online datafile
SQL>; alter tablespace undotbs01 offline;
SQL>; alter tablespace undotbs01 online;
To view space information
v$undostat – Estimate the current over load
v$rollstat – For automatic undo management mode
v$transaction – Contains undo segment information
desc dba_undo_extends – Shows the status and size of each extent in the undo tablespace