How to replace undo tablespace in an Oracle database

Task: replace UNDO tablespace in an Oracle database

Steps:

1. See the UNDO settings in the database: SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- -------------------- undo_management string AUTO undo_retention integer 10800 undo_tablespace string UNDO 2. Determine the size of the UNDO datafiles: SQL> select bytes, name from v$datafile where upper(name) like '%UNDO%'; BYTES NAME -------------------- ----------------------------------------------- 1433075712 /fs/u02/oradata/testdb/undo_01.dbf 3. Create a new UNDO tablespace according to the requirement: CREATE UNDO TABLESPACE UNDOTBS1 datafile '/fs/u02/oradata/testdb/undotbs01.dbf' size 2000m; Tablespace created. 4. Edit the init.ora file and change the parameter "undo_tablespace=UNDOTBS1" and "undo_management=MANUAL". Note that a pfile may need to be created first by using "create pfile=… from spfile=…". "undo_management=MANUAL" ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again. 5. Shut down the databse cleanly by performing a shutdown immediate. SQL> shutdown immediate 6. Startup the database (specify the pfile if needed.) SQL> startup 7. Confirm the new tablespace is in use: SQL> show parameter undo_tablespace NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1 8. 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 ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU1$ UNDO OFFLINE PUBLIC _SYSSMU2$ UNDO OFFLINE PUBLIC _SYSSMU3$ UNDO OFFLINE PUBLIC _SYSSMU4$ UNDO OFFLINE PUBLIC _SYSSMU5$ UNDO OFFLINE PUBLIC _SYSSMU6$ UNDO OFFLINE PUBLIC _SYSSMU7$ UNDO OFFLINE PUBLIC _SYSSMU8$ UNDO OFFLINE PUBLIC _SYSSMU9$ UNDO OFFLINE PUBLIC _SYSSMU10$ UNDO OFFLINE PUBLIC _SYSSMU11$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE 21 rows selected. If the old segments are online, then they must be taken offline as follows: SQL>alter rollback segment "_SYSSMU3$" offline; 9. Drop the old undo tablespace and remove from the OS directory: SQL> drop tablespace UNDO; Tablespace dropped. SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU11$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE 11 rows selected. SQL> shutdown immediate 10. Modify the init.ora file to change undo_management to AUTO and restart the database. spfile may need to be created. : SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU11$ UNDOTBS1 ONLINE PUBLIC _SYSSMU12$ UNDOTBS1 ONLINE PUBLIC _SYSSMU13$ UNDOTBS1 ONLINE PUBLIC _SYSSMU14$ UNDOTBS1 ONLINE PUBLIC _SYSSMU15$ UNDOTBS1 ONLINE PUBLIC _SYSSMU16$ UNDOTBS1 ONLINE PUBLIC _SYSSMU17$ UNDOTBS1 ONLINE PUBLIC _SYSSMU18$ UNDOTBS1 ONLINE PUBLIC _SYSSMU19$ UNDOTBS1 ONLINE PUBLIC _SYSSMU20$ UNDOTBS1 ONLINE 11 rows selected.

More Oracle DBA tips, please visit Oracle DBA Tips