表空间、数据文件offline和online操作

1、表空间的offline、online操作

语法：alter tablespace tbs1 offline [normal | temporary | immediate]; 默认normal

1.1 alter tablespace tbs1 offline normal; --normal为默认选项，可不加。

表空间及包含的数据文件均为正常状态时使用。

--新建一个表空间,添加两个数据文件 SYS@EVA>create tablespace tbs1 datafile '/u01/app/oracle/oradata/EVA/tbs1.dbf' size 10m; SYS@EVA>alter tablespace tbs1 add datafile '/u01/app/oracle/oradata/EVA/tbs2.dbf' size 10m; --查询当前数据文件状态(由于v$datafile和v$datafile_header信息来源不同,通常情况下我们需要两个同时查询,查看是否有异常情况) SYS@EVA>select FILE#,NAME,STATUS,OFFLINE_CHANGE#,ONLINE_CHANGE#,CHECKPOINT_CHANGE# from v$datafile; --信息来自控制文件 FILE# NAME STATUS OFFLINE_CHANGE# ONLINE_CHANGE# CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ------- --------------- -------------- ------------------ 1 /u01/app/oracle/oradata/EVA/system01.dbf SYSTEM 0 0 110152826 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 0 0 110152826 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 0 0 110152826 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 0 0 110152826 5 /u01/app/oracle/oradata/EVA/tbs1.dbf ONLINE 0 0 110152826 6 /u01/app/oracle/oradata/EVA/tbs2.dbf ONLINE 0 0 110152826 SYS@EVA>select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile_header; --信息来自数据文件头 FILE# NAME STATUS CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ------- ------------------ 1 /u01/app/oracle/oradata/EVA/system01.dbf ONLINE 110152826 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 110152826 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 110152826 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 110152826 5 /u01/app/oracle/oradata/EVA/tbs1.dbf ONLINE 110152826 6 /u01/app/oracle/oradata/EVA/tbs2.dbf ONLINE 110152826 此时可以看到检查点SCN值均为一直状态,并且OFFLINE_CHANGE#和ONLINE_CHANGE#值都为0,说明没有进行offline、online操作过 --查询当前表空间状态 SYS@EVA>select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE TBS1 ONLINE --使用normal模式offline表空间 SYS@EVA>alter tablespace tbs1 offline normal; --查询数据文件状态 SYS@EVA>select FILE#,NAME,STATUS,OFFLINE_CHANGE#,ONLINE_CHANGE#,CHECKPOINT_CHANGE# from v$datafile; FILE# NAME STATUS OFFLINE_CHANGE# ONLINE_CHANGE# CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ------- --------------- -------------- ------------------ 1 /u01/app/oracle/oradata/EVA/system01.dbf SYSTEM 0 0 110152826 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 0 0 110152826 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 0 0 110152826 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 0 0 110152826 5 /u01/app/oracle/oradata/EVA/tbs1.dbf OFFLINE 0 0 110152914 6 /u01/app/oracle/oradata/EVA/tbs2.dbf OFFLINE 0 0 110152914 SYS@EVA>select FILE#,NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile_header; FILE# NAME STATUS CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ------- ------------------ 1 /u01/app/oracle/oradata/EVA/system01.dbf ONLINE 110152826 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 110152826 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 110152826 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 110152826 5 OFFLINE 0 6 OFFLINE 0 --查询表空间状态 SYS@EVA>select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE TBS1 OFFLINE 此时我们可以看出数据文件和表空间的状态均为OFFLINE --将表空间重新online SYS@EVA>alter tablespace tbs1 online; --数据文件状态 SYS@EVA>select FILE#,NAME,STATUS,OFFLINE_CHANGE#,ONLINE_CHANGE#,CHECKPOINT_CHANGE# from v$datafile; FILE# NAME STATUS OFFLINE_CHANGE# ONLINE_CHANGE# CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ------- --------------- -------------- ------------------ 1 /u01/app/oracle/oradata/EVA/system01.dbf SYSTEM 0 0 110152826 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 0 0 110152826 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 0 0 110152826 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 0 0 110152826 5 /u01/app/oracle/oradata/EVA/tbs1.dbf ONLINE 110152914 110153324 110153324 6 /u01/app/oracle/oradata/EVA/tbs2.dbf ONLINE 110152914 110153324 110153324 此时我们可以看到OFFLINE_CHANGE#和ONLINE_CHANGE#都有值,并且ONLINE_CHANGE#的值等于online操作时数据库的当前SCN值,以上信息显示比其他数据文件大是因为SCN还缓存在内存中,只需要手工checkpoint一下就全部变成一致了。 当oracle数据库将表空间offline操作时，会对表空间内所有数据文件设置检查点，因此在进行online操作时无需recover就可以正常online。