表空间offline的三个参数：normal、temporary、immediate

联机文档Administrator's Guide-P14-15中这样描述这三个参数：
NORMAL
A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace
can be currently offline as the result of a write error. When you specify OFFLINE NORMAL, the database takes a
checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default.
TEMPORARY
A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY, the database takes offline the datafiles that are not already offline(有write error的数据文件已经offline), checkpointing them as it does so.
If no files are offline, but you use the temporary clause, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.
IMMEDIATE
A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.

自己试验如下：
SQL> col name for a30
SQL> select file#,ts#,status,name from v$datafile where ts#=8;
     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf
SQL> alter database datafile 6 offline;--数据文件的offline与表空间的offline不同，数据文件offline之后，马上状态就变为recover，一定需要介质恢复
Database altered.
SQL> select file#,ts#,status,name from v$datafile where ts#=8;
     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 RECOVER /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/oradata/orcl/tbsmn01.dbf'
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.

下面演示表空间的offline的三个参数

[offline normal]

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn offline normal;

Tablespace altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 OFFLINE /u01/oradata/orcl/tbsmn01.dbf
         7          8 OFFLINE /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn online;

Tablespace altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf

[offline temporary]

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf

SQL> alter database datafile 6 offline;--模拟其中一个数据文件write error导致的offline

Database altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 RECOVER /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn offline temporary;--offline temporary会对file 7执行检查点，所以online时file 7不需要恢复

Tablespace altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 RECOVER /u01/oradata/orcl/tbsmn01.dbf
         7          8 OFFLINE /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn online;
alter tablespace tbsmn online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/oradata/orcl/tbsmn01.dbf'

SQL> recover datafile 6;
Media recovery complete.
SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 OFFLINE /u01/oradata/orcl/tbsmn01.dbf
         7          8 OFFLINE /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn online;

Tablespace altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf

[offline immediate]

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf

SQL> alter database datafile 7 offline;

Database altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 RECOVER /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn offline immediate;--offline immediate对所有数据文件都不会执行检查点

Tablespace altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 RECOVER /u01/oradata/orcl/tbsmn01.dbf
         7          8 RECOVER /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn online;
alter tablespace tbsmn online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/oradata/orcl/tbsmn01.dbf'

SQL> recover datafile 6;
Media recovery complete.
SQL> alter tablespace tbsmn online;
alter tablespace tbsmn online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/oradata/orcl/tbsmn02.dbf'

SQL> recover datafile 7;
Media recovery complete.
SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 OFFLINE /u01/oradata/orcl/tbsmn01.dbf
         7          8 OFFLINE /u01/oradata/orcl/tbsmn02.dbf

SQL> alter tablespace tbsmn online;

Tablespace altered.

SQL> select file#,ts#,status,name from v$datafile where ts#=8;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ------------------------------
         6          8 ONLINE  /u01/oradata/orcl/tbsmn01.dbf
         7          8 ONLINE  /u01/oradata/orcl/tbsmn02.dbf

参考链接：
http://www.cnblogs.com/suredandan/archive/2012/09/03/2668599.html
http://hi.baidu.com/chinadm123/item/b9ecfe5f1bc5062895eb0505

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

转载于:http://blog.itpub.net/26524307/viewspace-1061155/