目录11G and before分为不停机和停机两种方式：一、不停机移动数据文件二、停机移动数据文件12C and later
11G and before
分为不停机和停机两种方式：
一、不停机移动数据文件
完整步骤：
1、确认开启归档模式
2、offline数据文件
3、物理层移动数据文件（可重命名）
4、逻辑层rename数据文件路径及名称
5、recover恢复数据文件
6、online数据文件
–开启归档模式SQL> archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination /archivelogOldest online log sequence 1Current log sequence 2SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> SQL> SQL> startup mountORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 452988064 bytesDatabase Buffers 1140850688 bytesRedo Buffers 7319552 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.–offline数据文件SQL> / FILE# NAME STATUS———- ———————————————————— ——- 1 /oradata/orcl11g/system01.dbf SYSTEM 2 /oradata/orcl11g/sysaux01.dbf ONLINE 3 /oradata/orcl11g/undotbs01.dbf ONLINE 4 /oradata/orcl11g/users01.dbf ONLINE 5 /oradata/orcl11g/example01.dbf ONLINE 6 /oradata/orcl11g/test01.dbf ONLINE 7 /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf ONLINE7 rows selected.SQL> alter database datafile 7 offline;Database altered.–物理层移动数据文件SQL> !mv /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf /oradata/orcl11g/test02.dbfSQL> !ls /oradata/orcl11g/test02.dbf/oradata/orcl11g/test02.dbf–逻辑层rename数据文件SQL> alter database rename file \’/oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf\’ to \’/oradata/orcl11g/test02.dbf\’;Database altered.–恢复数据文件SQL> recover datafile 7;Media recovery complete.–online数据文件SQL> alter database datafile 7 online;Database altered.SQL> select file#,name,status from v$datafile; FILE# NAME STATUS———- ———————————————————— ——- 1 /oradata/orcl11g/system01.dbf SYSTEM 2 /oradata/orcl11g/sysaux01.dbf ONLINE 3 /oradata/orcl11g/undotbs01.dbf ONLINE 4 /oradata/orcl11g/users01.dbf ONLINE 5 /oradata/orcl11g/example01.dbf ONLINE 6 /oradata/orcl11g/test01.dbf ONLINE 7 /oradata/orcl11g/test02.dbf ONLINE7 rows selected.
二、停机移动数据文件
完整步骤：
1、关闭数据库
2、物理层移动数据文件（可重命名）
3、开启数据库到mount
4、逻辑层rename数据文件路径及名称
5、开启数据库
–创建一个TEST表空间，发现建在了/oradata/ORCL11G/下，希望移动到/oradata/orcl11g/下SQL> create tablespace TEST;Tablespace created.SQL> select name from v$datafile;NAME——————————————————————————–/oradata/orcl11g/system01.dbf/oradata/orcl11g/sysaux01.dbf/oradata/orcl11g/undotbs01.dbf/oradata/orcl11g/users01.dbf/oradata/orcl11g/example01.dbf/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf–尝试在线移动数据文件SQL> alter database rename file \’/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf\’ to \’/oradata/orcl11g/test01.dbf\’;alter database rename file \’/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf\’ to \’/oradata/orcl11g/test01.dbf\’*ERROR at line 1:ORA-01511: error in renaming log/data filesORA-01121: cannot rename database file 6 – file is in use or recoveryORA-01110: data file 6: \’/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf\’–报错ORA-01121[oracle@orcl11g:/home/oracle]$ oerr ORA 0112101121, 00000, \”cannot rename database file %s – file is in use or recovery\”// *Cause: Attempted to use ALTER DATABASE RENAME to rename a// datafile that is online in an open instance or is being recovered.// *Action: Close database in all instances and end all recovery sessions.
明确无法在线移动数据文件，需要关闭数据库。
–操作系统层面移动数据文件，并且重命名[oracle@orcl11g:/oradata/ORCL11G/datafile]$ lltotal 102408-rw-r—– 1 oracle oinstall 104865792 Apr 15 20:55 o1_mf_test_j7jfm30c_.dbf[oracle@orcl11g:/oradata/ORCL11G/datafile]$ mv o1_mf_test_j7jfm30c_.dbf /oradata/orcl11g/control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf [oracle@orcl11g:/oradata/ORCL11G/datafile]$ mv o1_mf_test_j7jfm30c_.dbf /oradata/orcl11g/test01.dbf[oracle@orcl11g:/oradata/ORCL11G/datafile]$ ll /oradata/orcl11g/test01.dbf –开启数据库到mountSQL> startup mountORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 452988064 bytesDatabase Buffers 1140850688 bytesRedo Buffers 7319552 bytesDatabase mounted.–rename数据文件名称SQL> select name from v$datafile;NAME——————————————————————————–/oradata/orcl11g/system01.dbf/oradata/orcl11g/sysaux01.dbf/oradata/orcl11g/undotbs01.dbf/oradata/orcl11g/users01.dbf/oradata/orcl11g/example01.dbf/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf6 rows selected.SQL> alter database rename file \’/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf\’ to \’/oradata/orcl11g/test01.dbf\’;Database altered.SQL> select name from v$datafile;NAME——————————————————————————–/oradata/orcl11g/system01.dbf/oradata/orcl11g/sysaux01.dbf/oradata/orcl11g/undotbs01.dbf/oradata/orcl11g/users01.dbf/oradata/orcl11g/example01.dbf/oradata/orcl11g/test01.dbf6 rows selected.–开启数据库SQL> alter database open;Database altered.
12C and later
支持在线移动数据文件：
可参考：Online Move Datafile in Oracle Database 12c Release 1 (12.1)
语法如下：
ALTER DATABASE MOVE DATAFILE ( \’filename\’ | \’ASM_filename\’ | file_number ) [ TO ( \’filename\’ | \’ASM_filename\’ ) ] [ REUSE ] [ KEEP ]
以上就是Oracle移动数据文件不停机和停机两种方式详解的详细内容，更多关于Oracle移动数据文件的资料请关注悠久资源其它相关文章！
