首页 / 数据库 / MySQL / Oracle 10g表空间联机脱机跟检查点的关系

一般，归档下offline tablespace有3种方式：normal（默认）,immediate,temporaryNormal会对该表空间所有文件执行检查点，会将对应的db_buffer中的脏数据写到数据文件中，online时不需要recover，只需要将当前的scn写到数据文件头即可；Immediate 不会对该表空间执行检查点，立即脱机，不会转储任何脏数据。所以online的时候需要应用日志做recover。只有文件受到损坏以至没法完成检查点时，通常才这么做；Temporary 介于normal跟immediate之间，能执行检查点的执行检查点，不能执行检查点的（如文件损坏）就立即脱机，当然online时也相应的需要恢复。

数据文件的offline相当于immediate方式，不写检查点，但online时需要recover
archive mode1. tablespace offline: normal（默认）
SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER
------------------------
                  493517SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:OracleORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           ONLINE              493334
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4933346 rows selected.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           ONLINE              493334
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4933346 rows selected.SQL> alter tablespace cqf offline normal;Tablespace altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           OFFLINE             493550
         6 C:ORACLEORADATARCCQF02.DBF           OFFLINE             4935506 rows selected.SQL> alter tablespace cqf online;Tablespace altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           ONLINE              493623
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4936236 rows selected.SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER
------------------------
                  4936472. tablespace offline: immediate
SQL> alter tablespace cqf offline immediate;Tablespace altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           RECOVER             493623
         6 C:ORACLEORADATARCCQF02.DBF           RECOVER             4936236 rows selected.SQL> alter tablespace cqf online;
alter tablespace cqf online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: "C:ORACLEORADATARCCQF01.DBF"
SQL> recover tablespace cqf;
Media recovery complete.
SQL> alter tablespace cqf online;Tablespace altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           ONLINE              493752
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4937526 rows selected.3. Datafile offline
SQL> alter database datafile 5 offline;Database altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           RECOVER             493752
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4937526 rows selected.SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: "C:ORACLEORADATARCCQF01.DBF"
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;Database altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              493334
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              493334
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              493334
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              493334
         5 C:ORACLEORADATARCCQF01.DBF           ONLINE              493876
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4937526 rows selected.Noarchive mode

表空间脱机只有normal模式，没有immediate的原因我们也能猜的到，没有开归档，online的时候就有可能没法完全应用自offline以来的日志（日志可能被覆盖）。跟检查点的关系同归档模式。
数据文件脱机只有offline drop模式，没有offline模式，原因同上。事实上，noarchivelog 下的数据文件脱机offline drop = offline for drop ，意思是这个文件以后不再需要，但物理层面上并没有真正删除，一般在某个数据文件丢失以至于没法open数据库的时候才做datafile offline。如果在数据库open的情况下做offline datafile，这种文件一般就没法再online了。不过只要redo日志没有切换一个循环（最初日志没被覆盖），还是可以实现online的。

SQL> alter database datafile 5 offline drop;Database altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              494175
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              494175
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              494175
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              494175
         5 C:ORACLEORADATARCCQF01.DBF           RECOVER             494331
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4943316 rows selected.SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: "C:ORACLEORADATARCCQF01.DBF"
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;Database altered.SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile;     FILE# NAME                                     STATUS CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------- ------------------
         1 C:ORACLEORADATARCSYSTEM01.DBF        SYSTEM              494175
         2 C:ORACLEORADATARCUNDOTBS01.DBF       ONLINE              494175
         3 C:ORACLEORADATARCSYSAUX01.DBF        ONLINE              494175
         4 C:ORACLEORADATARCUSERS01.DBF         ONLINE              494175
         5 C:ORACLEORADATARCCQF01.DBF           ONLINE              494626
         6 C:ORACLEORADATARCCQF02.DBF           ONLINE              4943316 rows selected.

1 2 下一页

指定LOB参数RETENTION,PCTVERSION的一个小BUG搞懂Oracle字符集的几个要点相关资讯      Oracle教程 

Oracle中纯数字的varchar2类型和  （07/29/2015 07:20:43） Oracle教程：Oracle中查看DBLink密  （07/29/2015 07:16:55） [Oracle] SQL*Loader 详细使用教程  （08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程  （07/29/2015 07:18:28） Oracle教程：ORA-25153 临时表空间  （07/29/2015 07:13:37） Oracle教程之管理安全和资源  （04/08/2013 11:39:32）

本文评论 查看全部评论 （0）

表情： 姓名： 匿名字数 <div id="footbar"> 版权所有©石家庄振强科技有限公司2024 <a href="https://beian.miit.gov.cn" target="_blank">冀ICP备08103738号-5</a> <a href="/storage/sitemap.xml">网站地图</a> </div> </div> <script type="text/javascript" src="//cpro.baidustatic.com/cpro/ui/cm.js"></script> <script> var _mtj = _mtj || []; (function () { var mtj = document.createElement("script"); mtj.src = "https://node12.aizhantj.com:21233/tjjs/?k=p2tceukth5c"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(mtj, s); })(); </script> </body> </html>