We were running out of space on an ASM DG and had to free up space ASAP. Since out UNDO tablespaces are sized at about 200gb and were on the ASM DG that was full, we decided to move them.
This was the primary database with a physical (data guard) standby, so I needed to turn off standby file management
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
Create a new undo file for the RAC node 01 in the new ASM disk group:
create undo tablespace UNDOTBS1A datafile '+ASM_DG_01/PRODPRM/undotbs1a.dbf' size 20G;
Alter to allocate the new undo file to RAC node 01:
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1A SCOPE=BOTH SID='PRODPRM1';
Since this is a multi-node RAC, we can take one node offline to remove all users attached to the old undo tablespace off RAC node 01:
srvctl stop instance PRODPRM1 –d PRODPRM –o immediate
To be safe, make sure the RAC node is down with:
srvctl status database –d PRODPRM
Also, check the alert log on this particular node to make sure it was a clean shutdown.
With the RAC node down, drop the data file:
drop tablespace UNDOTBS1 including contents and datafiles;
Bring up RAC node 01 and it will be using the new undo table space:
srvctl start instance PRODPRM1 –d PRODPRM
Turn on standby file management
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
