一般，归档下offline tablespace有3种方式：normal(默认),immediate,temporary
Normal会对该表空间所有文件执行检查点，会将对应的db_buffer中的脏数据写到数据文件中，online时不需要recover，只需要将当前的scn写到数据文件头即可；
Immediate 不会对该表空间执行检查点，立即脱机，不会转储任何脏数据。所以online的时候需要应用日志做recover。只有文件受到损坏以至没法完成检查点时，通常才这么做；
Temporary 介于normal跟immediate之间，能执行检查点的执行检查点，不能执行检查点的(如文件损坏)就立即脱机，当然online时也相应的需要恢复。
数据文件的offline相当于immediate方式，不写检查点，但online时需要recover
archive mode
1. tablespace offline: normal(默认)
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
493517
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 493334
6 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 493334
6 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 493550
6 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 493623
6 rows selected.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
493647
2. 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 493623
6 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 493752
6 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 493752
6 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 493752
6 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 494331
6 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 494331
6 rows selected.