Oracle12c: Moving Datafiles Online

Renaming or relocating Oracle datafiles prior to Oracle 11g was not straight forward process. You had to perform 4 steps.

1. Take the tablespace offline.

2. Rename or relocate using OS

3. Issue ” ALTER DATABASE RENAME FILE”  command. All that this command did was update  the control files.

4. Bring the tablespace online.

Starting with Oracle12c, All you need is one single command and the best thing is that this is done online. In fact this command will work only on online datafiles. The new command is “ALTER DATABASE MOVE DATAFILE ‘file1′ TO ‘file2′;

This command copies/renames the datafile to new location, updates the controlfile and deletes the old datafile. You also have options to retain the old datafile with “KEEP” option but as far the database goes, the retained file is not part of database.

See example below

15:52:56 SQL> create tablespace myts datafile ‘/mydb06/oradata/mydb/myts01.dbf’ size 20G;

Tablespace created.

Elapsed: 00:00:55.01

15:54:48 SQL> alter database move datafile ‘/mydb06/oradata/mydb/myts01.dbf’ to ‘/mydb05/oradata/mydb/myts01.dbf’;

Database altered.

Elapsed: 00:05:32.34 ==> 5 minutes to copy & rename the file

16:37:32 SQL> select name , status from  v$datafile where name like ‘%ts%’;

NAME                                                                            STATUS
———————————————————————————-
/mydb05/oradata/mydb/myts01.dbf                                  ONLINE

Elapsed: 00:00:00.02

Major Drawback

Renaming files using offline (pre-12c) approach is much faster if the datafiles need to be just renamed on the same filesystem and not relocated. With the new approach, Oracle makes a copy of datafile irrespective of  whether KEEP option is specified or not. This  can be achieved very fast with  OS “mv” command using the offline(pre-12c) approach. So if you can afford to take the files offline and all you want is rename and not relocate, use the offline approach as it is very fast.

16:37:51 SQL> alter database move datafile ‘/mydb05/oradata/mydb/myts01.dbf’ to ‘/mydb05/oradata/mudb/mytabs01.dbf’;

Database altered.

Elapsed: 00:05:46.57
16:54:26 SQL>
-bash-3.2$ ls -latr
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:08 myts01.dbf
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:51 mytabs01.dbf
-bash-3.2$

Things to know before using this command

Dataguard

Move operations on the primary or standby is independent of each other

Flashback

Flashback operations will  retain the new location of datafile irrespective of the flashback time.

OS

Old files are not deleted on Windows

About these ads

Tagged: , ,

One thought on “Oracle12c: Moving Datafiles Online

  1. […] Moving datafiles online […]

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 54 other followers

%d bloggers like this: