[20160407]bbed修改文件头2(补充).txt

[20160407]bbed修改文件头2(补充).txt

--昨天被别人问一个问题,就是我的测试修改数据文件相应的CHECKPOINT_CHANGE#就ok了.偏移量是block=1的offset=484.
--链接 http://blog.itpub.net/267265/viewspace-2075424/

--不过别人问的是这个时间是如何存储的.我以前也做过一些.链接:
--http://blog.itpub.net/267265/viewspace-746222/

--我的感觉在11.2.0.3下要修改CHECKPOINT_CHANGE#, CHECKPOINT_COUNT,时间不用修改.实际上如果要跳过修改CHECKPOINT_COUNT,可以
--通过重建控制文件的方法实现,我自己从来没有做这个测试.

--//后记:
--//更正:严重错误,应该按照如下公式计算:
time = (((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;
--//参考新链接:
http://blog.itpub.net/267265/viewspace-2135046/

SYS@book> @ &r/stamp 908533461
     STAMP STAMP_CONV_TIME
---------- -------------------
 908533461 2016-04-07 10:24:21

SYS@book> @ &r/stamp 908506808
     STAMP STAMP_CONV_TIME
---------- -------------------
 908506808 2016-04-07 03:00:08

--//这样与视图v$datafile_header 的CHECKPOINT_TIME一样,特此更正!!

SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header ;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
    1        13227551841 2016-04-07 10:24:21                7       13227286650 ONLINE              1011 YES /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227551841 2016-04-07 10:24:21             1834       13227286650 ONLINE              1007 YES /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227551841 2016-04-07 10:24:21           923328       13227286650 ONLINE               927 YES /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227551841 2016-04-07 10:24:21            16143       13227286650 ONLINE              1011 YES /mnt/ramdisk/book/users01.dbf    USERS
    5        13227551841 2016-04-07 10:24:21           952916       13227286650 ONLINE               924 YES /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227551841 2016-04-07 10:24:21          1314508       13227286650 ONLINE               940 YES /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227536676 2016-04-07 03:00:08      13227207527       13227286650 OFFLINE               32 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.
============

摘抄一段:

The file header is stored in the first block cf the data file. We can use bbed tc examine the blcck and show
the block map. The header blocks contain a single data structure — kcvfh. Oracle considers four attributes
of this data structure when determining if a data file is sync with the other data files of the database:

kscnbas  (at offset 484) -- SCN of last change to the datafile.
kcvcptim (at offset 492) -- Time of the last change to the datafile.
kcvfhcpc (at offset 140) -- Checkpoint count.
kcvfhccc (at offset 148) -- Unknown, but is always l less than the checkpoint point count.

The first two attributes are stored in the kcvfhckp sub-structure. The second two are attributes in their own
right. We can use the print command to display them all for the file that requires recovery:

--下面看看时间如何保存的:

1.环境:

SCOTT@book> @ &r/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

SCOTT@book> alter database datafile 7 offline ;
Database altered.

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header ;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
    1        13227551841 2016-04-07 10:24:21                7       13227286650 ONLINE              1011 YES /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227551841 2016-04-07 10:24:21             1834       13227286650 ONLINE              1007 YES /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227551841 2016-04-07 10:24:21           923328       13227286650 ONLINE               927 YES /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227551841 2016-04-07 10:24:21            16143       13227286650 ONLINE              1011 YES /mnt/ramdisk/book/users01.dbf    USERS
    5        13227551841 2016-04-07 10:24:21           952916       13227286650 ONLINE               924 YES /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227551841 2016-04-07 10:24:21          1314508       13227286650 ONLINE               940 YES /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227536676 2016-04-07 03:00:08      13227207527       13227286650 OFFLINE               32 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

2.通过bbed观察:

BBED> p dba 7,1 kcvfhckp.kcvcptim
ub4 kcvcptim                                @492      0x3626b6b8

BBED> p dba 1,1 kcvfhckp.kcvcptim
ub4 kcvcptim                                @492      0x36271ed5

SCOTT@book> select dump(sysdate,16) from dual ;
DUMP(SYSDATE,16)
------------------------------------------------
Typ=13 Len=8: e0,7,4,7,a,1a,24,0

SCOTT@book> create table tt (cr date);
Table created.

SCOTT@book> insert into tt values ('2016-04-07 10:24:21');
1 row created.

SCOTT@book> commit ;
Commit complete.

--注意我这里有点不规范,直接使用字符变量,因为我定义了环境变量,这样带入没有问题.
$ env | grep NLS
NLS_LANG=AMERICAN_AMERICA.zhs16gbk
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

SCOTT@book> select dump(cr,16) c40,cr from tt;
C40                                      CR
---------------------------------------- -------------------
Typ=12 Len=7: 78,74,4,7,b,19,16          2016-04-07 10:24:21

--注意保存在数据块的直接dump(sysdate)的不一样,即使这样明显对不上.一般linux表示使用从1970/1/1的秒数.
--所以上面的保存理论讲应该也是秒数.

SCOTT@book> @ &r/16to10 36271ed5
16 to 10 DEC
------------
   908533461

SCOTT@book> select to_date('1970/1/1','yyyy/mm/dd')+908533461/86400 c40 from dual ;
C40
----------------------------------------
1998-10-16 10:24:21

--明显也不对.

SCOTT@book> @ &r/16to10 3626b6b8
16 to 10 DEC
------------
   908506808

SCOTT@book> select 908533461-908506808 from dual ;
908533461-908506808
-------------------
              26653

SCOTT@book> select  (to_date('2016-04-07 10:24:21','yyyy/mm/dd hh24:mi:ss') -  to_date('2016-04-07 03:00:08','yyyy/mm/dd hh24:mi:ss'))*86400 N20 from dual ;
       N20
----------
     26653

--可以发现正好对上,也就是上面的记数单位还是秒.只不过起点不上1970/1/1.

SCOTT@book> select  to_date('2016-04-07 10:24:21','yyyy/mm/dd hh24:mi:ss') - 908533461/86400  c30 from dual ;
C30
------------------------------
1987-06-24 00:00:00

--也就是从这个时间开始记数的.还记得以前写的blog吗?
--[20160119]V$RMAN_OUTPUT的stamp.txt http://blog.itpub.net/267265/viewspace-1979123/
--那里的时间是1987-06-26 00:00:00.相差2天.

--研究这个没什么意思,仅仅当作play!!