How to create undo tablespace in oracle RAC

Oracle RAC

1) Create Undo tablespace UNDOTBS01

SQL> create undo tablespace UNDOTBS01 DATAFILE ‘+DATAGROUP’ SIZE 4g;
Tablespace created.
SQL> @asm
NAME TOTAL_GB FREE_GB


DATAGROUP 249.995117 11.46875
IDXGROUP 149.99707 7.55761719

2) Create Undo tablespace UNDOTBS02

SQL> create undo tablespace UNDOTBS02 DATAFILE ‘+DATAGROUP’ SIZE 2G;
Tablespace created.

3) Assign the Newly created UNDOTBS01 tablespace to node1

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS01 SCOPE=BOTH SID=’test1′;
System altered.

3) Assign the Newly created UNDOTBS02 tablespace to node2

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH SID=’test2′;
System altered.

SQL>

SQL> show parameter undo

NAME TYPE VALUE
óóóóóóóóóóóó ñ
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS02
SQL>

To drop tablespace UNDOTBS1(old Undo) see its segment status

SQL> 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 = ‘UNDOTBS1’
);
NAME STATUS USERNA SID SERIAL#


_SYSSMU82$ PENDING OFFLINE SCOTT 10 16007
_SYSSMU241$ PENDING OFFLINE SCOTT 17 30587
_SYSSMU34$ PENDING OFFLINE SCOTT 31 22303
_SYSSMU9$ PENDING OFFLINE SCOTT 33 10379
_SYSSMU22$ PENDING OFFLINE SCOTT 42 1131
_SYSSMU105$ PENDING OFFLINE SCOTT 45 58502
_SYSSMU234$ PENDING OFFLINE SCOTT 46 44824

wait for some time and execute again

QL> SELECT a.name,b.status , d.username , d.sid , d.serial#
2 FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
3 WHERE a.usn = b.usn
4 AND a.usn = c.xidusn
5 AND c.ses_addr = d.saddr
6 AND a.name IN (
7 SELECT segment_name
8 FROM dba_segments
9 WHERE tablespace_name = ‘UNDOTBS1’
10 );
SQL> 0 rows

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace ëUNDOTBS1í is currently in use

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′ and status=’ONLINE’;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS


PUBLIC _SYSSMU103$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU198$ UNDOTBS1 ONLINE

After sometime executed the same query

SQL> /

no rows selected

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′ and status=’ONLINE’
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
And same for UNDOTBS2.