oracle里tablespace offline和datafile offline的区别

现在的位置: 首页 > 综合 > 正文

oracle里tablespace offline和datafile offline的区别

2013年09月11日 ⁄ 综合 ⁄ 共 2761字 ⁄ 字号小中大 ⁄ 评论关闭

网上搜了几篇关于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中则没有这些选项)

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.

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)

【上篇】得到android系统的当前时间
【下篇】C# 数据库操作固定模板

作者: adventist

该日志由 adventist 于11年前发表在综合分类下，最后更新于 2013年09月11日.
转载请注明: oracle里tablespace offline和datafile offline的区别 | 学步园 +复制链接

抱歉!评论已关闭.

学步园

oracle里tablespace offline和datafile offline的区别

作者: adventist

书签

最新文章New

本站推荐

返回首页