表空间的offline的用法:
(在本实验中,tablespace test1 由datafile 6\7 组成,每次offline tablespace之前都先将datafile 7 offline,版本11.2.0.4)
归档状态下:
1.offline normal:默认的offline模式,offline的时候会在表空间内部进行Check Point动作,保证表空间内部各个文件头上面的SCN一致。如果存在数据文件不能前推SCN,如已经Offline的情况,offline normal失效报错。
SQL> alter database datafile 7 offline;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> select CHECKPOINT_CHANGE#, OFFLINE_CHANGE#,FILE#,status from v$datafile where file# in (6,7);
CHECKPOINT_CHANGE# OFFLINE_CHANGE# FILE# STATUS
------------------ --------------- ---------- -------
2120006 2117740 6 ONLINE
2118055 2117740 7 RECOVER
SQL> select CHECKPOINT_CHANGE#,FUZZY,RECOVER,FILE#,STATUS,CHECKPOINT_TIME from v$datafile_header where file# in (6,7);
CHECKPOINT_CHANGE# FUZ REC FILE# STATUS CHECKPOIN
------------------ --- --- ---------- ------- ---------
2120006 YES NO 6 ONLINE 16-FEB-19
2118055 YES YES 7 OFFLINE 16-FEB-19
SQL> alter tablespace test1 offline;
alter tablespace test1 offline
*
ERROR at line 1:
ORA-01191: file 7 is already offline - cannot do a normal offline
ORA-01110: data file 7: '/u01/app/oradata/orcl/test12.dbf'
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oradata/orcl/test12.dbf'
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
2.offline temporary:比Normal要求略松的一种模式。Temporary模式下,Oracle依然会去“尝试”统一表空间内部文件头的SCN号。如果数据文件可以统一,就进行Check Point动作,如果文件不能统一,操作也不会报错,只是将其状态标记为不一致。Temporary模式下Offline的表空间Online的时候,那些“有问题”的不一致文件,是需要进行media recovey的。没有问题,打入check point的数据文件,就不需要进行恢复动作。
SQL> alter database datafile 7 offline;
Database altered.
SQL> alter tablespace test1 offline temporary;
Tablespace altered.
SQL> select CHECKPOINT_CHANGE#, OFFLINE_CHANGE#,FILE#,status from v$datafile;
CHECKPOINT_CHANGE# OFFLINE_CHANGE# FILE# STATUS
------------------ --------------- ---------- -------
2123089 1747805 1 SYSTEM
2123089 1747805 2 ONLINE
2123089 1747805 3 ONLINE
2123089 1747805 4 ONLINE
2123089 1747805 5 ONLINE
2124230 2117740 6 OFFLINE
2123089 2117740 7 RECOVER
7 rows selected.
SQL> select CHECKPOINT_CHANGE#,FUZZY,RECOVER,FILE#,STATUS,CHECKPOINT_TIME from v$datafile_header;
CHECKPOINT_CHANGE# FUZ REC FILE# STATUS CHECKPOIN
------------------ --- --- ---------- ------- ---------
2123089 YES NO 1 ONLINE 16-FEB-19
2123089 YES NO 2 ONLINE 16-FEB-19
2123089 YES NO 3 ONLINE 16-FEB-19
2123089 YES NO 4 ONLINE 16-FEB-19
2123089 YES NO 5 ONLINE 16-FEB-19
2124230 NO NO 6 OFFLINE 16-FEB-19
2123089 YES YES 7 OFFLINE 16-FEB-19
7 rows selected.
3.offline immediate:最松的一种offline模式。Immediate模式下,Oracle不会进行check point动作,无论有无问题的Datafile,都会被设置为需要Recover过程。在重新online的时候,表空间就需要进行重新的全表空media recover。
SQL> alter database datafile 7 offline;
Database altered.
SQL> alter tablespace test1 offline immediate;
Tablespace altered.
SQL> select CHECKPOINT_CHANGE#, OFFLINE_CHANGE#,FILE#,status from v$datafile;
CHECKPOINT_CHANGE# OFFLINE_CHANGE# FILE# STATUS
------------------ --------------- ---------- -------
2123089 1747805 1 SYSTEM
2123089 1747805 2 ONLINE
2123089 1747805 3 ONLINE
2123089 1747805 4 ONLINE
2123089 1747805 5 ONLINE
2124401 2117740 6 RECOVER
2124401 2117740 7 RECOVER
7 rows selected.
SQL> select CHECKPOINT_CHANGE#,FUZZY,RECOVER,FILE#,STATUS,CHECKPOINT_TIME from v$datafile_header;
CHECKPOINT_CHANGE# FUZ REC FILE# STATUS CHECKPOIN
------------------ --- --- ---------- ------- ---------
2123089 YES NO 1 ONLINE 16-FEB-19
2123089 YES NO 2 ONLINE 16-FEB-19
2123089 YES NO 3 ONLINE 16-FEB-19
2123089 YES NO 4 ONLINE 16-FEB-19
2123089 YES NO 5 ONLINE 16-FEB-19
2124401 YES YES 6 OFFLINE 16-FEB-19
2124401 YES YES 7 OFFLINE 16-FEB-19
7 rows selected.
因此在日常选择上,我们倾向严格的原则。因为非Normal方式的offline,都需要借助外部的redo log(可能还会用到archive log)进行media recover动作。所以选择的顺序是normal,temporary和immediate。
非归档状态下:
可以对表空间进行offline normal 、offline temporary,不能进行offline immediate。
对数据文件只能进行offline drop,不能进行offline。
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database datafile 7 offline drop;
Database altered.
SQL> alter tablespace test1 offline;
alter tablespace test1 offline
*
ERROR at line 1:
ORA-01191: file 7 is already offline - cannot do a normal offline
ORA-01110: data file 7: '/u01/app/oradata/orcl/test12.dbf'
SQL> alter tablespace test1 offline immediate;
alter tablespace test1 offline immediate
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter tablespace test1 offline temporary;
Tablespace altered.
(参考http://blog.itpub.net/17203031/viewspace-773828/)