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。复制
1.2 alter tablespace tbs1 offline temporary;
表空间内包含的数据文件异常时，比如某个数据文件已经offline时，使用该选项。
--将datafile6 offline SYS@EVA>alter database datafile 6 offline; --此时我们正常offline发现会报错 SYS@EVA>alter tablespace tbs1 offline; alter tablespace tbs1 offline * ERROR at line 1: ORA-01191: file 6 is already offline - cannot do a normal offline ORA-01110: data file 6: '/u01/app/oracle/oradata/EVA/tbs2.dbf' --使用temporary使表空间离线 SYS@EVA>alter tablespace tbs1 offline temporary; --此时表空间状态 SYS@EVA>select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE TBS1 OFFLINE --此时数据文件状态 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 110153686 110153699 110154064 6 /u01/app/oracle/oradata/EVA/tbs2.dbf RECOVER 110153686 110153699 110153699 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 OFFLINE 110154064 6 /u01/app/oracle/oradata/EVA/tbs2.dbf OFFLINE 110153699 这里我们发现v$datafile中datafile 6是recover状态的,此时我们online表空间时必然是需要我们去做recover的，后面讲到数据文件offline和online操作时会详细讲解。 --将表空间重新online SYS@EVA>alter tablespace tbs1 online; alter tablespace tbs1 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/u01/app/oracle/oradata/EVA/tbs2.dbf' SYS@EVA>recover datafile 6; Media recovery complete. SYS@EVA>alter tablespace tbs1 online; Tablespace altered.复制
1.3 alter tablespace tbs1 offline immediate;
将表空间立即脱机，并且不对任何数据文件设置检查点，因此online操作时需要recover
--将表空间立即脱机 SYS@EVA>alter tablespace tbs1 offline immediate; Tablespace altered. SYS@EVA>select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE TBS1 OFFLINE 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 RECOVER 110153686 110153699 110154306 6 /u01/app/oracle/oradata/EVA/tbs2.dbf RECOVER 110153686 110153699 110154306 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 OFFLINE 110154306 6 /u01/app/oracle/oradata/EVA/tbs2.dbf OFFLINE 110154306 此时我们看到v$datafile中表空间内的两个数据文件均为recover状态。 --将表空间重新online SYS@EVA>alter tablespace tbs1 online; alter tablespace tbs1 online * ERROR at line 1: ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/u01/app/oracle/oradata/EVA/tbs1.dbf' SYS@EVA>recover tablespace tbs1; Media recovery complete. SYS@EVA>alter tablespace tbs1 online; Tablespace altered.复制
2、数据文件的offline、online操作
语法：alter database datafile 6 offline [drop];
当数据库处于归档模式下offline drop和offline没什么区别，当数据库处于非归档模式时只能使用offline drop。
2.1归档模式下数据文件的offline、online操作
--数据库处于归档模式 SYS@EVA>archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/fast_recovery_area/eva Oldest online log sequence 109 Next log sequence to archive 111 Current log sequence 111 --直接将数据文件offline SYS@EVA>select GROUP#,SEQUENCE#,STATUS from v$log; --当前共有3组日志组 GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 112 INACTIVE 2 113 INACTIVE 3 114 CURRENT SYS@EVA>alter database datafile 6 offline; --将数据文件offline,并切换三次日志 Database altered. SYS@EVA>alter system switch logfile; System altered. SYS@EVA>alter system switch logfile; System altered. SYS@EVA>alter system switch logfile; System altered. 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 110155024 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 0 0 110155024 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 0 0 110155024 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 0 0 110155024 5 /u01/app/oracle/oradata/EVA/tbs1.dbf ONLINE 110153686 110153699 110155024 6 /u01/app/oracle/oradata/EVA/tbs2.dbf RECOVER 110153686 110153699 110154841 此时查看6号数据文件为recover状态 --重新将数据文件online SYS@EVA>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/app/oracle/oradata/EVA/tbs2.dbf' SYS@EVA>recover datafile 6; --由于是归档模式,直接recover ORA-00279: change 110154841 generated at 07/29/2024 16:36:00 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/eva/1_111_1173349032.dbf ORA-00280: change 110154841 for thread 1 is in sequence #111 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto --读取归档日志,选auto Log applied. Media recovery complete. SYS@EVA>alter database datafile 6 online; Database altered. --查看数据文件状态，我们发现offline数据文件并不会改变控制文件记录的OFFLINE_CHANGE#和ONLINE_CHANGE#的值，因为offline表空间会设置checkpoint，offline数据文件不会设置checkpoint。 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 110155289 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 0 0 110155289 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 0 0 110155289 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 0 0 110155289 5 /u01/app/oracle/oradata/EVA/tbs1.dbf ONLINE 110153686 110153699 110155289 6 /u01/app/oracle/oradata/EVA/tbs2.dbf ONLINE 110153686 110153699 110155289复制
2.2非归档模式下数据文件的offline、online操作
--数据库处于非归档模式 SYS@EVA>archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/fast_recovery_area/eva Oldest online log sequence 112 Current log sequence 114 --此时直接将数据文件offline发现报错 SYS@EVA>alter database datafile 6 offline; alter database datafile 6 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled --使用drop选项offline SYS@EVA>alter database datafile 6 offline drop; Database altered. --查看当前数据文件状态 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 110156292 2 /u01/app/oracle/oradata/EVA/sysaux01.dbf ONLINE 0 0 110156292 3 /u01/app/oracle/oradata/EVA/undotbs01.dbf ONLINE 0 0 110156292 4 /u01/app/oracle/oradata/EVA/users01.dbf ONLINE 0 0 110156292 5 /u01/app/oracle/oradata/EVA/tbs1.dbf ONLINE 110153686 110153699 110156292 6 /u01/app/oracle/oradata/EVA/tbs2.dbf RECOVER 110153686 110153699 110156292 可以看到6号数据文件处于recover状态，说明需要介质恢复才能正常online，此时如果redo日志没有被覆盖我们一样可以通过redo进行recover，但是如果此时redo被覆盖了，又处于非归档模式，那么使用常规手段肯定是无法恢复了。 --切换redo，尝试online数据文件 SYS@EVA>alter system switch logfile; System altered. SYS@EVA>alter system switch logfile; System altered. SYS@EVA>alter system switch logfile; System altered. SYS@EVA>alter database datafile 6 online; --online数据文件提示需要介质恢复 alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/u01/app/oracle/oradata/EVA/tbs2.dbf' SYS@EVA>recover datafile 6; --恢复时提示需要114号归档，但是归档不存在，此时陷入僵局，只能通过非常规手段强制开库。 ORA-00279: change 110156292 generated at 07/29/2024 17:12:48 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/eva/1_114_1173349032.dbf ORA-00280: change 110156292 for thread 1 is in sequence #114 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/eva/1_114_1173349032.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 此时如果想重新online数据文件只能通过BBED或者一些隐含参数来跳过redo,进行不完全恢复,会丢失被覆盖redo的数据。复制
3.总结
1.使用NORMAL参数可以确保在进行表空间置为online前不需要执行恢复操作。 2.使用TEMPORARY参数则允许表空间在出现问题时临时置为offline状态，之后在表空间置为online前只需要恢复那些出错的offline文件。 3.使用IMMEDIATE参数将表空间立即置为offline状态后，如果需要online，表空间包含的所有数据文件均需要recover。 4.归档模式下，recover所需要的日志都保留完整的情况下，数据文件可以完全恢复,无数据丢失。 5.非归档模式下，如果redo被覆盖，数据文件将无法正常恢复，需要通过特殊手段进行不完全恢复，会丢失部分数据。 6.生产环境中建议要开启归档模式，并做好定期rman备份。复制
最后修改时间：2024-08-01 12:18:24
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
筱悦星辰
9月前
评论
0当我们相信自己很好的时候，才会遇见更好的自己。
9月前
点赞 评论
还我至尊
9月前
评论
0normal | temporary | immediate]; 一般不用加这些选项吧
9月前
点赞 评论
相关阅读
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
578次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
527次阅读
2025-04-18 14:18:38
Oracle SQL 执行计划分析与优化指南
Digital Observer
484次阅读
2025-04-01 11:08:44
墨天轮个人数说知识点合集
JiekeXu
467次阅读
2025-04-01 15:56:03
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
464次阅读
2025-04-08 09:12:48
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
456次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
449次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
424次阅读
2025-04-22 00:13:51
Oracle 19c RAC更换IP实战，运维必看！
szrsu
412次阅读
2025-04-08 23:57:08
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
399次阅读
2025-04-17 17:02:24
