ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转载-表空间和数据文件offline的影响分析

转载-表空间和数据文件offline的影响分析

原创 Linux操作系统 作者:浪漫双鱼 时间:2011-04-13 16:21:57 0 删除 编辑

简单的说,offline datafile不做检查点,online的时候需要恢复
offline tablespace做检查点(注意这里指normal),online的时候不需要恢复

表空间offline

将表空间离线的语法中有三个子句可选,如下:

ALTER TABLESPACE tablespace_name OFFLINE [NORMAL,TEMPORARRY,IMMEDIATE]

现在对这三个子句的作用及对数据库的影响做个比较。

【NORMAL 】

如果用NORMAL子句则会使表空间所属的数据文件的块的内容在系统全局区里的数据刷新到文件中,因此当你再次将表空间联机时不需要恢复表空间里的数据文件。这也是默认子句。

【IMMEDIATE】

如果你在语句中指定IMMEDIATE,则数据库不确定会对表空间所属数据文件进行检查点,这时就会造成离线文件与数据库不一致,所以当你需要将其联机时,就需要进行恢复操作。

【TEMPORARY】

如果在语法中指定TEMPORARY 子句,则数据库会运行检查点进程,会对离线表空间中的数据文件进行同步。但数据库不能确定会对所有数据文件进行。所以当你将表空间联机时,有可能会需要进行恢复。

 

比较以下三种状态对数据字典的影响
RMAN.DBF
文件offline
users   
表空间offline
最后一次,users 表空间offline immediate


sys@FOX> alter database datafile 'D:\ORACLE\ORADATA\FOX\RMAN.DBF' offline;

Database altered.

--dump
控制文件
sys@FOX> ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';

Session altered.
--dump
数据文件
sys@FOX> ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 2';

Session altered.

sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;

DNAME                                    CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF               2460183058                   2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF              2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF                2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF                   2460183058   2460183439      2460066227 RECOVER

6 rows selected.

sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,creation_change#,status from
 v$datafile;

DNAME                                    CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- ---------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF               2460183058                   2460066227             5 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF              2460183058                   2460066227             5042 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF                 2460183058                   2460066227             6491 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF                2460183058                   2460066227             6529 ONLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF                 2460183058                   2460066227           483025 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF                   2460183058   2460183439      2460066227          1899464 RECOVER

6 rows selected.

dumpcf文件可以看到文件8 Stop scn: 0x0000.92a36b8f
sys@FOX> select to_number('92a36b8f','xxxxxxxx') from dual;

TO_NUMBER('92A36B8F','XXXXXXXX')
--------------------------------
                      2460183439
就是last_change#

sys@FOX> alter tablespace users offline;

Tablespace altered.
sys@FOX> select name,SCNBAS from ts$;

NAME                                                   SCNBAS
-------------------------------------------------- ----------
SYSTEM                                                      0
UNDOTBS1                                                    0
TEMP                                                        0
INDX                                                        0
TOOLS                                                       0
USERS                                              2460183864
DEMO                                                        0
TEST                                                        0
RMAN                                                        0

sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;

DNAME                                    CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF               2460183058                   2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF              2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF                2460183864   2460183864      2460066227 OFFLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF                   2460183058   2460183439      2460066227 RECOVER

6 rows selected.

sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;

DNAME                                    REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF       NO  YES         2460183058             1708
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF      NO  YES         2460183058             1701
D:\ORACLE\ORADATA\FOX\INDX01.DBF         NO  YES         2460183058             1692
                                                                  0                0
D:\ORACLE\ORADATA\FOX\DEMO01.DBF         NO  YES         2460183058             1661
D:\ORACLE\ORADATA\FOX\RMAN.DBF           YES YES         2460183058             1637

6 rows selected.
tablespace offline normal
时,生成了一个ts$.SCNBAS(脱机scn),对应于每个数据文件的结束SCN,基于ts$.SCNBAS(脱机scn)能够直接online 
同时可以看到users表空间对应的checkpoint_change#=last_change#
会影响CONTROLFILE_CHANGE#

再来看offline immediate
没有生成脱机scn。也会影响CONTROLFILE_CHANGE#
sys@FOX> alter tablespace users offline immediate;

Tablespace altered.

sys@FOX> select name,SCNBAS from ts$;

NAME                                                   SCNBAS
-------------------------------------------------- ----------
SYSTEM                                                      0
UNDOTBS1                                                    0
TEMP                                                        0
INDX                                                        0
TOOLS                                                       0
USERS                                                       0
DEMO                                                        0
TEST                                                        0
RMAN                                                        0
TESTING_LMT_ASSM                                            0
TEST_TBS                                                    0
UNDOTBS2                                                    0
TBS_2K                                                      0
T_S                                                   6455446
T_1                                                         0
TEST1                                                       0
TESTING_LMT_MSSM                                            0
TBS_TEST                                                    0

18 rows selected.
同时可以看到last_change#>checkpoint_change#,这两个就是文件恢复的终点和起点
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;

DNAME                                    CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF               2460183058                   2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF              2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF                2460184060   2460184084      2460183864 RECOVER
D:\ORACLE\ORADATA\FOX\DEMO01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF                   2460183058   2460183439      2460066227 RECOVER

6 rows selected.

sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;

DNAME                                    REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF       NO  YES         2460183058             1708
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF      NO  YES         2460183058             1701
D:\ORACLE\ORADATA\FOX\INDX01.DBF         NO  YES         2460183058             1692
D:\ORACLE\ORADATA\FOX\USERS01.DBF        YES YES         2460184060             1703
D:\ORACLE\ORADATA\FOX\DEMO01.DBF         NO  YES         2460183058             1661
D:\ORACLE\ORADATA\FOX\RMAN.DBF           YES YES         2460183058             1637

6 rows selected.

sys@FOX> recover tablespace users;
Media recovery complete.
sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;

DNAME                                    CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF               2460183058                   2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF              2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF                2460184084   2460184084      2460183864 OFFLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF                 2460183058                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF                   2460183058   2460183439      2460066227 RECOVER

6 rows selected.

sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;

DNAME                                    REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF       NO  YES         2460183058             1708
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF      NO  YES         2460183058             1701
D:\ORACLE\ORADATA\FOX\INDX01.DBF         NO  YES         2460183058             1692
D:\ORACLE\ORADATA\FOX\USERS01.DBF        NO  NO          2460184084             1704
D:\ORACLE\ORADATA\FOX\DEMO01.DBF         NO  YES         2460183058             1661
D:\ORACLE\ORADATA\FOX\RMAN.DBF           YES YES         2460183058             1637

6 rows selected.

sys@FOX> alter tablespace users online;

Tablespace altered.

最后再看一下read only状态
也会生成脱机scn,同时users表空间对应的checkpoint_change#=last_change#
会影响CONTROLFILE_CHANGE#
sys@FOX>  alter tablespace USERS read only;

Tablespace altered.

sys@FOX> select name,SCNBAS from ts$;

NAME                                                   SCNBAS
-------------------------------------------------- ----------
SYSTEM                                                      0
UNDOTBS1                                                    0
TEMP                                                        0
INDX                                                        0
TOOLS                                                       0
USERS                                              2460184528
DEMO                                                        0
TEST                                                        0
RMAN                                                        0
TESTING_LMT_ASSM                                            0
TEST_TBS                                                    0
UNDOTBS2                                                    0
TBS_2K                                                      0
T_S                                                   6455446
T_1                                                         0
TEST1                                                       0
TESTING_LMT_MSSM                                            0
TBS_TEST                                                    0

18 rows selected.

sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;

DNAME                                    REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF       NO  YES         2460184522             1710
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF      NO  YES         2460184522             1703
D:\ORACLE\ORADATA\FOX\INDX01.DBF         NO  YES         2460184522             1694
D:\ORACLE\ORADATA\FOX\USERS01.DBF        NO  NO          2460184528             1708
D:\ORACLE\ORADATA\FOX\DEMO01.DBF         NO  YES         2460184522             1663
D:\ORACLE\ORADATA\FOX\RMAN.DBF           YES YES         2460183058             1637

6 rows selected.

sys@FOX> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;

DNAME                                    CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF               2460184522                   2460066227 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF              2460184522                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\INDX01.DBF                 2460184522                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF                2460184528   2460184528      2460183864 ONLINE
D:\ORACLE\ORADATA\FOX\DEMO01.DBF                 2460184522                   2460066227 ONLINE
D:\ORACLE\ORADATA\FOX\RMAN.DBF                   2460183058   2460183439      2460066227 RECOVER

6 rows selected.

sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;

DNAME                                    REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF       NO  YES         2460184522             1710
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF      NO  YES         2460184522             1703
D:\ORACLE\ORADATA\FOX\INDX01.DBF         NO  YES         2460184522             1694
D:\ORACLE\ORADATA\FOX\USERS01.DBF        NO  NO          2460184528             1708
D:\ORACLE\ORADATA\FOX\DEMO01.DBF         NO  YES         2460184522             1663
D:\ORACLE\ORADATA\FOX\RMAN.DBF           YES YES         2460183058             1637

6 rows selected.

sys@FOX> select checkpoint_change#,CONTROLFILE_CHANGE#,resetlogs_change# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# RESETLOGS_CHANGE#
------------------ ------------------- -----------------
        2460184522          2460184528        2460066228

sys@FOX> alter system checkpoint;

System altered.

sys@FOX> select checkpoint_change#,CONTROLFILE_CHANGE#,resetlogs_change# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# RESETLOGS_CHANGE#
------------------ ------------------- -----------------
        2460184615          2460184615        2460066228

sys@FOX> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;

DNAME                                    REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF       NO  YES         2460184615             1711
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF      NO  YES         2460184615             1704
D:\ORACLE\ORADATA\FOX\INDX01.DBF         NO  YES         2460184615             1695
D:\ORACLE\ORADATA\FOX\USERS01.DBF        NO  NO          2460184528             1708
D:\ORACLE\ORADATA\FOX\DEMO01.DBF         NO  YES         2460184615             1664
D:\ORACLE\ORADATA\FOX\RMAN.DBF           YES YES         2460183058             1637

6 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11088128/viewspace-692430/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    87
  • 访问量
    232580