recreate UNDO Tablespace

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g
How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g?

Solution:

1. Determine the size of your undo tablespace

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
12.09375

2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 400M;

Tablespace created.

3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
Change undo_tablespace=UNDOTBS2
Change undo_management=MANUAL

Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.

4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.
5. Startup the database
6. Confirm the new tablespace is in use:

SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------- ----------- ------------
undo_tablespace string UNDOTBS2

7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ --------------------------- ------------------------------ -----------
PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE

....etc.

If the old segments are online, then they must be taken offline:

SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;

This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
SQL>drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

9. Edit your init.ora file do the following changes.

undo_management='AUTO'
undo_tablespace='UNDOTBS2'

10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]