Moving UNDO tablespace to NEW ASM diskgroup in RAC

+++Change Undo tablespace to New BW_UNDO_01 disk group.

This can done by adding new UNDO tablespace and droping the old undo tablespace.

+ Adding undo for Node-1 

create undo tablespace UNDOTBS01 DATAFILE '+BW_UNDO_01' SIZE 8g;

alter tablespace UNDOTBS01 add datafile '+BW_UNDO_01' SIZE 8G;

alter tablespace UNDOTBS01 add datafile '+BW_UNDO_01' SIZE 8G;

alter tablespace UNDOTBS01 add datafile '+BW_UNDO_01' SIZE 8G;

+ Adding undo for Node-2

create undo tablespace UNDOTBS02 DATAFILE '+BW_UNDO_01' SIZE 8G;

alter tablespace UNDOTBS02 add datafile '+BW_UNDO_01' SIZE 8G;

alter tablespace UNDOTBS02 add datafile '+BW_UNDO_01' SIZE 8G;

alter tablespace UNDOTBS02 add datafile '+BW_UNDO_01' SIZE 8G;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS01 SCOPE=BOTH SID='TESTDB1';

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH SID='TESTDB2';

show parameter undo ==> this value should show the new undo tablespaces.

++ Check any old rollback segment was used by online.

select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS1' and status='ONLINE';

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 the above is zero rows then we can drop the old undo

DROP TABLESPACE undotbs1 ;

select file_name from dba_data_files where tablespace_name='UNDOTBS1';

++ For Node 2

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');

talbespace

DROP TABLESPACE undotbs2 ;

select file_name from dba_data_files where tablespace_name='UNDOTBS2';