Oracle移動數(shù)據(jù)文件不停機和停機兩種方式詳解
11G and before
分為不停機和停機兩種方式:
一、不停機移動數(shù)據(jù)文件
完整步驟:
1、確認開啟歸檔模式
2、offline數(shù)據(jù)文件
3、物理層移動數(shù)據(jù)文件(可重命名)
4、邏輯層rename數(shù)據(jù)文件路徑及名稱
5、recover恢復(fù)數(shù)據(jù)文件
6、online數(shù)據(jù)文件
--開啟歸檔模式 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數(shù)據(jù)文件 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. --物理層移動數(shù)據(jù)文件 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數(shù)據(jù)文件 SQL> alter database rename file '/oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf' to '/oradata/orcl11g/test02.dbf'; Database altered. --恢復(fù)數(shù)據(jù)文件 SQL> recover datafile 7; Media recovery complete. --online數(shù)據(jù)文件 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.
二、停機移動數(shù)據(jù)文件
完整步驟:
1、關(guān)閉數(shù)據(jù)庫
2、物理層移動數(shù)據(jù)文件(可重命名)
3、開啟數(shù)據(jù)庫到mount
4、邏輯層rename數(shù)據(jù)文件路徑及名稱
5、開啟數(shù)據(jù)庫
--創(chuàng)建一個TEST表空間,發(fā)現(xiàn)建在了/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 --嘗試在線移動數(shù)據(jù)文件 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.
明確無法在線移動數(shù)據(jù)文件,需要關(guān)閉數(shù)據(jù)庫。
--操作系統(tǒng)層面移動數(shù)據(jù)文件,并且重命名 [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 --開啟數(shù)據(jù)庫到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數(shù)據(jù)文件名稱 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. --開啟數(shù)據(jù)庫 SQL> alter database open; Database altered.
12C and later
支持在線移動數(shù)據(jù)文件:
可參考: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移動數(shù)據(jù)文件不停機和停機兩種方式詳解的詳細內(nèi)容,更多關(guān)于Oracle移動數(shù)據(jù)文件的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
oracle中存儲函數(shù)與存儲過程的區(qū)別介紹
這篇文章主要介紹了oracle中存儲函數(shù)與存儲過程的區(qū)別介紹,非常不錯,具有參考借鑒價值,需要的朋友可以參考下2017-10-10Windows系統(tǒng)下Oracle?11g完全卸載教程(推薦!)
安裝軟件是首要,雖然不是特別重要,但是錯誤的安裝也是相當費勁和抓狂的,所以這篇文章主要給大家介紹了關(guān)于Windows系統(tǒng)下Oracle?11g完全卸載的相關(guān)資料,文中通過圖文介紹的非常詳細,要的朋友可以參考下2024-06-06