oracle里tablespace offline和datafile offline的区别

There is a big difference between:
Taking the tablespace offline and Taking the datafiles offline

ALTER TABLESPACE ... OFFLINE
Does a checkpoint on the datafiles
Takes the datafiles offline
(从EM中可以看到tablespace offline有几种选项可供选择normal, temporary,immediate, for recovery,而在datafile中则没有这些选项)
view plaincopy to clipboardprint?
SQL>select dbms_flashback.get_system_change_number from dual;  
 
GET_SYSTEM_CHANGE_NUMBER  
 
------------------------  
 
27259186261  
 
 
SQL>alter tablespace block offline;  
 
表空间已更改  
 
 
SQL>select file#,name,checkpoint_change# from v$datafile;  
 
FILE#   NAME    CHECKPOINT_CHANGE#  
 
 
......  
 
13  F:\ORACLE\ORADATA\TEST\RMAN.DBF 27259134244  
 
14  F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF 27207207995  
 
15  F:\ORACLE\ORADATA\TEST\BLOCK01.DBF  27259186316  
 
 
SQL>select dbms_flashback.get_system_change_number from dual;  
 
GET_SYSTEM_CHANGE_NUMBER  
 
------------------------  
 
27259186335  
 
 
SQL>alter tablespace block online;  
 
表空间已更改  
 
 
SQL>select file#,name,checkpoint_change# from v$datafile;  
 
FILE#   NAME    CHECKPOINT_CHANGE#  
 
......  
 
13  F:\ORACLE\ORADATA\TEST\RMAN.DBF 27259134244  
 
14  F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF 27207207995  
 
15  F:\ORACLE\ORADATA\TEST\BLOCK01.DBF  27259186345  
 
 
SQL>select dbms_flashback.get_system_change_number from dual;  
 
GET_SYSTEM_CHANGE_NUMBER  
 
------------------------  
 
27259186378 
SQL>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

27259186261

SQL>alter tablespace block offline;

表空间已更改

SQL>select file#,name,checkpoint_change# from v$datafile;

FILE#   NAME    CHECKPOINT_CHANGE#

......

13  F:\ORACLE\ORADATA\TEST\RMAN.DBF 27259134244

14  F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF 27207207995

15  F:\ORACLE\ORADATA\TEST\BLOCK01.DBF  27259186316

SQL>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

27259186335

SQL>alter tablespace block online;

表空间已更改

SQL>select file#,name,checkpoint_change# from v$datafile;

FILE#   NAME    CHECKPOINT_CHANGE#

......

13  F:\ORACLE\ORADATA\TEST\RMAN.DBF 27259134244

14  F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF 27207207995

15  F:\ORACLE\ORADATA\TEST\BLOCK01.DBF  27259186345

SQL>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

27259186378
 

ALTER DATABASE DATAFILE ... OFFLINE
Does not perform. a checkpoint,
So that if the database is open, you may need to perform. media recovery when bringing it online.

view plaincopy to clipboardprint?
SQL>select file#,name,checkpoint_change# from v$datafile;  
FILE#  NAME   CHECKPOINT_CHANGE#  
......  
 
13 F:\ORACLE\ORADATA\TEST\RMAN.DBF27259134244  
14 F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF27207207995  
15 F:\ORACLE\ORADATA\TEST\BLOCK01.DBF 27259134244  
 
SQL>select dbms_flashback.get_system_change_number from dual;  
GET_SYSTEM_CHANGE_NUMBER  
------------------------  
27259186119  
 
SQL>alter database datafile 15 offline;  
数据库已更改  
 
SQL>select file#,name,checkpoint_change# from v$datafile;  
FILE#  NAME   CHECKPOINT_CHANGE#  
......  
 
13 F:\ORACLE\ORADATA\TEST\RMAN.DBF27259134244  
14 F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF27207207995  
15 F:\ORACLE\ORADATA\TEST\BLOCK01.DBF 27259134244  
 
SQL>select dbms_flashback.get_system_change_number from dual;  
GET_SYSTEM_CHANGE_NUMBER  
------------------------  
27259186180  
 
SQL>alter database datafile 15 online;  
ORA-01113:文件15需要介质恢复  
ORA-01110:数据文件15: 'F:\ORACLE\ORADATA\TEST\BLOCK01.DBF' 
 
SQL>recover datafile 15;  
完成介质恢复  
 
SQL>alter database datafile 15 online;  
数据库已更改  
 
SQL>select file#,name,checkpoint_change# from v$datafile;  
FILE#  NAME   CHECKPOINT_CHANGE#  
 
......  
 
13 F:\ORACLE\ORADATA\TEST\RMAN.DBF27259134244  
14 F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF27207207995  
15 F:\ORACLE\ORADATA\TEST\BLOCK01.DBF 27259186244 
SQL>select file#,name,checkpoint_change# from v$datafile;
FILE#  NAME   CHECKPOINT_CHANGE#
......

13 F:\ORACLE\ORADATA\TEST\RMAN.DBF27259134244
14 F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF27207207995
15 F:\ORACLE\ORADATA\TEST\BLOCK01.DBF 27259134244

SQL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
27259186119

SQL>alter database datafile 15 offline;
数据库已更改

SQL>select file#,name,checkpoint_change# from v$datafile;
FILE#  NAME   CHECKPOINT_CHANGE#
......

13 F:\ORACLE\ORADATA\TEST\RMAN.DBF27259134244
14 F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF27207207995
15 F:\ORACLE\ORADATA\TEST\BLOCK01.DBF 27259134244

SQL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
27259186180

SQL>alter database datafile 15 online;
ORA-01113:文件15需要介质恢复
ORA-01110:数据文件15: 'F:\ORACLE\ORADATA\TEST\BLOCK01.DBF'

SQL>recover datafile 15;
完成介质恢复

SQL>alter database datafile 15 online;
数据库已更改

SQL>select file#,name,checkpoint_change# from v$datafile;
FILE#  NAME   CHECKPOINT_CHANGE#

......

13 F:\ORACLE\ORADATA\TEST\RMAN.DBF27259134244
14 F:\ORACLE\ORADATA\TEST\MAGGIE_DATA1.DBF27207207995
15 F:\ORACLE\ORADATA\TEST\BLOCK01.DBF 27259186244
 

That is the reason why:

You cannot do 'alter database datafile ... offline' if you are in noarchivelog (but tablespace offline works)

You cannot do 'alter tablespace ... offline' if database is read-only (but datafile offline works)

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wh62592855/archive/2009/10/21/4709403.aspx

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22021749/viewspace-627068/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22021749/viewspace-627068/