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
2) Rename from normal location to ASM
3) Move from one ASM location to another
4) Move from ASM to Non-ASM
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>
Comments