Relocate Datafile under 11.2.0.4

Contents

  1. 1 Overview
  2. 2 Caution
  3. 3 Scenario Set Up
  4. 4 Procedure

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:

NAME
/opt/oracle/app/oradata/repos/system01.dbf
/opt/oracle/app/oradata/repos/sysaux01.dbf
/opt/oracle/app/oradata/repos/undotbs01.dbf
/opt/oracle/app/oradata/repos/users01.dbf
/tmp/dummy01.dbf

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