12c 联机状态移动数据文件

oracle12在表空间管理中推出了一个新特性，在线移动数据文件ALTER DATABASE MOVE DATAFILE. 省去了过去offline  mv  online等工作，可以一气呵成。

本次实验环境为pdb数据库中的在线移动数据文件

1. 登录cdb数据库

[root@snow ~]# su - oracle

[oracle@snow ~]$ sqlplus / as sysdba

SYS@cdb > show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

2. 切换到pdb数据库

SYS@cdb > alter session set container=pdb1;

3. 查看pdb1数据库的数据文件路径

SYS@cdb > select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/cdb/pdb1/example01.dbf

/u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf

/u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf

/u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf

/u01/app/oracle/oradata/cdb/pdb1/system01.dbf

/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf

4. 移动数据文件到新路径/home/oracle

SYS@cdb > alter database move datafile '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf' to '/home/oracle/baymax_comp02.dbf';

SYS@cdb > select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/cdb/pdb1/system01.dbf

/u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf

/u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf

/u01/app/oracle/oradata/cdb/pdb1/example01.dbf

/home/oracle/baymax_comp02.dbf

/u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf

5.  确认一下数据文件的文件号file_id

SYS@cdb > col file_name for a60

SYS@cdb > select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME

---------- ------------------------------------------------------------

11 /u01/app/oracle/oradata/cdb/pdb1/example01.dbf

10 /u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf

9 /u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf

12 /u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf

8 /u01/app/oracle/oradata/cdb/pdb1/system01.dbf

13 /home/oracle/baymax_comp02.dbf

6.  使用keep关键字将/home/oracle/baymax_comp02.dbf移回原来的路径，并且在/home/oracle路径下保留一份。

SYS@cdb > alter database move datafile 13 to '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf' keep;

文件已经被移动回原来的位置

SYS@cdb > select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME

---------- ------------------------------------------------------------

8 /u01/app/oracle/oradata/cdb/pdb1/system01.dbf

9 /u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf

10 /u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf

11 /u01/app/oracle/oradata/cdb/pdb1/example01.dbf

13 /u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf

12 /u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf

6 rows selected.

/home/oracle路径下还保留一份数据文件

SYS@cdb > !ls -l /home/oracle/bay*

-rw-r----- 1 oracle oinstall 15736832 Mar 4 14:29 /home/oracle/baymax_comp02.dbf

对比一下新特性之前和现在的变化

如果用传统方式来做需要如下步骤

1.

alter tablespace baymax_comp offline;

2.

mv '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf' '/home/oracle/baymax_comp02.dbf'

3.

alter tablespace baymax_comp rename datafile '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf'

to '/home/oracle/baymax_comp02.dbf'

4.

alter tablespace baymax_comp online;

新特性只需一步

alter database move datafile '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf'

to '/home/oracle/baymax_comp02.dbf'

全文完

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29047826/viewspace-1448754/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29047826/viewspace-1448754/