0

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?

Neelima
  • 27
  • 1
  • 6

2 Answers2

2

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;
doc123
  • 106
  • 6
1

I would consider creating the undo tablespace as a BIGFILE datafile:

CREATE BIGFILE UNDO TABLESPACE UNDOTBS02 
    DATAFILE '/yournewlocation/UNDOTBS02.dbf' 
    SIZE 100M AUTOEXTEND ON NEXT 100M 
    MAXSIZE 500G; --or whatever size you consider sufficient for your DB

I would also alter the system with a scope of BOTH, to make sure the change is made in both memory and spfile:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH;

Then, provided all active transactions and the UNDO_RETENTION period (if any) are done, you'll be able to drop the tablespace as described by doc123.

Ramon Rey
  • 171
  • 1
  • 3