Jun 132011
Sometimes at the first installation, we don’t care about the size of UNDO Tablespace. We just create it with maximum extensible unlimited size. So there’s one big data file with a big I/O since write-read process only on that file. This article I wrote for give you a solution how to replace an UNDO Tablespace with a big datafile with a new UNDO Tablespace with several tiny datafiles.
- Check the UNDO setting in the database :
- Create new UNDO tablespace
- Shutdown the Database (don’t used abort!!)
- Edit parameter file, initSID.ora, and change the parameter “undo_tablespace” and “undo_management”
- Startup database
- Assure the UNDO parameters already changed
- Assure all undo segments in the old UNDO Tablespace already offline
- If the undo segment of old UNDO Tablespace still “online”, make it offline
- If all undo segments of old UNDO Tablespace already “offline”, we can drop the old UNDO Tablespace
- Check current undo segments
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$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU12$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU13$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU14$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU15$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU16$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU17$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU18$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU20$ UNDOTBS2 OFFLINE 11 rows selected.
- Shutdown the database
- Modify the parameter file, initSID.ora
- Startup the database
- Check the undo segments of new UNDO Tablespace
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- -------------------- undo_management string AUTO undo_retention integer 10800 undo_tablespace string UNDOTBS1
SQL> create undo tablespace undotbs2 datafile '/oracle/oradata/undotbs2_01.dbf' size 100M autoextend on next 8M maxsize 2048M, '/oracle/oradata/undotbs2_02.dbf' size 100M autoextend on next 8M maxsize 2048M, '/oracle/oradata/undotbs2_03.dbf' size 100M autoextend on next 8M maxsize 2048M, '/oracle/oradata/undotbs2_04.dbf' size 100M autoextend on next 8M maxsize 2048M, '/oracle/oradata/undotbs2_05.dbf' size 100M autoextend on next 8M maxsize 2048M, '/oracle/oradata/undotbs2_06.dbf' size 100M autoextend on next 8M maxsize 2048M, '/oracle/oradata/undotbs2_07.dbf' size 100M autoextend on next 8M maxsize 2048M extent management local;
SQL> shutdown immediate
undo_tablespace=UNDOTBS2 undo_management=MANUAL
NOTE: if you’re using spfile, then you have to create pfile first before edit it.”Create pfile from spfile”.
SQL> startup pfile=initSID.ora
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- -------------------- undo_management string MANUAL undo_retention integer 10800 undo_tablespace string UNDOTBS2
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$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU4$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU5$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU6$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU7$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU8$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU9$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU10$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU11$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU12$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU13$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU14$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU15$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU16$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU17$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU18$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE PUBLIC _SYSSMU20$ UNDOTBS2 OFFLINE 21 rows selected.
NOTE: undo segments in the new UNDO Tablespace could be “offline” also.
SQL>alter rollback segment "_SYSSMU3$" offline;
SQL> DROP tablespace UNDOTBS1 including contents and datafiles;
SQL> shutdown immediate
undo_management=AUTO
NOTE: after edit the pfile, re-create the spfile: “create spfile from pfile”.
SQL> startup
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$ UNDOTBS2 ONLINE PUBLIC _SYSSMU12$ UNDOTBS2 ONLINE PUBLIC _SYSSMU13$ UNDOTBS2 ONLINE PUBLIC _SYSSMU14$ UNDOTBS2 ONLINE PUBLIC _SYSSMU15$ UNDOTBS2 ONLINE PUBLIC _SYSSMU16$ UNDOTBS2 ONLINE PUBLIC _SYSSMU17$ UNDOTBS2 ONLINE PUBLIC _SYSSMU18$ UNDOTBS2 ONLINE PUBLIC _SYSSMU19$ UNDOTBS2 ONLINE PUBLIC _SYSSMU20$ UNDOTBS2 ONLINE 11 rows selected.
Now you have already replaced your old UNDO Tablespace with a new one. Congratulation!!
