oracle表空間offline的三種方式差別

         oracle表空間offline的三種方式差別

一 offline 表空間注意事項

1 不能離線如下表空間

  system

  undo tablespace

  temporary tablespace

2 考慮下離線表空間對某些使用者是否有影響，比如某個使用者的預設表空間為你要離線的表空間。

二 alter tablespace ....offline 後面可跟參數 normal/temporary/immediate 

normal:

A tablespace can be taken offline normally if no error conditions exist 

for any of the datafiles of the tablespace. No datafile in the tablespace

can be currently offline as the result of a write error. When you specify 

OFFLINE NORMAL, the database takes a checkpoint for all datafiles of the 

tablespace as it takes them offline. NORMAL is the default

以上說明：

1 normal 是offline的預設方式

2 normal 對表空間的所有資料檔案執行檢查點操作，online表空間時不需要媒體恢複。

3 normal 方式離線表空間時，不應該有寫錯誤，表空間所有檔案應該online狀态。

-----------------------------------------------------------------------------

temporary:

A tablespace can be taken offline temporarily, even if there are 

error conditions for one or more files of the tablespace. When 

you specify OFFLINE TEMPORARY, the database takes offline the 

datafiles that are not already offline, checkpointing them as it 

does so.

If no files are offline, but you use the temporary clause, media 

recovery is not required to bring the tablespace back online. 

However, if one or more files of the tablespace are offline 

because of write errors, and you take the tablespace offline 

temporarily, the tablespace requires recovery before you can 

bring it back online.

1 offline temporary 表空間時，如果表空間中沒有offline的資料檔案，則online該表空間時不需要媒體恢複。

2 offline temporary 表空間時，不會對已經offline的資料檔案執行檢查點操作，僅僅對online的資料檔案執行檢查點操作

3 offline temporary 表空間時，對于離線表空間之前已經offline的資料檔案，則online該表空間時，offline資料檔案需要媒體恢複。

------------------------------------------------------------------

immediate:

A tablespace can be taken offline immediately, without the 

database taking a checkpoint on any of the datafiles. When you 

specify OFFLINE IMMEDIATE, media recovery for the tablespace is 

required before the tablespace can be brought online. You 

cannot take a tablespace offline immediately if the database is 

running in NOARCHIVELOG mode.

以上說明三點：

1 offline immediate  不會對表空間的任何檔案執行檢查點操作。

2 online 表空間時需要對所有資料檔案進行 media recovery

3 offline immediate 需要資料庫日志模式為歸檔

-----------------------------------------------------------------------------------------

注意事項

如果必須離線表空間，推薦使用offline normal 方式離線該表空間，因為該表空間online時不需要執行媒體恢複。

三 實驗

1 測試offline temporary

查詢系統目前表空間以及相應的資料檔案

SQL> select a.name as tablespace,b.file#,b.status,b.name as datafile from v$tablespace a,v$datafile b where a.ts#=b.ts#;

TABLESPACE                          FILE# STATUS  DATAFILE

------------------------------ ---------- ------- ------------------------------

SYSTEM                                  1 SYSTEM  /oracle/CRM2/CRM/system01.dbf

SYSAUX                                  3 ONLINE  /oracle/CRM2/CRM/sysaux01.dbf

USERS                                   4 ONLINE  /oracle/CRM2/CRM/users01.dbf

UNDOTBS2                                6 ONLINE  /oracle/CRM2/CRM/undotbs2.dbf

ZX                                      5 ONLINE  /oracle/CRM2/CRM/zx1.dbf

ZX                                      2 ONLINE  /oracle/CRM2/CRM/zx2.dbf

SQL> alter database datafile 2 offline;

Database altered.

不能用offline normal正常offline 因為表空間zx資料檔案2已經offline狀态

SQL> alter tablespace zx offline;

alter tablespace zx offline

*

ERROR at line 1:

ORA-01191: file 2 is already offline - cannot do a normal offline

ORA-01110: data file 2: '/oracle/CRM2/CRM/zx2.dbf'

用offline temporary 離線

SQL> alter tablespace zx offline temporary;

Tablespace altered.

SQL> alter tablespace zx online;

alter tablespace zx online

ORA-01113: file 2 needs media recovery

SQL> recover datafile 2;

Media recovery complete.

使zx表空間online

2 測試offline immediate

查詢目前表空間及其資料檔案。

SQL> alter tablespace zx offline immediate;

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/oracle/CRM2/CRM/zx1.dbf'

SQL> recover datafile 5;   

本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1033891，如需轉載請自行聯系原作者