How to Resize the UNDO tablespace

Hi,

This is fairly very familiar to everyone but nevertheless I wanted to share this.

UNDO tablespace size can be changed by taking a outage on the database or without it.

Below examples is considering the database is a two node RAC

The database has two UNDO tablespaces

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_data_files where tablespace_name like '%UNDO%' group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

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

UNDOTS2 25

UNDOTS1 25

With Outage

----------

It is fairly straight forward.

1. Shut down the database cleanly

conn / as sysdba

shut immediate;

2. From one of the database nodes you can do the below steps

SQL> sqlplus / as sysdba

SQL> startup mount;

SQL> drop tablespace UNDOTBS1;

SQL> drop tablespace UNDOTBS2;

SQL> create undo tablespace UNDOTBS1

datafile

'/dv/pper/ora_SCOTT_p1/' size 8600M,

'/dv/pper/ora_SCOTT_p2/' size 8600M

/

SQL> create undo tablespace UNDOTBS2

datafile

'/dv/pper/ora_SCOTT_p3/' size 8600M,

'/dv/pper/ora_SCOTT_p4/' size 8600M

/

Without Outage

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

When the database is up and running there will be sessions using the undo tablespace and hence you cannot drop and recreate the tablespace.

Hence we will have to switch the UNDO Tablespace

1. Create two undo tablespaces with smaller size just to handle the load until the activity is finished

create undo tablespace UNDOTBS3

datafile

'/dv/pper/ora_SCOTT_p6/' size 8600M

/

create undo tablespace UNDOTBS4

datafile

'/dv/pper/ora_SCOTT_p7/' size 8600M

/

2. Now alter the instances to use the new UNDO tablspaces

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

On Node 1

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS1

SQL> alter system set undo_tablespace = 'UNDOTBS3';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS3

On Node 2

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS2

SQL> alter system set undo_tablespace = 'UNDOTBS4';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS4

When these steps are performed already existing connections will continue to use the UNDOTS1 and UNDOTS2 but all the new sessions will start using

UNDOTBS3 and UNDOTBS4.

3. Drop tablespace and Recreate

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

You cannot drop the tablespace UNDOTBS1 and UNDOTBS2 until the sessions using them release the undo segments, if not you will get the error

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Use the below query to check if any UNDO segments are in use in the tablespaces UNDOTBS1 and UNDOTBS2 and clear them or wait till they get released

set lines 10000

column name format a10

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE a.usn = b.usn

AND a.usn = c.xidusn

AND c.ses_addr = d.saddr

AND a.name IN (

SELECT segment_name

FROM dba_segments

WHERE tablespace_name IN ('UNDOTBS1','UNDOTBS2')

);

You can also reduce the undo_retention so that the sessions releases the UNDO segments

On Node 1

---------

* sqlplus / as sysdba

SQL> drop tablespace UNDOTBS1; (Include "including contents and datafiles" if it is not RAW filesystem)

SQL> drop tablespace UNDOTBS2; (Include "including contents and datafiles" if it is not RAW filesystem)

SQL> create undo tablespace UNDOTBS3

datafile

'/dv/pper/ora_SCOTT_p1/' size 8600M,

'/dv/pper/ora_SCOTT_p2/' size 8600M

/

Tablespace created

SQL> create undo tablespace UNDOTBS4

datafile

'/dv/pper/ora_SCOTT_p3/' size 8600M,

'/dv/pper/ora_SCOTT_p4/' size 8600M

/

Tablespace created

4. Now alter the instances to use the RESIZED UNDO tablspaces

On Node 1

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS3

SQL> alter system set undo_tablespace = 'UNDOTBS4';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS4

On Node 2

---------

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS4

SQL> alter system set undo_tablespace = 'UNDOTBS2';

System altered

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS2

5. Check if any UNDO segments are in use in the tablespaces UNDOTBS3 and UNDOTBS4 and clear them or wait till they get released.

Then drop the tablespaces

SQL> drop tablespace UNDOTBS3;

Tablespace dropped

SQL> drop tablespace UNDOTBS4;

Tablespace dropped

--- Tanveer Madan