[20190104]bbed手工插入資料.txt
[20190104]bbed手工插入資料.txt
--//下午沒事,測試看看bbed手動插入資料,要求在塊中有空間能容納修改資訊.
--//特殊修復bbed實現起來還是比較麻煩,如果特殊修復可以在一臺好的資料庫上建立相同的表結果(注意字符集,大小頭問題),
--//插入資料,然後想辦法引入對應的資料塊中.
--//我的測試僅僅一臺機器,也可以演示操作過程,不要在生產系統做這樣的操作!!
1.環境:
SCOTT@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
SCOTT@book> create table empy as select * from emp where rownum<=2;
Table created.
SCOTT@book> select rowid,empy.* from empy;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWH0AAEAAAAILAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAWH0AAEAAAAILAAB 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
SCOTT@book> @ rowid AAAWH0AAEAAAAILAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90612 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;
SCOTT@book> select rowid,emp.* from emp where empno=7839;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAI 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
SCOTT@book> @ rowid AAAVREAAEAAAACXAAI
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87108 4 151 8 0x1000097 4,151 alter system dump datafile 4 block 151 ;
--//測試想辦法將dba=4,151的EMPNO=7839的記錄插入dba=4,523的資料塊中.
SCOTT@book> alter system checkpoint;
System altered.
2.首先確定匯出記錄長度以及偏移量:
BBED> x /rnccntnnn dba 4,151 *kdbr[8]
rowdata[197] @7818
------------
flag@7818: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7819: 0x01
cols@7820: 8
col 0[3] @7821: 7839
col 1[4] @7825: KING
col 2[9] @7830: PRESIDENT
col 3[0] @7840: *NULL*
col 4[7] @7841: 1981-11-17 00:00:00
col 5[2] @7849: 5000
col 6[0] @7852: *NULL*
col 7[2] @7853: 10
--//7853+3-1-7818+1 = 38,確定記錄長度是38,從offset=7818開始.
BBED> dump /v offset 7818 count 39
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 151 Offsets: 7818 to 7856 Dba:0x01000097
-----------------------------------------------------------------------------------------------------------
2c010803 c24f2804 4b494e47 09505245 53494445 4e54ff07 77b50b11 01010102 l ,....O(.KING.PRESIDENT..w.......
c233ff02 c10b2c l .3....,
<32 bytes per line>)
SCOTT@book> @ bbvi 4 151
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 1236992 -s 8192 /mnt/ramdisk/book/users01.dbf
xxd -c16 -g 2 -s 1236992 -l 8192 /mnt/ramdisk/book/users01.dbf
dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=151 count=1 of=4_151.dd conv=notrunc 2>/dev/null
od -j 1236992 -N 8192 -t x1 -v /mnt/ramdisk/book/users01.dbf
hexdump -s 1236992 -n 8192 -C -v /mnt/ramdisk/book/users01.dbf
alter system dump datafile '/mnt/ramdisk/book/users01.dbf' block 151;
alter session set events 'immediate trace name set_tsn_p1 level 5';
alter session set events 'immediate trace name buffer level 16777367';
9 rows selected.
--//1236992+7818 =1244810,確定在檔案的總偏移量1244810.可以執行命令如下:
$ xxd -c39 -g 8 -s 1244810 -l 39 /mnt/ramdisk/book/users01.dbf
012fe8a: 2c010803c24f2804 4b494e4709505245 534944454e54ff07 77b50b1101010102 c233ff02c10b2c ,...翺(.KING.PRESIDENT.w?.....?.?,
3.確定匯入的偏移量:
BBED> p dba 4,523 kdbr
sb2 kdbr[0] @142 8026
sb2 kdbr[1] @144 7983
--//查詢最小的kdbr[N]值.這裡對應kdbr[1].
BBED> x /rnccntnnn dba 4,523 *kdbr[1]
rowdata[0] @8107
----------
flag@8107: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8108: 0x00
cols@8109: 8
col 0[3] @8110: 7499
col 1[5] @8114: ALLEN
col 2[8] @8120: SALESMAN
col 3[3] @8129: 7698
col 4[7] @8133: 1981-02-20 00:00:00
col 5[2] @8141: 1600
col 6[2] @8144: 300
col 7[2] @8147: 30
--//8107-38 = 8069,可以確定匯入偏移量是8069.
4.生成bbed執行程式碼:
$ xxd -c1 -g 1 -s 1244810 -l 38 /mnt/ramdisk/book/users01.dbf | cut -c10-11 | xargs -I{} echo assign /x offset @ = {}
assign /x offset @ = 2c
assign /x offset @ = 01
assign /x offset @ = 08
assign /x offset @ = 03
assign /x offset @ = c2
assign /x offset @ = 4f
assign /x offset @ = 28
assign /x offset @ = 04
assign /x offset @ = 4b
assign /x offset @ = 49
assign /x offset @ = 4e
assign /x offset @ = 47
assign /x offset @ = 09
assign /x offset @ = 50
assign /x offset @ = 52
assign /x offset @ = 45
assign /x offset @ = 53
assign /x offset @ = 49
assign /x offset @ = 44
assign /x offset @ = 45
assign /x offset @ = 4e
assign /x offset @ = 54
assign /x offset @ = ff
assign /x offset @ = 07
assign /x offset @ = 77
assign /x offset @ = b5
assign /x offset @ = 0b
assign /x offset @ = 11
assign /x offset @ = 01
assign /x offset @ = 01
assign /x offset @ = 01
assign /x offset @ = 02
assign /x offset @ = c2
assign /x offset @ = 33
assign /x offset @ = ff
assign /x offset @ = 02
assign /x offset @ = c1
assign /x offset @ = 0b
--//儲存檔案,利用vim的increment.vim外掛執行如下:%Inc s8069 i1,生成如下程式碼,開頭手工加入set dba 4,523.
--//外掛可以在如下連結下載:
set dba 4,523
assign /x offset 8069 = 2c
assign /x offset 8070 = 01
assign /x offset 8071 = 08
assign /x offset 8072 = 03
assign /x offset 8073 = c2
assign /x offset 8074 = 4f
assign /x offset 8075 = 28
assign /x offset 8076 = 04
assign /x offset 8077 = 4b
assign /x offset 8078 = 49
assign /x offset 8079 = 4e
assign /x offset 8080 = 47
assign /x offset 8081 = 09
assign /x offset 8082 = 50
assign /x offset 8083 = 52
assign /x offset 8084 = 45
assign /x offset 8085 = 53
assign /x offset 8086 = 49
assign /x offset 8087 = 44
assign /x offset 8088 = 45
assign /x offset 8089 = 4e
assign /x offset 8090 = 54
assign /x offset 8091 = ff
assign /x offset 8092 = 07
assign /x offset 8093 = 77
assign /x offset 8094 = b5
assign /x offset 8095 = 0b
assign /x offset 8096 = 11
assign /x offset 8097 = 01
assign /x offset 8098 = 01
assign /x offset 8099 = 01
assign /x offset 8100 = 02
assign /x offset 8101 = c2
assign /x offset 8102 = 33
assign /x offset 8103 = ff
assign /x offset 8104 = 02
assign /x offset 8105 = c1
assign /x offset 8106 = 0b
--//執行如上程式碼後,檢查:
BBED> x /rnccntnnn dba 4,523 offset 8069
freespace[7923] @8069
---------------
flag@8069: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8070: 0x01
cols@8071: 8
col 0[3] @8072: 7839
col 1[4] @8076: KING
col 2[9] @8081: PRESIDENT
col 3[0] @8091: *NULL*
col 4[7] @8092: 1981-11-17 00:00:00
col 5[2] @8100: 5000
col 6[0] @8103: *NULL*
col 7[2] @8104: 10
BBED> assign offset 8070=0x0;
ub1 freespace[0] @8070 0x00
--//取消lock標識.
--//OK正確.現在確定要插入的偏移量.
BBED> p dba 4,523 kdbr
sb2 kdbr[0] @142 8026
sb2 kdbr[1] @144 7983
--//注意修改kdbr行目錄偏移是相對偏移要減去kbh的地址.當前塊kdbh位於124.
BBED> map dba 4,523
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 523 Dba:0x0100020b
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[2] @142
ub1 freespace[7961] @146
ub1 rowdata[81] @8107
ub4 tailchk @8188
--//8069-124 = 7945. 插入1行到行目錄中.首先增加記錄數量.
BBED> p kdbt
struct kdbt[0], 4 bytes @138
sb2 kdbtoffs @138 0
sb2 kdbtnrow @140 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BBED> assign kdbt.kdbtnrow=3;
sb2 kdbtnrow @140 3
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1
sb2 kdbhnrow @126 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 22
sb2 kdbhfseo @132 7983
sb2 kdbhavsp @134 7961
sb2 kdbhtosp @136 7961
BBED> assign kdbh.kdbhnrow=3
sb2 kdbhnrow @126 3
BBED> p dba 4,523 kdbr
sb2 kdbr[0] @142 8026
sb2 kdbr[1] @144 7983
sb2 kdbr[2] @146 0
BBED> assign kdbr[2]=7945
sb2 kdbr[0] @146 7945
BBED> x /rnccntnnn dba 4,523 *kdbr[2]
rowdata[0] @8069
----------
flag@8069: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8070: 0x00
cols@8071: 8
col 0[3] @8072: 7839
col 1[4] @8076: KING
col 2[9] @8081: PRESIDENT
col 3[0] @8091: *NULL*
col 4[7] @8092: 1981-11-17 00:00:00
col 5[2] @8100: 5000
col 6[0] @8103: *NULL*
col 7[2] @8104: 10
BBED> sum apply dba 4,523
Check value for File 4, Block 523:
current = 0xaa11, required = 0xaa11
BBED> verify dba 4,523
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 523
Block Checking: DBA = 16777739, Block Type = KTB-managed data block
data header at 0x206be7c
kdbchk: fsbo(22) wrong, (hsz 24)
Block 523 failed with check code 6129
BBED> assign kdbh.kdbhfsbo=24;
sb2 kdbhfsbo @130 24
BBED> sum apply dba 4,523
Check value for File 4, Block 523:
current = 0xaa1f, required = 0xaa1f
BBED> verify dba 4,523
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 523
Block Checking: DBA = 16777739, Block Type = KTB-managed data block
data header at 0x208de7c
kdbchk: bad row offset slot 2 offs 7945 fseo 7983 dtl 8168 bhs 104
Block 523 failed with check code 6135
BBED> assign kdbh.kdbhfseo=7945;
sb2 kdbhfseo @132 7945
BBED> sum apply dba 4,523
Check value for File 4, Block 523:
current = 0xaa39, required = 0xaa39
BBED> verify dba 4,523
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 523
Block Checking: DBA = 16777739, Block Type = KTB-managed data block
data header at 0x208de7c
kdbchk: the amount of space used is not equal to block size
used=143 fsc=0 avsp=7961 dtl=8064
Block 523 failed with check code 6110
--//dtl-used-fsc = 8064-143-0 = 7921
BBED> assign kdbh.kdbhavsp=7921
sb2 kdbhavsp @134 7921
BBED> sum apply dba 4,523
Check value for File 4, Block 523:
current = 0xabd1, required = 0xabd1
BBED> verify dba 4,523
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 523
Block Checking: DBA = 16777739, Block Type = KTB-managed data block
data header at 0x20b007c
kdbchk: space available on commit is incorrect
tosp=7961 fsc=0 stb=0 avsp=7921
Block 523 failed with check code 6111
BBED> assign kdbh.kdbhtosp=kdbh.kdbhavsp
sb2 kdbhtosp @136 7921
BBED> sum apply dba 4,523
Check value for File 4, Block 523:
current = 0xaa39, required = 0xaa39
BBED> verify dba 4,523
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 523
--//終於OK!!
4.驗證是否插入成功:
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select rowid,empy.* from empy;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWH0AAEAAAAILAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAWH0AAEAAAAILAAB 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
AAAWH0AAEAAAAILAAC 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
--//步驟太多了,很容易出錯...^_^.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2331196/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190104]bbed手動修改資料.txt
- [20210906]bbed讀取資料塊(bbed-wrap.sh).txt
- [20210318]bbed讀取資料塊.txt
- [20180604]在記憶體修改資料(bbed).txt記憶體
- [20210831]bbed讀取資料塊6.txt
- [20210930]bbed恢復刪除的資料.txt
- [20210323]bbed讀取資料塊5.txt
- [20210318]bbed讀取資料塊2.txt
- [20210319]bbed讀取資料塊3.txt
- [20210930]bbed讀取資料塊7 fffext.sh.txt
- [20190213]學習bbed-恢復刪除的資料.txt
- [20220223]bbed讀取資料塊mssm與assm 2.txtSSM
- [20190104]ipcs檢視共享記憶體段.txt記憶體
- [20210401]使用bbed讀取資料塊恢復注意6.txt
- [20181204]bbed修改問題.txt
- [20180619]bbed verify問題.txt
- [20231008]bbed探究lob段.txt
- [20220223]bbed ktbbh.ktbbhict.txt
- [20210920]bbed的assign命令.txt
- [20210304]bbed的assign命令.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題.txt
- mybatis插入資料、批量插入資料MyBatis
- [20181227]bbed的使用問題.txt
- [20210901]cygwin下使用bbed.txt
- [20230224]bbed設定偏移技巧.txt
- [20210303]bbed使用小問題.txt
- [20210223]bbed itl ktbitflg 2.txt
- [20190125]bbed恢復資料遇到延遲塊清除的問題3.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- [20180628]顯示bbed x命令格式.txt
- [20210311]如何建立bbed安裝包.txt
- [20190104]sga_target 的設定和ORA-04031錯誤.txt
- [20200825]手工安裝HR schema.txt
- [20180415]blob的插入.txt
- [20180416]clob的插入.txt
- [20180627]測試bbed是否支援管道命令.txt
- [20231021]生成bbed的執行指令碼.txt指令碼
- [20230427]bbed sum apply問題2.txtAPP