简单的说，offline datafile不做检查点，online的时候需要恢复
而offline tablespace做检查点（注意这里指normal），online的时候不需要恢复
表空间offline
将表空间离线的语法中有三个子句可选，如下：
ALTER TABLESPACE tablespace_name OFFLINE [NORMAL,TEMPORARRY,IMMEDIATE]。
现在对这三个子句的作用及对数据库的影响做个比较。
【NORMAL 】
如果用NORMAL子句则会使表空间所属的数据文件的块的内容在系统全局区里的数据刷新到文件中，因此当你再次将表空间联机时不需要恢复表空间里的数据文件。这也是默认子句。
【IMMEDIATE】
如果你在语句中指定IMMEDIATE，则数据库不确定会对表空间所属数据文件进行检查点，这时就会造成离线文件与数据库不一致，所以当你需要将其联机时，就需要进行恢复操作。
【TEMPORARY】
如果在语法中指定TEMPORARY 子句，则数据库会运行检查点进程，会对离线表空间中的数据文件进行同步。但数据库不能确定会对所有数据文件进行。所以当你将表空间联机时，有可能会需要进行恢复。
比较以下三种状态对数据字典的影响
RMAN.DBF 文件offline
users 表空间offline
最后一次，users 表空间offline immediate
sys@FOX> alter database datafile 'D:\ORACLE\ORADATA\FOX\RMAN.DBF' offline;
Database altered.
--dump控制文件
sys@FOX> ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';
Session altered.
--dump数据文件
sys@FOX> ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 2';
Session altered.
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 2460183058 2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF 2460183058 2460183439 2460066227 RECOVER
6 rows selected.
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,creation_change#,status from
v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- ---------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 2460183058 2460066227 5 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 2460183058 2460066227 5042 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF 2460183058 2460066227 6491 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 2460183058 2460066227 6529 ONLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF 2460183058 2460066227 483025 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF 2460183058 2460183439 2460066227 1899464 RECOVER
6 rows selected.
从dump的cf文件可以看到文件8的 Stop scn: 0x0000.92a36b8f
sys@FOX> select to_number('92a36b8f','xxxxxxxx') from dual;
TO_NUMBER('92A36B8F','XXXXXXXX')
--------------------------------
2460183439
就是last_change#
sys@FOX> alter tablespace users offline;
Tablespace altered.
sys@FOX> select name,SCNBAS from ts$;
NAME SCNBAS
-------------------------------------------------- ----------
SYSTEM 0
UNDOTBS1 0
TEMP 0
INDX 0
TOOLS 0
USERS 2460183864
DEMO 0
TEST 0
RMAN 0
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 2460183058 2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 2460183864 2460183864 2460066227 OFFLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF 2460183058 2460183439 2460066227 RECOVER
6 rows selected.
sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 2460183058 1708
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 2460183058 1701
D:\ORACLE\ORADATA\FOX\INDX01.DBF NO YES 2460183058 1692
0 0
D:\ORACLE\ORADATA\FOX\DEMO01.DBF NO YES 2460183058 1661
D:\ORACLE\ORADATA\FOX\RMAN.DBF YES YES 2460183058 1637
6 rows selected.
tablespace offline normal时，生成了一个ts$.SCNBAS（脱机scn），对应于每个数据文件的结束SCN，基于ts$.SCNBAS（脱机scn）能够直接online
同时可以看到users表空间对应的checkpoint_change#=last_change#
会影响CONTROLFILE_CHANGE#
再来看offline immediate
没有生成脱机scn。也会影响CONTROLFILE_CHANGE#
sys@FOX> alter tablespace users offline immediate;
Tablespace altered.
sys@FOX> select name,SCNBAS from ts$;
NAME SCNBAS
-------------------------------------------------- ----------
SYSTEM 0
UNDOTBS1 0
TEMP 0
INDX 0
TOOLS 0
USERS 0
DEMO 0
TEST 0
RMAN 0
TESTING_LMT_ASSM 0
TEST_TBS 0
UNDOTBS2 0
TBS_2K 0
T_S 6455446
T_1 0
TEST1 0
TESTING_LMT_MSSM 0
TBS_TEST 0
18 rows selected.
同时可以看到last_change#>checkpoint_change#，这两个就是文件恢复的终点和起点
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 2460183058 2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 2460184060 2460184084 2460183864 RECOVER
D:\ORACLE\ORADATA\FOX\DEMO01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF 2460183058 2460183439 2460066227 RECOVER
6 rows selected.
sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 2460183058 1708
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 2460183058 1701
D:\ORACLE\ORADATA\FOX\INDX01.DBF NO YES 2460183058 1692
D:\ORACLE\ORADATA\FOX\USERS01.DBF YES YES 2460184060 1703
D:\ORACLE\ORADATA\FOX\DEMO01.DBF NO YES 2460183058 1661
D:\ORACLE\ORADATA\FOX\RMAN.DBF YES YES 2460183058 1637
6 rows selected.
sys@FOX> recover tablespace users;
Media recovery complete.
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 2460183058 2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 2460184084 2460184084 2460183864 OFFLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF 2460183058 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF 2460183058 2460183439 2460066227 RECOVER
6 rows selected.
sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 2460183058 1708
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 2460183058 1701
D:\ORACLE\ORADATA\FOX\INDX01.DBF NO YES 2460183058 1692
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO NO 2460184084 1704
D:\ORACLE\ORADATA\FOX\DEMO01.DBF NO YES 2460183058 1661
D:\ORACLE\ORADATA\FOX\RMAN.DBF YES YES 2460183058 1637
6 rows selected.
sys@FOX> alter tablespace users online;
Tablespace altered.
最后再看一下read only状态
也会生成脱机scn，同时users表空间对应的checkpoint_change#=last_change#
会影响CONTROLFILE_CHANGE#
sys@FOX> alter tablespace USERS read only;
Tablespace altered.
sys@FOX> select name,SCNBAS from ts$;
NAME SCNBAS
-------------------------------------------------- ----------
SYSTEM 0
UNDOTBS1 0
TEMP 0
INDX 0
TOOLS 0
USERS 2460184528
DEMO 0
TEST 0
RMAN 0
TESTING_LMT_ASSM 0
TEST_TBS 0
UNDOTBS2 0
TBS_2K 0
T_S 6455446
T_1 0
TEST1 0
TESTING_LMT_MSSM 0
TBS_TEST 0
18 rows selected.
sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 2460184522 1710
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 2460184522 1703
D:\ORACLE\ORADATA\FOX\INDX01.DBF NO YES 2460184522 1694
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO NO 2460184528 1708
D:\ORACLE\ORADATA\FOX\DEMO01.DBF NO YES 2460184522 1663
D:\ORACLE\ORADATA\FOX\RMAN.DBF YES YES 2460183058 1637
6 rows selected.
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 2460184522 2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 2460184522 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF 2460184522 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 2460184528 2460184528 2460183864 ONLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF 2460184522 2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF 2460183058 2460183439 2460066227 RECOVER
6 rows selected.
sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 2460184522 1710
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 2460184522 1703
D:\ORACLE\ORADATA\FOX\INDX01.DBF NO YES 2460184522 1694
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO NO 2460184528 1708
D:\ORACLE\ORADATA\FOX\DEMO01.DBF NO YES 2460184522 1663
D:\ORACLE\ORADATA\FOX\RMAN.DBF YES YES 2460183058 1637
6 rows selected.
sys@FOX> select checkpoint_change#,CONTROLFILE_CHANGE#,resetlogs_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# RESETLOGS_CHANGE#
------------------ ------------------- -----------------
2460184522 2460184528 2460066228
sys@FOX> alter system checkpoint;
System altered.
sys@FOX> select checkpoint_change#,CONTROLFILE_CHANGE#,resetlogs_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# RESETLOGS_CHANGE#
------------------ ------------------- -----------------
2460184615 2460184615 2460066228
sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 2460184615 1711
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 2460184615 1704
D:\ORACLE\ORADATA\FOX\INDX01.DBF NO YES 2460184615 1695
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO NO 2460184528 1708
D:\ORACLE\ORADATA\FOX\DEMO01.DBF NO YES 2460184615 1664
D:\ORACLE\ORADATA\FOX\RMAN.DBF YES YES 2460183058 1637
6 rows selected.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11088128/viewspace-692430/，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 【故障-ORACLE】OSWBB不能运行解决
- 【故障-ORACLE】‘ALTER SYSTEM SET service_names='' SCOPE=MEMORY SID=’解释
- 【故障-ORACLE】rdbms ipc message timeout解释
- 【故障-ORACLE】GGS_STICK出来大量锁的问题
- 【故障-ORACLE】定时清理dba_recyclebin
- 【转载-ORACLE】ORA-6512 During Full Export
- 【故障-ORACLE】_NEXT_OBJE Exclusive锁问题分析
- 【测试-RAC】CRS崩溃后快速改单节点启动
- 【转载-ORACLE】ORA-12547: TNS:lost contact
- 【故障-oracle】ORA-600[KQLINVOBJUSER]
