tablespace offline的4种方式
-normal
对属于该表空间的所有数据文件做检查点并且offline，说明两点：
1.表空间所有文件不能有错
2.表空间所有文件都要online
-temporary
对属于表空间且非offline的文件做检查点并且offline，online的时候要对原先已经offline的文件做 recover
-immediate
都不做检查点
非归档不能用
-for recover
10g已经取消
实验一：表空间有一个文件offline，则不能采用offline normal来脱机表空间
sys@FOX> alter database datafile 9 offline drop;
Database altered.
sys@FOX> alter tablespace tools offline;
alter tablespace tools offline
*
ERROR at line 1:
ORA-01191: file 9 is already offline - cannot do a normal offline
ORA-01110: data file 9: 'D:\TOOL02.DBF'
实验二：表空间有一个文件错误，则不能采用offline normal来脱机表空间
sys@FOX> alter tablespace tools offline;
alter tablespace tools offline
*
ERROR at line 1:
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: 'D:\TOOL02.DBF'
ORA-01251: Unknown File Header Version read for file number 9
实验三：表空间有一个文件损坏，但是还是online状态，不能做offline temporary
做完全检查点后，oracle会将其置为offline状态，这时就可以使用temporary选项了
sys@FOX> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# NAME
----- ------- ------------------ ---------------------------------------------
1 SYSTEM 2460064082 D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF
2 ONLINE 2460064082 D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF
3 ONLINE 2460064082 D:\ORACLE\ORADATA\FOX\INDX01.DBF
4 ONLINE 2460064082 D:\ORACLE\ORADATA\FOX\TOOLS01.DBF
5 ONLINE 2460064082 D:\ORACLE\ORADATA\FOX\USERS01.DBF
6 ONLINE 2460064082 D:\ORACLE\ORADATA\FOX\DEMO01.DBF
7 ONLINE 2460064082 D:\BACKUP_DIR\TESTING_LMT.DBF
8 ONLINE 2460064082 D:\ORACLE\ORADATA\FOX\RMAN.DBF
9 ONLINE 2460064082 D:\TOOL02.DBF
9 rows selected.
sys@FOX> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;
FILE# CHECKPOINT_CHANGE# REC FUZ
----- ------------------ --- ---
1 2460064082 NO YES
2 2460064082 NO YES
3 2460064082 NO YES
4 2460064082 NO YES
5 2460064082 NO YES
6 2460064082 NO YES
7 2460064082 NO YES
8 2460064082 NO YES
9 2460064082 YES
9 rows selected.
sys@FOX> alter tablespace tools offline;
alter tablespace tools offline
*
ERROR at line 1:
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: 'D:\TOOL02.DBF'
ORA-01251: Unknown File Header Version read for file number 9
sys@FOX> alter tablespace tools offline temporary;
alter tablespace tools offline temporary
*
ERROR at line 1:
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: 'D:\TOOL02.DBF'
ORA-01251: Unknown File Header Version read for file number 9
sys@FOX> alter system checkpoint;
System altered.
sys@FOX> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# NAME
----- ------- ------------------ ---------------------------------------------
1 SYSTEM 2460064551 D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF
2 ONLINE 2460064551 D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF
3 ONLINE 2460064551 D:\ORACLE\ORADATA\FOX\INDX01.DBF
4 ONLINE 2460064551 D:\ORACLE\ORADATA\FOX\TOOLS01.DBF
5 ONLINE 2460064551 D:\ORACLE\ORADATA\FOX\USERS01.DBF
6 ONLINE 2460064551 D:\ORACLE\ORADATA\FOX\DEMO01.DBF
7 ONLINE 2460064551 D:\BACKUP_DIR\TESTING_LMT.DBF
8 ONLINE 2460064551 D:\ORACLE\ORADATA\FOX\RMAN.DBF
9 RECOVER 2460064082 D:\TOOL02.DBF
9 rows selected.
sys@FOX> select FILE#,checkpoint_change#,recover, fuzzy,status from v$datafile_header;
FILE# CHECKPOINT_CHANGE# REC FUZ STATUS
----- ------------------ --- --- -------
1 2460064551 NO YES ONLINE
2 2460064551 NO YES ONLINE
3 2460064551 NO YES ONLINE
4 2460064551 NO YES ONLINE
5 2460064551 NO YES ONLINE
6 2460064551 NO YES ONLINE
7 2460064551 NO YES ONLINE
8 2460064551 NO YES ONLINE
9 2460064082 YES OFFLINE
9 rows selected.
sys@FOX> alter tablespace tools offline temporary;
Tablespace altered.
理论参考官方文档
阅读(3136) | 评论(0) | 转发(0) |
