oracle12在表空间管理中推出了一个新特性，在线移动数据文件ALTER DATABASE MOVE DATAFILE. 省去了过去offline mv online等工作，可以一气呵成。
本次实验环境为pdb数据库中的在线移动数据文件
1. 登录cdb数据库
本次实验环境为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博客 ” ，链接：https://blog.itpub.net/29047826/viewspace-1448754/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
