Rename/move datafile online with oracle database 12c

Rename/Move data file online - This is the first new feature I am demonstration on Oracle database 12c new features. This is really a wonderful feature.

You cannot only move datafiles  form one location to another on normal file system, you can even move your datafiles  from NON-ASM to ASM Diskgrups:

In this article I will demonstrate rename/move :

1) Normal rename of datafille
2) Rename from normal location to ASM
3) Move from one ASM location to another
4) Move from ASM to Non-ASM


1) Normal rename of datafille

SQL> SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/prod_data/my_data01.dbf         ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE



6 rows selected.

SQL> alter database move datafile
  2  '/u01/12cDb_Home/oradata/prod/prod_data/my_data01.dbf'    
  3  to 
  4  '/u01/12cDb_Home/oradata/prod/mydata01.dbf';

Database altered.

SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/mydata01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE

6 rows selected.

SQL> 

2) Rename from normal location to ASM

SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/mydata01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE

6 rows selected.

SQL>



SQL> alter database move datafile
  2  '/u01/12cDb_Home/oradata/prod/mydata01.dbf'
  3  to
  4  '+DBDATA/prod/mydata01.dbf';

Database altered.

SQL> alter database move datafile
  2  '/u01/12cDb_Home/oradata/prod/users01.dbf'
  3  to 
  4  '+DBDATA/prod/users01.dbf';

Database altered.

SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/mydata01.dbf                                    ONLINE
+DBDATA/prod/users01.dbf                                     ONLINE

6 rows selected.

SQL> 

 3) Move from one ASM location to another

SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/mydata01.dbf                                    ONLINE
+DBDATA/prod/users01.dbf                                     ONLINE
6 rows selected.



SQL> alter database move datafile 
  2  '+DBDATA/prod/mydata01.dbf'
  3  to '+DBDATA/prod/prod_data/mydata01.dbf';

Database altered.

SQL> alter database move datafile
  2  '+DBDATA/prod/users01.dbf'
  3  to
  4  '+DBDATA/prod/prod_data/users01.dbf';
Database altered.

SQL>  select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/prod_data/mydata01.dbf                          ONLINE
+DBDATA/prod/prod_data/users01.dbf                           ONLINE

6 rows selected.

SQL> 

4) Move from ASM to Non-ASM

SQL>  select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/prod_data/mydata01.dbf                          ONLINE
+DBDATA/prod/prod_data/users01.dbf                           ONLINE

6 rows selected.

SQL> alter database move datafile
  2  '+DBDATA/prod/prod_data/mydata01.dbf'
  3  to 
  4  '/u01/12cDb_Home/oradata/prod/mydata01.dbf';

Database altered.

SQL> alter database move datafile
  2  '+DBDATA/prod/prod_data/users01.dbf'
  3  to 
  4  '/u01/12cDb_Home/oradata/prod/users01.dbf';

Database altered.
SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/mydata01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE

6 rows selected.

SQL> 


SQL>