[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!!
--昨天被别人问一个问题,就是我的测试修改数据文件相应的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!!
