Relocate Datafile under 11.2.0.4

Overview

This procedure documents a working method for moving a datafile under Oracle RDBMS 11.2.0.4. In Oracle 12c, this procedure is replaced by a much simpler and less intrusive one.

Caution

This procedure needs an outage on the affected tablespace.

The database has to be in ARCHIVELOG mode.

Scenario Set Up

To demonstrate this procedure, I ran the following command on the REPOS database:

create smallfile tablespace dummy   datafile '/tmp/dummy01.dbf'     size 5m     autoextend on       next 5m       maxsize unlimited;

To see where all of the data files are located, I ran the following command:

select name from v$datafile;

The result was:

Procedure

The following RMAN commands do:

1. Put the DUMMY tablespace offline
2. Create a copy of the data file in the correct location
3. Update the database to use the new data file
4. Recover the DUMMY tablespace
5. Bring the DUMMY tablespace online

sql 'alter tablespace dummy offline'; backup as copy datafile '/tmp/dummy01.dbf' format '/opt/oracle/app/oradata/repos/dummy01.dbf'; switch datafile '/tmp/dummy01.dbf' to copy; recover tablespace dummy; sql 'alter tablespace dummy online';