11G and before
分为不停机和停机两种方式：
一、不停机移动数据文件
完整步骤：
1、确认开启归档模式
2、offline数据文件
3、物理层移动数据文件（可重命名）
4、逻辑层rename数据文件路径及名称
5、recover恢复数据文件
6、online数据文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
--开启归档模式 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /archivelog Oldest online log sequence 1 Current log sequence 2 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup mount ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 452988064 bytes Database Buffers 1140850688 bytes Redo Buffers 7319552 bytes Database 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 ONLINE 7 rows selected. SQL> alter database datafile 7 offline; Database altered. --物理层移动数据文件 SQL> !mv /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf /oradata/orcl11g/test02.dbf SQL> !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 ONLINE 7 rows selected. |
二、停机移动数据文件
完整步骤：
1、关闭数据库
2、物理层移动数据文件（可重命名）
3、开启数据库到mount
4、逻辑层rename数据文件路径及名称
5、开启数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
--创建一个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 files ORA-01121: cannot rename database file 6 - file is in use or recovery ORA-01110: data file 6: '/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf' --报错ORA-01121 [oracle@orcl11g:/home/oracle]$ oerr ORA 01121 01121, 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. |
明确无法在线移动数据文件，需要关闭数据库。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
--操作系统层面移动数据文件，并且重命名 [oracle@orcl11g:/oradata/ORCL11G/datafile]$ ll total 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 --开启数据库到mount SQL> startup mount ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 452988064 bytes Database Buffers 1140850688 bytes Redo Buffers 7319552 bytes Database 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_.dbf 6 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.dbf 6 rows selected. --开启数据库 SQL> alter database open ; Database altered. |
12C and later
支持在线移动数据文件：
可参考：Online Move Datafile in Oracle Database 12c Release 1 (12.1)
语法如下：
|
1 2 3 |
ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number ) [ TO ( 'filename' | 'ASM_filename' ) ] [ REUSE ] [ KEEP ] |
以上就是Oracle移动数据文件不停机和停机两种方式详解的详细内容，更多关于Oracle移动数据文件的资料请关注服务器之家其它相关文章！
原文链接：https://juejin.cn/post/7035087468391038989
查看更多关于Oracle移动数据文件不停机和停机两种方式详解的详细内容...