[20160526]bbed修改資料記錄(不等長).txt
[20160526]bbed修改資料記錄(不等長).txt
--以前做的測試,有點亂,當時沒有很好的理解快速提交.而且做的很亂,連結如下:
http://blog.itpub.net/267265/viewspace-1193074/
--今天重複測試看看:
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
--首先等長的修改很簡單,因為是原地替換,直接修改就ok了,再做sum apply就ok了.這個不再練習的範圍.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t (id int,name varchar2(100));
insert into t values(1,'AAAAA');
insert into t values(2,'BBBBB');
insert into t values(3,'CCCCC');
insert into t values(4,'DDDDD');
insert into t values(5,'EEEEE');
commit;
alter system checkpoint;
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237811650 AAAW7EAAEAAAT/7AAA 1 AAAAA
13237811650 AAAW7EAAEAAAT/7AAB 2 BBBBB
13237811650 AAAW7EAAEAAAT/7AAC 3 CCCCC
13237811650 AAAW7EAAEAAAT/7AAD 4 DDDDD
13237811650 AAAW7EAAEAAAT/7AAE 5 EEEEE
SCOTT@book> @ &r/rowid AAAW7EAAEAAAT/7AAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
93892 4 81915 0 4,81915 alter system dump datafile 4 block 81915
2.修改id=1,3,5:
SCOTT@book> update t set name=name||id where id in (1,3,5);
3 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237811703 AAAW7EAAEAAAT/7AAA 1 AAAAA1
13237811703 AAAW7EAAEAAAT/7AAB 2 BBBBB
13237811703 AAAW7EAAEAAAT/7AAC 3 CCCCC3
13237811703 AAAW7EAAEAAAT/7AAD 4 DDDDD
13237811703 AAAW7EAAEAAAT/7AAE 5 EEEEE5
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system flush buffer_cache;
System altered.
--OK現在使用bbed是否可以恢復原樣.
3.使用bbed恢復:
BBED> set dba 4,81915
DBA 0x01013ffb (16859131 4,81915)
BBED> p kdbr
sb2 kdbr[0] @118 8015
sb2 kdbr[1] @120 8064
sb2 kdbr[2] @122 8002
sb2 kdbr[3] @124 8040
sb2 kdbr[4] @126 7989
--最小的offset是kdbr[4].
BBED> p *kdbr[4]
rowdata[0]
----------
ub1 rowdata[0] @8089 0x2c
BBED> x /3rnc rowdata
rowdata[0] @8089
----------
flag@8089: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8090: 0x02
cols@8091: 2
col 0[2] @8092: 5
col 1[6] @8095: EEEEE5
rowdata[13] @8102
-----------
flag@8102: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8103: 0x02
cols@8104: 2
col 0[2] @8105: 3
col 1[6] @8108: CCCCC3
rowdata[26] @8115
-----------
flag@8115: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8116: 0x02
cols@8117: 2
col 0[2] @8118: 1
col 1[6] @8121: AAAAA1
--//從這裡可以確定修改3條記錄.lock=0x02,對應第2個ITL槽.
--使用如下可以輸出全部記錄.
BBED> p *kdbr[4]
BBED> x /8rnc rowdata
...
rowdata[39] @8128
-----------
flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8129: 0x00
cols@8130: 2
col 0[2] @8131: 5
col 1[5] @8134: EEEEE
....
rowdata[63] @8152
-----------
flag@8152: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8153: 0x00
cols@8154: 2
col 0[2] @8155: 3
col 1[5] @8158: CCCCC
...
rowdata[87] @8176
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x00
cols@8178: 2
col 0[2] @8179: 1
col 1[5] @8182: AAAAA
--可以確定偏移在8128,8152,8176. kdbr記錄的是相對偏移要再減去100. 應該輸入:8028,8052,8076
--排序kdbr,前面3個就是的地址要替換為原來的.
7989=>kdbr[4]=>8028
8002=>kdbr[2]=>8052
8015=>kdbr[0]=>8076
8040
8064
assign kdbr[4] = 8028
assign kdbr[2] = 8052
assign kdbr[0] = 8076
--執行如上命令:
--然後檢查資訊是否正確.
BBED> p *kdbr[4]
rowdata[39]
-----------
ub1 rowdata[39] @8128 0x2c
BBED> x /rnc
rowdata[39] @8128
-----------
flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8129: 0x00
cols@8130: 2
col 0[2] @8131: 5
col 1[5] @8134: EEEEE
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x51d4, required = 0x51d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
Block Checking: DBA = 16859131, Block Type = KTB-managed data block
data header at 0x7f8163e23264
kdbchk: xaction header lock count mismatch
trans=2 ilk=3 nlo=0
Block 81915 failed with check code 6108
--修改行記錄的lock=0x20.注意在行記錄的位置+1. 8128,8152,8176=>8129,8153,8177.
modify /x 0x02 offset 8129
modify /x 0x02 offset 8153
modify /x 0x02 offset 8177
BBED> p *kdbr[4]
rowdata[39]
-----------
ub1 rowdata[39] @8128 0x2c
BBED> x /rnc
rowdata[39] @8128
-----------
flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8129: 0x02
cols@8130: 2
col 0[2] @8131: 5
col 1[5] @8134: EEEEE
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x53d4, required = 0x53d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
Block Checking: DBA = 16859131, Block Type = KTB-managed data block
data header at 0xbe0464
kdbchk: the amount of space used is not equal to block size
used=88 fsc=0 avsp=7997 dtl=8088
Block 81915 failed with check code 6110
--依舊不行.
4.要修改一致kdbh.kdbhtosp=kdbh.kdbhavsp
BBED> p kdbh.kdbhavsp
sb2 kdbhavsp @110 7997
BBED> p kdbh.kdbhtosp
sb2 kdbhtosp @112 7997
--要修改一致kdbh.kdbhtosp=kdbh.kdbhavsp,已經一致可以不修改.
BBED> assign kdbh.kdbhtosp=kdbh.kdbhavsp
sb2 kdbhtosp @112 7997
--// dtl-used = 8088-88=8000
assign kdbh.kdbhtosp=8000
assign kdbh.kdbhavsp=8000
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x53d4, required = 0x53d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
5.修改對應ITL資訊:
--//我看了其他文件要修改對應
assign ktbbhitl[1]._ktbitun,_ktbitfsc=0
--//不過我這裡已經是0,不需要修改,上面也不報錯.而且這個資訊對應上面的fsc=0(used=88 fsc=0 avsp=7997 dtl=8088)
BBED> p ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x001e
ub4 kxidsqn @72 0x00009b75
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c001b5
ub2 kubaseq @80 0x1def
ub1 kubarec @82 0x17
ub2 ktbitflg @84 0x2003 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x1508f9f7
6.檢驗修改成果:
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237811703 AAAW7EAAEAAAT/7AAA 1 AAAAA
13237811703 AAAW7EAAEAAAT/7AAB 2 BBBBB
13237811703 AAAW7EAAEAAAT/7AAC 3 CCCCC
13237811703 AAAW7EAAEAAAT/7AAD 4 DDDDD
13237811703 AAAW7EAAEAAAT/7AAE 5 EEEEE
--OK結果正確.
總結:
1.這樣操作比我原來的修改簡單一些,我以前的修改太多.
2.首先修改行目錄指向正確的偏移.
3.修改相應記錄的lock等於原來的itl槽.
4.使用verify檢查根據提示,我這裡fsc=0,我那另外的情況來說明:
--修改前:
BBED> p /d kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 1
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 20
sb2 kdbhfseo @108 6841
sb2 kdbhavsp @110 7608
sb2 kdbhtosp @112 7647
BBED> p ktbbhitl[0]
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x002e
ub4 kxidsqn @48 0x00001ac8
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00800860
ub2 kubaseq @56 0x2398
ub1 kubarec @58 0x1c
ub2 ktbitflg @60 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 39
ub2 _ktbitwrp @62 0x0027
ub4 ktbitbas @64 0x8fc1230f
Block header dump: 0x0180239c
Object id on Block? Y
seg/obj: 0x1da20 csc: 0x03.8fc12309 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1802399 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00001ac8 0x00800860.2398.1c --U- 1 fsc 0x0027.8fc1230f
0x02 0x0001.005.00001873 0x0080000f.1e28.0d C--- 0 scn 0x0003.690c955a
--修改後出現:
Block Checking: DBA = 25174940, Block Type = KTB-managed data block
data header at 0x22a9c64
kdbchk: the amount of space used is not equal to block size
used=480 fsc=39 avsp=7608 dtl=8088
Block 9116 failed with check code 6110
--實際上修改前滿足 dtl-used+fsc= kdbh.kdbhtosp
-- 8088-480+39=7647
--fsc來源於ITL槽.
--// dtl-used 8088-480=7608
assign kdbh.kdbhavsp=7608
assign kdbh.kdbhtosp=7608
--然後設定(根據修改記錄lock指向的itl槽)
assign ktbbhitl[0]._ktbitun._ktbitfsc=0
BBED> sum apply
Check value for File 21, Block 9116:
current = 0x16db, required = 0x16db
BBED> verify
DBVERIFY - Verification starting
FILE = /home/oracle/emp.aaa
BLOCK = 9116
5.寫這些主要是前面修復一個沒有加where的update語句,好在表本身記錄不多,沒有開啟歸檔,已經過去N久,修改前的資訊還儲存在塊中.
所以才重新整理這篇文件.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2107060/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180604]在記憶體修改資料(bbed).txt記憶體
- [20190104]bbed手動修改資料.txt
- [20181204]bbed修改問題.txt
- [20190104]bbed手工插入資料.txt
- [20210906]bbed讀取資料塊(bbed-wrap.sh).txt
- [20210318]bbed讀取資料塊.txt
- [20220909]bbed關於刪除記錄恢復的問題.txt
- [20210831]bbed讀取資料塊6.txt
- [20210930]bbed恢復刪除的資料.txt
- [20210323]bbed讀取資料塊5.txt
- [20210318]bbed讀取資料塊2.txt
- [20210319]bbed讀取資料塊3.txt
- [20181122]bbed人為修改事務提交標誌.txt
- 27_bbed實戰(1)_修改資料內容
- [20210930]bbed讀取資料塊7 fffext.sh.txt
- [20190213]學習bbed-恢復刪除的資料.txt
- [20220223]bbed讀取資料塊mssm與assm 2.txtSSM
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- 獲取當前修改的行記錄資料
- [20210401]使用bbed讀取資料塊恢復注意6.txt
- 記錄一次mysql批量修改大量資料MySql
- 【kingsql分享】使用BBED修改Oracle資料檔案頭推進SCNSQLOracle
- [20180619]bbed verify問題.txt
- [20231008]bbed探究lob段.txt
- [20220223]bbed ktbbh.ktbbhict.txt
- [20210920]bbed的assign命令.txt
- [20210304]bbed的assign命令.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題.txt
- GBase XDM（單機/分片叢集）資料庫修改記錄資料庫
- 樹莓派ubuntu系統下修改config.txt檔案 樹莓派config.txt檔案修改記錄樹莓派Ubuntu
- bbed修改undo段狀態
- [20181227]bbed的使用問題.txt
- [20210901]cygwin下使用bbed.txt
- [20230224]bbed設定偏移技巧.txt
- [20210303]bbed使用小問題.txt
- [20210223]bbed itl ktbitflg 2.txt
- BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。Oracle資料庫
- Oracle 之利用BBED修改資料塊SCN----沒有備份資料檔案的資料恢復Oracle資料恢復
- TestComplete資料驅動測試教程（三）——修改記錄測試