目錄

• 11G and before
• 分為不停機和停機兩種方式：
• 一、不停機移動數據文件
• 二、停機移動數據文件
• 12C and later

11G and before

分為不停機和停機兩種方式：

一、不停機移動數據文件

完整步驟：

1、確認開啟歸檔模式

2、offline數據文件

3、物理層移動數據文件（可重命名）

4、邏輯層rename數據文件路徑及名稱

5、recover恢復數據文件

6、online數據文件

--開啟歸檔模式
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 Buffers7319552 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
--offline數據文件
SQL> /
     FILE# NAME     STATUS
---------- ------------------------------------------------------------ -------
 1 /oradata/orcl11g/system01.dbfSYSTEM
 2 /oradata/orcl11g/sysaux01.dbfONLINE
 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.dbfSYSTEM
 2 /oradata/orcl11g/sysaux01.dbfONLINE
 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、開啟數據庫

--創建一個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.

明確無法在線移動數據文件，需要關閉數據庫。

--操作系統層面移動數據文件，并且重命名
[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 Buffers7319552 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)

語法如下：

ALTER DATABASE MOVE DATAFILE ( "filename" | "ASM_filename" | file_number )
 [ TO ( "filename" | "ASM_filename" ) ]
 [ REUSE ] [ KEEP ]

以上就是Oracle移動數據文件不停機和停機兩種方式詳解的詳細內容，更多關于Oracle移動數據文件的資料請關注其它相關文章！