offline datafile和offline tablespace的区别

offline datafile和offline tablespace的区别

简单的说，offline datafile不做检查点，online的时候需要恢复
而offline tablespace做检查点（注意这里指normal），online的时候不需要恢复

比较以下三种状态对数据字典的影响
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.

从dump的cf文件可以看到文件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.