oracle表空间offline的三种方式区别

简介:

         oracle表空间offline的三种方式区别


一 offline 表空间注意事项
1 不能离线如下表空间
  system
  undo tablespace
  temporary tablespace
2 考虑下离线表空间对某些用户是否有影响,比如某个用户的默认表空间为你要离线的表空间。
二 alter tablespace ....offline 后面可跟参数 normal/temporary/immediate 
normal:
A tablespace can be taken offline normally if no error conditions exist 
for any of the datafiles of the tablespace. No datafile in the tablespace
can be currently offline as the result of a write error. When you specify 
OFFLINE NORMAL, the database takes a checkpoint for all datafiles of the 
tablespace as it takes them offline. NORMAL is the default
以上说明:
1 normal 是offline的默认方式
2 normal 对表空间的所有数据文件执行检查点操作,online表空间时不需要介质恢复。
3 normal 方式离线表空间时,不应该有写错误,表空间所有文件应该online状态。
-----------------------------------------------------------------------------
temporary:
A tablespace can be taken offline temporarily, even if there are 
error conditions for one or more files of the tablespace. When 
you specify OFFLINE TEMPORARY, the database takes offline the 
datafiles that are not already offline, checkpointing them as it 
does so.
If no files are offline, but you use the temporary clause, media 
recovery is not required to bring the tablespace back online. 
However, if one or more files of the tablespace are offline 
because of write errors, and you take the tablespace offline 
temporarily, the tablespace requires recovery before you can 
bring it back online.

以上说明:
1 offline temporary 表空间时,如果表空间中没有offline的数据文件,则online该表空间时不需要介质恢复。
2 offline temporary 表空间时,不会对已经offline的数据文件执行检查点操作,仅仅对online的数据文件执行检查点操作
3 offline temporary 表空间时,对于离线表空间之前已经offline的数据文件,则online该表空间时,offline数据文件需要介质恢复。
------------------------------------------------------------------

immediate:
A tablespace can be taken offline immediately, without the 
database taking a checkpoint on any of the datafiles. When you 
specify OFFLINE IMMEDIATE, media recovery for the tablespace is 
required before the tablespace can be brought online. You 
cannot take a tablespace offline immediately if the database is 
running in NOARCHIVELOG mode.
以上说明三点:
1 offline immediate  不会对表空间的任何文件执行检查点操作。
2 online 表空间时需要对所有数据文件进行 media recovery
3 offline immediate 需要数据库日志模式为归档
-----------------------------------------------------------------------------------------
注意事项
如果必须离线表空间,推荐使用offline normal 方式离线该表空间,因为该表空间online时不需要执行介质恢复。

三 实验

1 测试offline temporary

查询系统当前表空间以及相应的数据文件
SQL> select a.name as tablespace,b.file#,b.status,b.name as datafile from v$tablespace a,v$datafile b where a.ts#=b.ts#;

TABLESPACE                          FILE# STATUS  DATAFILE
------------------------------ ---------- ------- ------------------------------
SYSTEM                                  1 SYSTEM  /oracle/CRM2/CRM/system01.dbf
SYSAUX                                  3 ONLINE  /oracle/CRM2/CRM/sysaux01.dbf
USERS                                   4 ONLINE  /oracle/CRM2/CRM/users01.dbf
UNDOTBS2                                6 ONLINE  /oracle/CRM2/CRM/undotbs2.dbf
ZX                                      5 ONLINE  /oracle/CRM2/CRM/zx1.dbf
ZX                                      2 ONLINE  /oracle/CRM2/CRM/zx2.dbf

SQL> alter database datafile 2 offline;

Database altered.
不能用offline normal正常offline 因为表空间zx数据文件2已经offline状态
SQL> alter tablespace zx offline;
alter tablespace zx offline
*
ERROR at line 1:
ORA-01191: file 2 is already offline - cannot do a normal offline
ORA-01110: data file 2: '/oracle/CRM2/CRM/zx2.dbf'
用offline temporary 离线
SQL> alter tablespace zx offline temporary;

Tablespace altered.

SQL> alter tablespace zx online;
alter tablespace zx online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/oracle/CRM2/CRM/zx2.dbf'

SQL> recover datafile 2;
Media recovery complete.

使zx表空间online
SQL> alter tablespace zx online;

Tablespace altered.

2 测试offline immediate

查询当前表空间及其数据文件。
SQL> select a.name as tablespace,b.file#,b.status,b.name as datafile from v$tablespace a,v$datafile b where a.ts#=b.ts#;

TABLESPACE                          FILE# STATUS  DATAFILE
------------------------------ ---------- ------- ------------------------------
SYSTEM                                  1 SYSTEM  /oracle/CRM2/CRM/system01.dbf
SYSAUX                                  3 ONLINE  /oracle/CRM2/CRM/sysaux01.dbf
USERS                                   4 ONLINE  /oracle/CRM2/CRM/users01.dbf
UNDOTBS2                                6 ONLINE  /oracle/CRM2/CRM/undotbs2.dbf
ZX                                      5 ONLINE  /oracle/CRM2/CRM/zx1.dbf
ZX                                      2 ONLINE  /oracle/CRM2/CRM/zx2.dbf

SQL> alter tablespace zx offline immediate;

Tablespace altered.

SQL> alter tablespace zx online;
alter tablespace zx online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/oracle/CRM2/CRM/zx2.dbf'


SQL> recover datafile 2;
Media recovery complete.
SQL> alter tablespace zx online;
alter tablespace zx online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/CRM2/CRM/zx1.dbf'


SQL> recover datafile 5;   
Media recovery complete.
SQL> alter tablespace zx online;

Tablespace altered.










本文转自 zhangxuwl 51CTO博客,原文链接:http://blog.51cto.com/jiujian/1033891,如需转载请自行联系原作者
目录
相关文章
|
4天前
|
SQL Oracle 关系型数据库
避坑,Oracle中left join 与 (+) 的区别
避坑,Oracle中left join 与 (+) 的区别
|
4天前
|
Oracle 关系型数据库
oracle asm 磁盘显示offline
oracle asm 磁盘显示offline
55 2
|
4天前
|
存储 Oracle NoSQL
Oracle 表空间、数据文件、schema的关系
Oracle 表空间、数据文件、schema的关系
47 2
|
4天前
|
Oracle 关系型数据库 MySQL
mysql数据库和Oracle的区别
mysql数据库和Oracle的区别
54 1
|
6月前
|
SQL Oracle 关系型数据库
MySQL和Oracle的一些区别
MySQL和Oracle的一些区别
|
7月前
|
Oracle 关系型数据库 数据库
9-4 Oracle管理表空间和数据文件
9-4 Oracle管理表空间和数据文件
|
8月前
|
SQL 监控 Oracle
Oracle创建和管理表空间
Oracle创建和管理表空间
63 1
|
4天前
|
SQL Oracle 关系型数据库
Oracle 数据泵导出导入(映射表空间、Schema)
Oracle 数据泵导出导入(映射表空间、Schema)
|
4天前
|
Oracle 关系型数据库
oracle 修改表空间文件路径方法
oracle 修改表空间文件路径方法
|
4天前
|
Oracle 关系型数据库 数据库
Oracle系列之五:Oracle表空间
Oracle系列之五:Oracle表空间