Oracle Apps DBA

My photo
More than 5+ Years of IT Experience in administering Enterprise wide Multi Vendor UNIX Servers, Oracle Databases (8i to 11gR2), Middle-tiers, Applications and Clusters. I am a Sun Certified System Administrator (SCSA) for Solaris 10, oracle 10g database certified associate(OCA), Oracle 10g Database certified professional (OCP) and Oracle E-Business suite R12 certified professionl(OCP)

Oracle Database and Applications

Saturday, 29 June 2013

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> 



3 comments:

rakesh said...

Great...

Cindy Dy said...

Thank you for this post. Keep it up. Hope to read more post from you guys.

Viena
www.gofastek.com

Leslie Lim said...

It is great to have the opportunity to read a good quality article with useful information on topics that plenty are interested on.

www.imarksweb.org