[20190213]學習bbed-恢復刪除的資料.txt

--//以前也做過類似測試,當時在用bbed做verify時錯誤都不處理,當時的想法就是能讀出就ok了.

--//而且當時也做成功,純粹是依葫蘆畫瓢,按照別人的blog重複操作,一點不理解為什麼這樣做.

--//重複測試:

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 dept1 as select * from dept ;

Table created.

SCOTT@book> select rowid,dept1.* from dept1;

ROWID DEPTNO DNAME LOC

------------------ ---------- -------------- -------------

AAAWPyAAEAAAAILAAA 10 ACCOUNTING NEW YORK

AAAWPyAAEAAAAILAAB 20 RESEARCH DALLAS

AAAWPyAAEAAAAILAAC 30 SALES CHICAGO

AAAWPyAAEAAAAILAAD 40 OPERATIONS BOSTON

SCOTT@book> @ rowid AAAWPyAAEAAAAILAAA

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------

91122 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;

SCOTT@book> delete from dept1 where deptno=10;

1 row deleted.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system checkpoint ;

System altered.

2.使用bbed恢復測試:

BBED> set dba 4,523

DBA 0x0100020b (16777739 4,523)

BBED> x /rncc *kdbr[1]

rowdata[44] @8140

-----------

flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8141: 0x00

cols@8142: 3

col 0[2] @8143: 20

col 1[8] @8146: RESEARCH

col 2[6] @8155: DALLAS

BBED> x /rncc *kdbr[0]

rowdata[66] @8162

-----------

flag@8162: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

lock@8163: 0x02

cols@8164: 0

--//可以看出刪除的flag=0x3c(正常沒有發生行連結或者遷移的情況下).

BBED> assign offset 8162=0x2c;

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub1 rowdata[0] @8162 0x2c

BBED> x /rncc *kdbr[0]

rowdata[66] @8162

-----------

flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8163: 0x02

cols@8164: 3

col 0[2] @8165: 10

col 1[10] @8168: ACCOUNTING

col 2[8] @8179: NEW YORK

--//OK,現在已經正常讀出.

BBED> sum apply

Check value for File 4, Block 523:

current = 0x8dc2, required = 0x8dc2

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 523

Block Checking: DBA = 16777739, Block Type = KTB-managed data block

data header at 0x7f9b8221527c

kdbchk: the amount of space used is not equal to block size

used=118 fsc=24 avsp=7946 dtl=8064

Block 523 failed with check code 6110

--//以前verify出錯,我基本不做修復,因為這些數值不是很好理解.

--//實際上計算公司是

--//dtl-used-fsc=avsp 或者 used+fsc+avsp=dtl.

--//8064-118-24 = 7922

--//前面顯示(lock@8163: 0x02)事務使用ITL槽是1(從0開始記數).

BBED> p ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x000a

ub2 kxidslt @70 0x0007

ub4 kxidsqn @72 0x000058bf

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x00c001e4

ub2 kubaseq @80 0x11ad

ub1 kubarec @82 0x1d

ub2 ktbitflg @84 0x2001 (KTBFUPB)

union _ktbitun, 2 bytes @86

sb2 _ktbitfsc @86 24

ub2 _ktbitwrp @86 0x0018

ub4 ktbitbas @88 0x1775b85d

--//_ktbitfsc,_ktbitwrp類似C語言的union,當設定提交標識時,表示scn_wrap.其它表示dml操作回收的空間(不包括flag,lock標識).

--//如果insert或者update需要空間比原來大,記錄是0.

BBED> x /rncc *kdbr[0]

rowdata[66] @8162

-----------

flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8163: 0x02

cols@8164: 3

col 0[2] @8165: 10

col 1[10] @8168: ACCOUNTING

col 2[8] @8179: NEW YORK

--//1+1+2+1+10+1+8 = 24,正好等於回收空間.注意每個欄位前有1個長度指示器(如果字串長度大於250,需要3個位元組)

BBED> assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=0;

sb2 _ktbitfsc @86 0

BBED> sum apply

Check value for File 4, Block 523:

current = 0x8dda, required = 0x8dda

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 523

Block Checking: DBA = 16777739, Block Type = KTB-managed data block

data header at 0x1a98e7c

kdbchk: space available on commit is incorrect

tosp=7972 fsc=0 stb=0 avsp=7946

Block 523 failed with check code 6111

--//計算公式是tosp=fsc+stb+avsp,stb 標識什麼不理解.

BBED> p kdbh

struct kdbh, 14 bytes @124

ub1 kdbhflag @124 0x00 (NONE)

sb1 kdbhntab @125 1

sb2 kdbhnrow @126 4

sb2 kdbhfrre @128 -1

sb2 kdbhfsbo @130 26

sb2 kdbhfseo @132 7972

sb2 kdbhavsp @134 7946

sb2 kdbhtosp @136 7972

BBED> assign kdbhtosp=kdbhavsp;

sb2 kdbhtosp @136 7946

BBED> assign kdbh.kdbhtosp=kdbh.kdbhavsp;

sb2 kdbhtosp @136 7946

--//兩者寫法都支援.

BBED> sum apply

Check value for File 4, Block 523:

current = 0x8df4, required = 0x8df4

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 523

--//OK.

SCOTT@book> alter system flush buffer_cache;

System altered.

SCOTT@book> select rowid,dept1.* from dept1;

ROWID DEPTNO DNAME LOC

------------------ ---------- -------------- -------------

AAAWPyAAEAAAAILAAA 10 ACCOUNTING NEW YORK

AAAWPyAAEAAAAILAAB 20 RESEARCH DALLAS

AAAWPyAAEAAAAILAAC 30 SALES CHICAGO

AAAWPyAAEAAAAILAAD 40 OPERATIONS BOSTON

--//隨便說一下,不要再使用modify修改資訊,這樣非常容易出錯(要考慮大小頭問題).使用assign簡單快捷,很少出錯.

--//簡直就像小時候玩變形金剛的遊戲.

[20190213]學習bbed-恢復刪除的資料.txt

相關文章