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: {=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: {=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备份。
