How to re-create UNDO tablespace in Rac databases.

First, check what undo files in the tablespaces..

1)

set linesize 200;

col FILE_NAME for a80;

col TABLESPACE_NAME for a20;

select FILE_NAME,TABLESPACE_NAME,status,online_status from dba_data_files where tablespace_name like 'UNDO%';

check size of the tablespace...

 SELECT tablespace_name,

        SUM ( ( (bytes / 1024) / 1024) / 1024) size_gb,

        COUNT (file_id) no_of_datafiles

 FROM dba_data_files

 WHERE tablespace_name LIKE 'UNDOTBS1'

 GROUP BY tablespace_name;

2)

see what UNDO tablespace is currently used... 

show parameter UNDO_TABLESPACE;

second, check what segments are still tried to the UNDO...

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'

);

If no rows found.... then..

alter system set undo_tablespace=UNDOTBS2 scope=both SID='RAC_1'

drop tablespace UNDOTBS1 including contents and datafiles;

3) re-create undo tablespace again....

create undo tablespace UNDOTBS1 datafile '+DATA' size 10G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

     alter tablespace UNDOTBS1 add datafile '+DATIND' size 5G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

follow the same for the other undo too...

Hope this Helps...