oracle 12c new feature: moving online datafiles(在线移动数据文件)

在12c以前如果想移动表空间的数据文件首先要offline.再OS mv命令移动离线的数据文件到新路径，Recover datafile[ or tablespace or database] 再Online.从12c 开始可以移动Online 的数据文件了。

1)Move a data file into ORACLE ASM
2)Move datafiles from one type of storage to another
3)Make a tablespace read-only and move its datafiles to write-once storage
4)Renames or relocates an online datafile data file at operating system level
5)Enables users to access the file during move datafile operation
6)When you relocate a data file on the Windows platform, the original data file might be retained in the old location, even when the KEEP option is omitted.
...

take a example.

sys@ORA12C>select status,enabled,name,con_id from v$datafile;
STATUS  ENABLED    NAME                                                                  CON_ID
------- ---------- ------------------------------------------------------- --------------------
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf                               0
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf                           3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf                           3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf                       3
sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf'
  2  ;
Database altered.
sys@ORA12C>ho ls /u01/app/oracle/oradata/ora12c/pdb1/
ora_pdb1_users01.dbf  sysaux01.dbf  system01.dbf  temp01.dbf
sys@ORA12C>select status,enabled,name,con_id from v$datafile;
STATUS  ENABLED    NAME                                                                       CON_ID
------- ---------- ------------------------------------------------------- -------------------------
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf                                    0
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf                                3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf                                3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf                        3
sys@ORA12C>alter database move datafile  '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' keep;
Database altered.
sys@ORA12C>ho ls /u01/app/oracle/oradata/ora12c/pdb1/
ora_pdb1_users01.dbf  pdb1_users01.dbf  sysaux01.dbf  system01.dbf  temp01.dbf

c##anbob@ORA12C>conn / as sysdba
Connected.
sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf';
alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "10"

sys@ORA12C>select status,enabled,name,con_id from v$datafile;
STATUS  ENABLED    NAME                                                                CON_ID
------- ---------- --------------------------------------------------------------------------
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/system01.dbf                              1
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/sysaux01.dbf                              1
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf                             1
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf                      2
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/users01.dbf                               1
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf                      2
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf                         3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf                         3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf                     3
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/pdb2/system01.dbf                         4
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb2/sysaux01.dbf                         4
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/drmdb/system01.dbf                        5
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/drmdb/sysaux01.dbf                        5
13 rows selected.
sys@ORA12C>alter session set container=pdb1;
Session altered.
sys@ORA12C>select status,enabled,name,con_id from v$datafile;
STATUS  ENABLED    NAME                                                                CON_ID
------- ---------- --------------------------------------------------------------------------
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf                             0
SYSTEM  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf                         3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf                         3
ONLINE  READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf                     3
sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf';
alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf'
ORA-27038: created file already exists
Additional information: 1
sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' reuse;
Database altered.
复制

sys@ORA12C>select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- --------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0