The Geek Diary

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

by

Sometimes it is necessary to move a Datafile to another Location (eg. Mountpoint, ASM Diskgroup) 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 either using OS-Tools and SQL*PLUS, for example:

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

If Active Data Guard is used:

SQL> alter database database open;

or using RMAN:

RMAN> connect target /

RMAN> run
      {
      backup as copy datafile  format '';
      switch datafile  to datafilecopy '';
      }

or

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

Physical Standby Database is in Active Data Guard Mode (opened READ ONLY and Managed Recovery is running)

In Oracle 12c, it is now possible to online move a Datafile while Managed Recovery is running, i.e. 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.

You May Also Like