oracle 資料檔案offline
===============================
按表空間---移動資料檔案(資料庫開啟，歸檔模式)
===============================
SQL> create tablespace t1 datafile '/u01/app/oracle/oradata/orcl/t1.dbf' size 1M;
SQL> create table test(id number) tablespace t1;
SQL> insert into test values(1);
SQL> commit;
SQL> select * from test;
ID
----------
1
SQL> select name from v$dbfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/t1.dbf
SQL> alter tablespace t1 offline;
SQL> col name for a45
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/u01/app/oracle/oradata/orcl/t1.dbf OFFLINE
[oracle@chen ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@chen orcl]$ mv t1.dbf /home/oracle/ttt.dbf
SQL> alter tablespace t1 rename datafile '/u01/app/oracle/oradata/orcl/t1.dbf' to '/home/oracle/ttt.dbf';
SQL> alter tablespace t1 online;
SQL> select status,name from v$datafile;
STATUS NAME
------- ---------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
ONLINE /home/oracle/ttt.dbf
SQL> select * from test;
ID
----------
1
================================================
按資料檔案---移動資料檔案(資料庫開啟，歸檔模式)
================================================
SQL> alter database datafile '/home/oracle/ttt.dbf' offline;
SQL> select status,name from v$datafile;
STATUS NAME
------- ---------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
RECOVER /home/oracle/ttt.dbf
[oracle@chen orcl]$ mv /home/oracle/ttt.dbf /u01/app/oracle/oradata/orcl/t1.dbf
SQL> alter database rename file '/home/oracle/ttt.dbf' to '/u01/app/oracle/oradata/orcl/t1.dbf';
SQL> recover datafile '/u01/app/oracle/oradata/orcl/t1.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/t1.dbf' online;
SQL> select status,name from v$datafile;
STATUS NAME
------- ---------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
ONLINE /u01/app/oracle/oradata/orcl/t1.dbf
SQL> select * from test;
ID
----------
1
===================================
非歸檔模式，關閉資料庫移動資料檔案
===================================
SQL> shutdown immediate
[oracle@chen ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@chen orcl]$ mv t1.dbf /home/oracle/ttt.dbf
SQL> startup mount
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/t1.dbf' to '/home/oracle/ttt.dbf';
SQL> alter database open;
SQL> col name for a45
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- --------------------------------------------- -------
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE
6 /home/oracle/ttt.dbf ONLINE
SQL> select * from test;
ID
----------
1
參考與：
http://blog.csdn.net/tianlesoftware/article/details/4899172
================
資料檔案offline
================
歸檔模式下
alter database datafile '/home/oracle/t1.dbf' offline;
和
alter database datafile '/home/oracle/t1.dbf' offline drop; 一樣
非歸檔模式下
只有
alter database datafile '/home/oracle/t1.dbf' offline drop;
沒有
alter database datafile '/home/oracle/t1.dbf' offline;
該命令不會刪除資料檔案，只是將資料檔案的狀態更改為recover。 offline drop命令相當於把一個資料檔案至於離線狀態，並且需要恢復，並非刪除資料檔案。
資料檔案的相關資訊還會存在資料字典和控制檔案中。
SQL> create tablespace card datafile '/home/oracle/card01.dbf' size 1M;
SQL> create user chen identified by chen default tablespace t1;
SQL> grant connect,resource,dba to chen;
SQL> create table test(id number);
SQL> declare
2 begin
3 for i in 1..5 loop
4 insert into test values(i);
5 end loop;
6 end;
7 /
SQL> col name for a45
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
SQL> alter database datafile '/home/oracle/t1.dbf' offline;
資料檔案變為recover狀態
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf RECOVER
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/home/oracle/t1.dbf'
recover狀態資料檔案不能直接online
SQL> alter database datafile '/home/oracle/t1.dbf' online;
alter database datafile '/home/oracle/t1.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/t1.dbf'
需要先恢復
恢復需要利用當前日誌或歷史歸檔
資料庫歸檔模式下，在刪除歸檔之前，可以進行恢復
非歸檔模式下，只有當前日誌沒有被覆蓋，才可以恢復
SQL> recover datafile 6;
Media recovery complete.
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf OFFLINE
SQL> alter database datafile '/home/oracle/t1.dbf' online;
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
無法刪除offline狀態的資料檔案
SQL> alter tablespace t1 add datafile '/home/oracle/t2.dbf' size 1M;
SQL> alter database datafile '/home/oracle/t1.dbf' offline;
SQL> alter tablespace t1 drop datafile '/home/oracle/t1.dbf';
alter tablespace t1 drop datafile '/home/oracle/t1.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace T1
SQL> alter tablespace t1 drop datafile '/home/oracle/t2.dbf';
==============
表空間offline
==============
表空間offline，表空間及其表空間內所以資料庫都處於offline狀態；
表空間online，表空間及其表空間內所以資料庫都處於online狀態；
表空間Offline時，資料檔案的SCN會被凍結，而且表空間的資料檔案offline/online時又會發生檔案檢查點，使單個資料檔案SCN和資料庫其他問題不一致。
表空間online時，Oracle會取得當前SCN，解凍offline檔案SCN，和當前SCN同步。
單純的offline datafile，將不會觸發檔案檢查點，只有針對offline tablespace的時候才會觸發檔案檢查點，這也是為什麼online datafile需要media recovery而online tablespace不需要。
SQL> alter tablespace t1 offline;
SQL> select tablespace_name,status from user_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
T1 OFFLINE
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
SQL> alter tablespace t1 online;
SQL> select tablespace_name,status from user_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
T1 ONLINE
7 rows selected.
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
6 rows selected.
參考與：
http://blog.csdn.net/tianlesoftware/article/details/4899172
http://blog.csdn.net/tianlesoftware/archive/2009/11/29/4898800.aspx
http://www.cnblogs.com/sopost/p/3589731.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-1415296/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OFFLINE和DROP資料檔案的理解
- 表空間OFFLINE和資料檔案OFFLINE的區別
- 表空間offline,資料檔案offline 的區別(ZT)
- 利用offline datafile檔案方式遷移資料
- 資料檔案OFFLINE的3種情況
- 資料檔案、表空間offline用法及區別
- 資料檔案offline後unusable索引造成的問題索引
- Oracle單個資料檔案損壞，在Rman命令裡設定表空間、資料檔案offline方式來恢復最方便Oracle
- Oracle 資料檔案回收Oracle
- Data Guard 主端OFFLINE資料檔案和表空間
- oracle資料庫移動資料檔案、日誌檔案和控制檔案Oracle資料庫
- [20160329]bbed修復offline的資料檔案.txt
- oracle 線上rename資料檔案Oracle
- Oracle 刪除資料檔案Oracle
- oracle 資料檔案遷移Oracle
- 收縮Oracle資料檔案Oracle
- oracle刪除資料檔案Oracle
- oracle 關於-資料檔案Oracle
- oracle資料檔案遷移Oracle
- ORACLE移動資料檔案Oracle
- ORACLE 收縮資料檔案Oracle
- oracle資料檔案大小限制Oracle
- 表空間與資料檔案的offline和online操作
- 資料檔案實驗操作datafile的create/offline/drop/rename等操作
- 轉載-表空間和資料檔案offline的影響分析
- 20160331資料檔案offline與open resetlogs
- online/offline 表空間和資料檔案需謹慎！
- oracle中移動控制檔案、資料檔案、日誌檔案Oracle
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- Oracle資料檔案和臨時檔案的管理Oracle
- oracle資料庫的配置檔案Oracle資料庫
- oracle資料檔案驗證工具Oracle
- ORACLE 重新命名資料檔案Oracle
- Oracle_遷移資料檔案Oracle
- 重建Oracle資料庫控制檔案Oracle資料庫
- Oracle資料檔案最大值Oracle
- 收縮ORACLE的資料檔案Oracle
- 轉：Oracle刪除資料檔案Oracle