[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!!
--昨天被別人問一個問題,就是我的測試修改資料檔案相應的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!!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2076659/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【kingsql分享】使用BBED修改Oracle資料檔案頭推進SCNSQLOracle
- [20181204]bbed修改問題.txt
- [20211021]windows新建文字檔案帶日期(補充).txtWindows
- [20200414]Linux下快速刪除大量檔案(補充).txtLinux
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- [20190104]bbed手動修改資料.txt
- [20211221]提示precompute_subquery補充2.txt
- [20210223]bbed itl ktbitflg 2.txt
- [20191009]檔案頭fuzzy.txt
- [20180604]在記憶體修改資料(bbed).txt記憶體
- [20181227]簡單探究cluster table(補充)2.txt
- Python之檔案讀寫補充——R+模式下修改中文內容Python模式
- [20181122]bbed人為修改事務提交標誌.txt
- [20201208]為什麼返回2行記錄補充.txt
- [20230427]bbed sum apply問題2.txtAPP
- [20180928]ora-01426(補充).txt
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- 樹莓派ubuntu系統下修改config.txt檔案 樹莓派config.txt檔案修改記錄樹莓派Ubuntu
- MIDI檔案格式分析(補充和勘誤)
- 招投標中，已發出的招標檔案還能修改和補充嗎？
- [20210318]bbed讀取資料塊2.txt
- [20241016]Oracle C functions annotations補充.txtOracleFunction
- [20220603]測試quiz night(補充).txtUI
- [20211215]提示precompute_subquery補充.txt
- [20210429]檔案頭塊不會快取.txt快取
- [20201218]資料檔案OS頭的修復.txt
- [20211116]plsql_code_type=native補充.txtSQL
- Windows修改新建.txt檔名Windows
- [20220329]批量修改檔名.txt
- [20191128]oracle Audit檔案管理2.txtOracle
- [20221014]TNS-12543 TNSdestination host unreachable(補充).txt
- [20210803]使用那個shared pool latch(補充).txt
- [20211013]測試遠端監聽補充.txt
- [20231008]bbed探究lob段.txt
- [20210920]bbed的assign命令.txt
- [20220223]bbed ktbbh.ktbbhict.txt
- [20210304]bbed的assign命令.txt
- [20180619]bbed verify問題.txt
- 基礎補充：使用xlrd模組讀取excel檔案Excel