欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20190312]视图v$datafile字段OFFLINE_CHANGE#, ONLINE_CHANGE#.txt

程序员文章站 2022-05-23 08:58:40
[20190312]视图v$datafile字段OFFLINE_CHANGE#, ONLINE_CHANGE#.txt--//视图v$datafile存在2个字段OFFLINE_CHANGE#, ONLINE_CHANGE#,想当然会认为数据文件offline时记录scn号的改变.--//真的吗?通 ......

[20190312]视图v$datafile字段offline_change#, online_change#.txt

--//视图v$datafile存在2个字段offline_change#, online_change#,想当然会认为数据文件offline时记录scn号的改变.
--//真的吗?通过例子说明:

1.环境:
sys@book> @ ver1
port_string                    version        banner
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/linux 2.4.xx            11.2.0.4.0     oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

sys@book> select file#, checkpoint_change#,checkpoint_time,unrecoverable_change#,unrecoverable_time,last_change#,last_time, offline_change#, online_change#,first_nonlogged_scn,first_nonlogged_time,status,name from v$datafile;
file# checkpoint_change# checkpoint_time     unrecoverable_change# unrecoverable_time  last_change# last_time           offline_change# online_change# first_nonlogged_scn first_nonlogged_tim status  name
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
    1        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     system  /mnt/ramdisk/book/system01.dbf
    2        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     online  /mnt/ramdisk/book/sysaux01.dbf
    3        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     online  /mnt/ramdisk/book/undotbs01.dbf
    4        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     online  /mnt/ramdisk/book/users01.dbf
    5        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     online  /mnt/ramdisk/book/example01.dbf
    6        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     online  /mnt/ramdisk/book/tea01.dbf
6 rows selected.

--//我重建了控制文件,许多字段当前是空的.

2.测试:
sys@book> alter database datafile 6 offline ;
database altered.

sys@book>  select file#, checkpoint_change#,checkpoint_time,unrecoverable_change#,unrecoverable_time,last_change#,last_time, offline_change#, online_change#,first_nonlogged_scn,first_nonlogged_time,status,name from v$datafile where file# in (1,6);
file# checkpoint_change# checkpoint_time     unrecoverable_change# unrecoverable_time  last_change# last_time           offline_change# online_change# first_nonlogged_scn first_nonlogged_tim status  name
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
    1        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     system  /mnt/ramdisk/book/system01.dbf
    6        13279958095 2019-03-12 16:40:08                     0                      13279959313 2019-03-12 16:53:06               0              0                   0                     recover /mnt/ramdisk/book/tea01.dbf

--//实际上offline 数据文件时,在控制文件记录的是last_change#,last_time.

sys@book> recover datafile 6;
media recovery complete.
--//注无法直接online,要执行recover.所以如果有需求要offline,应该养成随手执行recover datafile n的习惯.

sys@book>  select file#, checkpoint_change#,checkpoint_time,unrecoverable_change#,unrecoverable_time,last_change#,last_time, offline_change#, online_change#,first_nonlogged_scn,first_nonlogged_time,status,name from v$datafile where file# in (1,6);
file# checkpoint_change# checkpoint_time     unrecoverable_change# unrecoverable_time  last_change# last_time           offline_change# online_change# first_nonlogged_scn first_nonlogged_tim status  name
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
    1        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     system  /mnt/ramdisk/book/system01.dbf
    6        13279959313 2019-03-12 16:53:06                     0                      13279959313 2019-03-12 16:53:06               0              0                   0                     offline /mnt/ramdisk/book/tea01.dbf

--//checkpoint_change#=last_change#.status 从recover=>offline.  看看看看文件头的情况:

sys@book>  select file#, checkpoint_change#, checkpoint_time,creation_change#  , resetlogs_change#,status, checkpoint_count,fuzzy,name,tablespace_name  from v$datafile_header where file# in (1,6);
file# checkpoint_change# checkpoint_time     creation_change# resetlogs_change# status  checkpoint_count fuz name                                               tablespace_name
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
    1        13279958095 2019-03-12 16:40:08                7            925702 online              1224 yes /mnt/ramdisk/book/system01.dbf                     system
    6        13279959313 2019-03-12 16:53:06      13276257767            925702 offline              607 no  /mnt/ramdisk/book/tea01.dbf                        tea

--//文件头的checkpoint_change#与控制文件checkpoint_change#一致.
--//注:v$datafile的信息来自控制文件,v$datafile_header的信息来自数据文件头,不要搞混了.

sys@book> alter database datafile 6 online ;
database altered.

sys@book>  select file#, checkpoint_change#,checkpoint_time,unrecoverable_change#,unrecoverable_time,last_change#,last_time, offline_change#, online_change#,first_nonlogged_scn,first_nonlogged_time,status,name from v$datafile where file# in (1,6);
file# checkpoint_change# checkpoint_time     unrecoverable_change# unrecoverable_time  last_change# last_time           offline_change# online_change# first_nonlogged_scn first_nonlogged_tim status  name
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
    1        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     system  /mnt/ramdisk/book/system01.dbf
    6        13279960343 2019-03-12 16:58:40                     0                                                                    0              0                   0                     online  /mnt/ramdisk/book/tea01.dbf

--//可以发现数据文件 online后,offline_change#,online_change#字段并没有任何记录.也就是这个字段并不是记录数据文件offline的scn号.

3.继续测试:
--//既然不是数据文件offline时记录scn号,自然想到表空间的offline,online有关.

sys@book> alter tablespace tea offline ;
tablespace altered.

sys@book>  select file#, checkpoint_change#,checkpoint_time,unrecoverable_change#,unrecoverable_time,last_change#,last_time, offline_change#, online_change#,first_nonlogged_scn,first_nonlogged_time,status,name from v$datafile where file# in (1,6);
file# checkpoint_change# checkpoint_time     unrecoverable_change# unrecoverable_time  last_change# last_time           offline_change# online_change# first_nonlogged_scn first_nonlogged_tim status  name
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
    1        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     system  /mnt/ramdisk/book/system01.dbf
    6        13279960510 2019-03-12 17:01:12                     0                      13279960510 2019-03-12 17:01:12               0              0                   0                     offline /mnt/ramdisk/book/tea01.dbf

--//表空间offline,缺省要更新文件头的scn,你可以发现status=offline(而不是recover).checkpoint_change#=last_change#.
--//注 : alter tablespace tea offline immediate ;.这样不更新文件头,与offline 数据文件类似,你可以理解一组文件(表空间)offline.大家可以自行测试.

sys@book> alter tablespace tea online ;
tablespace altered.

sys@book>  select file#, checkpoint_change#,checkpoint_time,unrecoverable_change#,unrecoverable_time,last_change#,last_time, offline_change#, online_change#,first_nonlogged_scn,first_nonlogged_time,status,name from v$datafile where file# in (1,6);
file# checkpoint_change# checkpoint_time     unrecoverable_change# unrecoverable_time  last_change# last_time           offline_change# online_change# first_nonlogged_scn first_nonlogged_tim status  name
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
    1        13279958095 2019-03-12 16:40:08                     0                                                                    0              0                   0                     system  /mnt/ramdisk/book/system01.dbf
    6        13279960695 2019-03-12 17:03:55                     0                                                          13279960510    13279960695                   0                     online  /mnt/ramdisk/book/tea01.dbf

--//可以发现表空间tea online后,offline_change#,online_change#有信息,offline_change#记录就是表空间offline时的scn,而online_change#记录就是表空间online时的scn.
--//oracle为什么这样设计,假设你需要恢复1个数据文件,取出来的数据文件scn小于offline_change#,这样恢复时,从offline_change# 到 online_change#的日志或者归档可以跳过,
--//节约日志应用与恢复时间.

--//自然要问,为什么数据文件offline时为什么没有类似的记录呢?而数据文件offline时,处于"不稳定状态",不能直接online的,这样控制文件仅仅记录last_change#(offline时).
--//这样恢复时日志仅仅应用到last_change#就ok了,但是为什么数据文件online时last_change#的信息不写入offline_change#,online时的scn写入online_change#呢?
--//感觉这个存在一点点歧义行,个人理解.

--//也许oracle在视图字段命名上不科学,应该将offline_change#, online_change#命名为tablespace_offline_change#,tablespace_online_change#更加科学一些.