How to Change the Existing Undo Tablespace to a New Undo Tablespace

How to Change the Existing Undo Tablespace to a New Undo Tablespace

Before doing any action, please perform a Backup of the database, just in case something doesn't works we will have a point to go back.

1. Determine the size of the datafile(s) for your current undo tablespace "UNDOTBS1":

SQL> select bytes, name from v$datafile where name like '%UNDO%';
Or)
SQL> select FILE_NAME,BYTES from dba_data_files where FILE_NAME like '%undo%';
BYTES                   NAME
--------------          -------------------------------
314572800             /u02/orada/PROD/UNDOTBS01.DBF'

2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> create undo tablespace UNDOTBS2 datafile '/u02/orada/PROD/UNDOTBS02.DBF' size 500M;Tablespace created.

3. Edit your init.ora file and change the parameter "undo_tablespace=" so it points to the newly created tablespace. You may need to create a pfile first:

SQL> create pfile='/d02/oracle/proddb/11.2.0.3/pfileprod.ora' from spfile;
File created.

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 (specify the pfile if you created one in step 3.)

SQL> startup pfile='/d02/oracle/proddb/11.2.0.3/pfileprod.ora'

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. Recreate your spfile with the new undo_tablespace value and change undo_management to AUTO:

undo_management='AUTO'
undo_tablespace='UNDOTBS2'

SQL> create spfile from pfile;

File created.

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

+++++++++++++++++++++

ID 431652.1

+++++++++++++++++++++

SQL> show parameter undo;

NAME                       TYPE        VALUE

--------------------      -----------     ------------------

undo_management        string       AUTO

undo_retention             integer      18000   // 900*20

undo_tablespace           string       APPS_UNDOTS1

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'APPS_UNDOTS1' GROUP BY STATUS;

STATUS    SUM(BYTES)/1024/1024   COUNT(*)

--------- -------------------- ----------

ACTIVE                  126442      23652

EXPIRED              1797.5625       1723

UNEXPIRED             6403.125       9231

To Know the how much space is occupied :

SQL> SELECT creation_time, metric_value, message_type, reason, suggested_action FROM dba_outstanding_alerts WHERE object_name='APPS_UNDOTS3';

To know the all data files  increastd by size:

SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name

from dba_data_files where autoextensible = 'YES';

+++++++++++++++++++++++++++++++