Moving datafiles


Notice: get_currentuserinfo is deprecated since version 4.5.0! Use wp_get_current_user() instead. in /home/dbascrip/public_html/wp-includes/functions.php on line 4435

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

Move a datafile online in Oracle 12c

One of the new feature of Oracle 12c is relocating datafile online using the move command.

if you want to do a copy instead of a move, use the keep keyword and the old datafile will be retained.

Move a datafile with RMAN

Using RMAN is the best way before 12c to relocate a datafile with a minimum downtime. The only downtime is for switching from the old datafile to the new one and recover it. That means that the datafile size as no impact on the downtime.

Here is the situation before moving the datafile

The first step is to create a copy of your datafile in the new destination. Here, we will copy the USERS datafile to
C:\ORACLEXE\APP\ORACLE\ORADATA2\XE:

Now I have a copy of my datafile in the new destination, I have to switch from the old datafile to the new one. Of course this operation cannot be done online, I’ll have to put my tablespace offline, this is the begining of the downtime.

Now I can switch from the old datafile to the new one:

Now we have to recover the datafile:

And we put back the tablespace USERS online:

Here is the new situation:

The old datafile is kept as copy:

We can remove it as we no longer need it:

That’s it !

Move the system datafile

For the system datafile there is an additionnal step which must be performed :

Now I have to put my database in mount state:

Now we can switch to the new datafile and recover it:

Open the database:

Remove the old file:

Thank you for reading.

2 thoughts on “Moving datafiles

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.