how to move undo datafile in running database without disturbing transactions?

Question

Unfortunately one UNDO data file was misplaced in wrong location when I was adding space. I want to move that file to correct location. As it is Production database, I don't want to disturb the ongoing transactions. Can I offline that particular undo data file, while moving... will Database work normally with zero data loss?

Version Of Oracle DB: 11.2.0.4.0

can any one suggest?

Answer 1

During database running and in 24/7 environment, you should need to create new undo tablespace with new location of undo datafile. After creating this newer tablespace, you can switch older to newer undo tablespace online without affecting any ongoing transactions.

Following example shows how to achieve your goal. Using this trick, you can avoid data loss.

SQL>create undo tablespace undotbs2 datafile '/yournewlocation/undotbs02.dbf' size 1000m;

Now set new undo tablespace as default undo tablespace using following command as SYSDBA in SQLPLUS
SQL> alter system set undo_tablespace= undotbs2 ;

After finishing above task you can drop old undotbs tablespace from database using following command.
SQL> drop tablespace undotbs including contents;