问题:

关于datafile offline后需要recover的疑问

小弟有些不解，为什么将datafile offline后需要recover才能重新将其online？recover做了哪些工作？
还有个问题，如以上图，我先讲表空间offline，此时相应的数据文件的状态也是offline的，在这种情况下将datafile联机不需要进行recover操作，但是如果我将datafile online后在offline，就需要recover才能重新online或使tablespace回到online状态，不是太懂这个recover操作意味着什么？求教~！

大米嗵嗵上传了这个附件：

2011-5-4 13:37

未命名1.jpg (106.5 KB)

2011-5-4 13:37

未命名2.jpg (49.04 KB)

----------------------------------------解答----------------------------------------------------

我以前写的一个文章

2.表空间offline online
SYS@ sun>alter tablespace users offline;

表空间已更改。

SYS@ sun>select b.FILE#,b.TS#,b.NAME,
  2       a.checkpoint_change# database_scn,
  3       b.checkpoint_change# datafile_scn,
  4       c.CHECKPOINT_CHANGE# start_scn,
  5       b.last_change#    shut_scn
  6 from v$database a, v$datafile b, v$datafile_header c
  7 where b.FILE# = c.FILE#;

   FILE#       TS# NAME                                                       DATABASE_SCN DATAFILE_SCN  START_SCN SHUT_SCN
---------- ---------- ------------------------------------------------------------ ------------ ------------ ---------- ----------
      1       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSTEM01.DBF             1672302879 1672302879 1672302879
      2       1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\UNDOTBS01.DBF          1672302879 1672302879 1672302879
      3       2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSAUX01.DBF             1672302879 1672302879 1672302879
      4       4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF             1672302879 1672302978       0 1672302978
               --shut_scn有值了，且shut_scn=datfile_scn但是start_scn为空
      5       6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\RCAT.DBF                1672302879 1672302879 1672302879

SYS@ sun>alter system checkpoint;

系统已更改。

SYS@ sun>select b.FILE#,b.TS#,b.NAME,
  2       a.checkpoint_change# database_scn,
  3       b.checkpoint_change# datafile_scn,
  4       c.CHECKPOINT_CHANGE# start_scn,
  5       b.last_change#    shut_scn
  6 from v$database a, v$datafile b, v$datafile_header c
  7 where b.FILE# = c.FILE#;

   FILE#       TS# NAME                                                       DATABASE_SCN DATAFILE_SCN  START_SCN SHUT_SCN
---------- ---------- ------------------------------------------------------------ ------------ ------------ ---------- ----------
      1       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSTEM01.DBF             1672303011 1672303011 1672303011
      2       1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\UNDOTBS01.DBF          1672303011 1672303011 1672303011
      3       2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSAUX01.DBF             1672303011 1672303011 1672303011
      4       4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF             1672303011 1672302978       0 1672302978
      5       6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\RCAT.DBF                1672303011 1672303011 1672303011

SYS@ sun>alter tablespace users online; --online表空间，不需要恢复
--查看日志
alter tablespace users online
Tue Dec 07 11:16:41 2010
Starting control autobackup
Tue Dec 07 11:16:41 2010
Control autobackup written to DISK device
handle 'D:\BACKUP\CONTROL_C-1841928616-20101207-07'             --oracle还自动备份了控制文件
Completed: alter tablespace users online

表空间已更改。

SYS@ sun>select b.FILE#,b.TS#,b.NAME,
  2       a.checkpoint_change# database_scn,
  3       b.checkpoint_change# datafile_scn,
  4       c.CHECKPOINT_CHANGE# start_scn,
  5       b.last_change#    shut_scn
  6 from v$database a, v$datafile b, v$datafile_header c
  7 where b.FILE# = c.FILE#;

   FILE#       TS# NAME                                                       DATABASE_SCN DATAFILE_SCN  START_SCN SHUT_SCN
---------- ---------- ------------------------------------------------------------ ------------ ------------ ---------- ----------
      1       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSTEM01.DBF             1672303011 1672303011 1672303011
      2       1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\UNDOTBS01.DBF          1672303011 1672303011 1672303011
      3       2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSAUX01.DBF             1672303011 1672303011 1672303011
      4       4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF             1672303011 1672303041 1672303041
      5       6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\RCAT.DBF                1672303011 1672303011 1672303011

SYS@ sun>alter system checkpoint;

系统已更改。

SYS@ sun>select b.FILE#,b.TS#,b.NAME,
  2       a.checkpoint_change# database_scn,
  3       b.checkpoint_change# datafile_scn,
  4       c.CHECKPOINT_CHANGE# start_scn,
  5       b.last_change#    shut_scn
  6 from v$database a, v$datafile b, v$datafile_header c
  7 where b.FILE# = c.FILE#;

   FILE#       TS# NAME                                                       DATABASE_SCN DATAFILE_SCN  START_SCN SHUT_SCN
---------- ---------- ------------------------------------------------------------ ------------ ------------ ---------- ----------
      1       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSTEM01.DBF             1672303068 1672303068 1672303068
      2       1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\UNDOTBS01.DBF          1672303068 1672303068 1672303068
      3       2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSAUX01.DBF             1672303068 1672303068 1672303068
      4       4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF             1672303068 1672303068 1672303068
      5       6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\RCAT.DBF                1672303068 1672303068 1672303068

SYS@ sun>

3.数据文件offline online
SYS@ sun>alter database datafile 4 offline;

数据库已更改。

SYS@ sun>select b.FILE#,b.TS#,b.NAME,
  2       a.checkpoint_change# database_scn,
  3       b.checkpoint_change# datafile_scn,
  4       c.CHECKPOINT_CHANGE# start_scn,
  5       b.last_change#    shut_scn
  6 from v$database a, v$datafile b, v$datafile_header c
  7 where b.FILE# = c.FILE#;

   FILE#       TS# NAME                                                       DATABASE_SCN DATAFILE_SCN  START_SCN SHUT_SCN
---------- ---------- ------------------------------------------------------------ ------------ ------------ ---------- ----------
      1       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSTEM01.DBF             1672303068 1672303068 1672303068
      2       1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\UNDOTBS01.DBF          1672303068 1672303068 1672303068
      3       2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSAUX01.DBF             1672303068 1672303068 1672303068
      4       4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF             1672303068 1672303068 1672303068 1672303267
-shut_scn有值了，但是datafile_scn=start_scn       5       6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\RCAT.DBF                1672303068 1672303068 1672303068

SYS@ sun>alter database datafile 4 online;
alter database datafile 4 online
*
第 1 行出现错误:
ORA-01113: 文件 4 需要介质恢复
ORA-01110: 数据文件 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF'

SYS@ sun>recover datafile 4;       -- datafile_scn=start_scn
完成介质恢复。
SYS@ sun>alter database datafile 4 online;

数据库已更改。

SYS@ sun>select b.FILE#,b.TS#,b.NAME,
  2       a.checkpoint_change# database_scn,
  3       b.checkpoint_change# datafile_scn,
  4       c.CHECKPOINT_CHANGE# start_scn,
  5       b.last_change#    shut_scn
  6 from v$database a, v$datafile b, v$datafile_header c
  7 where b.FILE# = c.FILE#;

   FILE#       TS# NAME                                                       DATABASE_SCN DATAFILE_SCN  START_SCN SHUT_SCN
---------- ---------- ------------------------------------------------------------ ------------ ------------ ---------- ----------
      1       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSTEM01.DBF             1672303068 1672303068 1672303068
      2       1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\UNDOTBS01.DBF          1672303068 1672303068 1672303068
      3       2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSAUX01.DBF             1672303068 1672303068 1672303068
      4       4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF             1672303068 1672303363 1672303363
      5       6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\RCAT.DBF                1672303068 1672303068 1672303068

SYS@ sun>alter system checkpoint;

系统已更改。

SYS@ sun>select b.FILE#,b.TS#,b.NAME,
  2       a.checkpoint_change# database_scn,
  3       b.checkpoint_change# datafile_scn,
  4       c.CHECKPOINT_CHANGE# start_scn,
  5       b.last_change#    shut_scn
  6 from v$database a, v$datafile b, v$datafile_header c
  7 where b.FILE# = c.FILE#;

   FILE#       TS# NAME                                                       DATABASE_SCN DATAFILE_SCN  START_SCN SHUT_SCN
---------- ---------- ------------------------------------------------------------ ------------ ------------ ---------- ----------
      1       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSTEM01.DBF             1672303380 1672303380 1672303380
      2       1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\UNDOTBS01.DBF          1672303380 1672303380 1672303380
      3       2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\SYSAUX01.DBF             1672303380 1672303380 1672303380
      4       4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\USERS01.DBF             1672303380 1672303380 1672303380
      5       6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUN\RCAT.DBF                1672303380 1672303380 1672303380

SYS@ sun>

================

总结

================

表空间offline 触发检查点
数据文件offline 不触发检查点

ITPub博客

tablespace offline 与datafile offline 区别

最新文章