前言：
现时兄弟们对“oracle的物理文件”都比较关注，小伙伴们都想要剖析一些“oracle的物理文件”的相关内容。那么小编也在网摘上收集了一些有关“oracle的物理文件””的相关知识，希望各位老铁们能喜欢，大家一起来学习一下吧！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的物理文件
