Sometimes it is necessary to move a Datafile to another Location (eg. Mountpoint, ASM Diskgroup,…) due to Space issues, Hardware Replacement or for 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, eg.

% cp <Source> <Destination>

SQL> alter database rename file ‘<Source or File#>’ to ‘<Destination>’;

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

or using RMAN:

RMAN> connect target /

RMAN> run

{

backup as copy datafile <File#> format ‘<Destination>’;

switch datafile <File#> to datafilecopy '<Destination from previous Command>';

}

New in Oracle Database 12c:

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

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#> to ‘<Destination>’ [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.

12c new feature

相關文章