How to move a Datafile to a different Location on Physical Standby Database

Sometimes it is necessary to move a Datafile to another Location (eg. Mountpoint, ASM Diskgroup, etc.) due to Space issues, Hardware Replacement, or Performance Reasons. There are several Possibilities to perform this Task explained below:

Physical Standby Database is mounted

We can copy and rename the Datafile while Managed Recovery is stopped. If the Physical Standby Database is opened READ ONLY you have to shutdown/startup mount first (and open again after renaming). You can use OS-Tools and SQL*PLUS, for example:

Using OS command:

$ cp [Source] [Destination]

Using SQL:

SQL> alter database rename file '[Path of Source datafilename or File#]' to '[Destination path and name]’;

SQL> alter database database open;      ### If Active Data Guard is used

or using RMAN:

RMAN> connect target /

RMAN> run
      {
      backup as copy datafile [File#] format '[Destination path and name]';
      switch datafile [File#] to datafilecopy '[Destination path from above backup command]';
      }

or

RMAN> run { backup as copy datafile [file#] format '[destination Path and name]'; }
RMAN> Switch datafile <file> to copy ;</file>

Physical Standby Database is in Active Data Guard Mode (opened READ ONLY and Managed Recovery is running) [New 12c Feature]

It is now possible to online move a Datafile while Managed Recovery is running, ie. the Physical Standby Database is in Active Data Guard Mode. You can use this command to move the Datafile:

SQL> alter database move datafile [File# or path and filename] to '[Destination path and name] [keep];

The ‘keep’ option will also keep the original Datafile, without this Option the File gets automatically deleted once the move completed.

The Destination can also be an ASM Diskgroup, of course if you want to move a Datafile to ASM or from one Diskgroup to another.