Moving datafiles online in 12c

I want to share with you a very good new feature in 12c , you can now move “datafiles” ONLINE while applications are performing DML operations.

In 11g we used to put tablespace offline, then use the operating system “mv” command to move the datafile from one directory to another then use the command:

ALTER TABLESPACE RENAME ‘/orad08/oradbd14/DUMMY_SM.ORA’ TO ‘/orad07/oradbd14/DUMMY_SM.ORA’;

The drawback was that there is an outage while performing this operation.

Now, in 12c you can perform this operation ONLINE whit no issues.

To illustrate:

A tablepsace TS_DUMMY is currently having a datafile under orad07 directory

tablespace dummy

Executing the command:

SQL> ALTER DATABASE MOVE DATAFILE ‘/orad07/oradbd14/DUMMY_SM.ORA’ TO ‘/orad10/oradbd14/DUMMY_SM.ORA’;

alter database command

NOW the data file has been relocated after the successful execution of the command:

dummy after re-location

Another option is using the same command with the keyword “KEEP” which copies the datafile to the destination directory but keeps the datafile in original directory. However, the default tablespace will reflect the new moved data file !

SQL> ALTER DATABASE MOVE DATAFILE ‘/orad10/oradbd14/DUMMY_SM.ORA’ TO ‘/orad09/oradbd14/DUMMY_SM.ORA’ KEEP;

keep option

2 datafiles exist in both directories

datafile both exists

Default datafile will be the moved one

default data file

You can’t do that with TEMP datafile or Archive,…etc

trying to move tmep datafile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s