[20180619]bbed verify問題.txt

[20180619]bbed verify問題.txt

--//記錄一下自己一個多年的錯誤.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t as select * from dept ;
Table created.

SCOTT@test01p> select rowid,t.* from t;
ROWID                  DEPTNO DNAME                LOC
------------------ ---------- -------------------- -------------
AAAaRYAAJAAAAC7AAA         10 ACCOUNTING           NEW YORK
AAAaRYAAJAAAAC7AAB         20 RESEARCH             DALLAS
AAAaRYAAJAAAAC7AAC         30 SALES                CHICAGO
AAAaRYAAJAAAAC7AAD         40 OPERATIONS           DALLAS

SCOTT@test01p> @ rowid AAAaRYAAJAAAAC7AAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
    107608          9        187          0  0x24000BB           9,187                alter system dump datafile 9 block 187 ;

SCOTT@test01p> alter system checkpoint ;
System altered.

2.如果delete刪除記錄,提交後在對應的itl槽,fsc記錄回收的空閒空間.

SCOTT@test01p> delete from t where deptno in (10,20);
2 rows deleted.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> alter system checkpoint ;
System altered.

BBED> p /d dba 9,188   kdbh
struct kdbh, 14 bytes                       @124
   ub1 kdbhflag                             @124      0 (NONE)
   b1 kdbhntab                              @125      1
   b2 kdbhnrow                              @126      4
   sb2 kdbhfrre                             @128     -1
   sb2 kdbhfsbo                             @130      26
   sb2 kdbhfseo                             @132      7972
   b2 kdbhavsp                              @134      7946
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   
   b2 kdbhtosp                              @136      7994
--//我使用windows的bbed,訪問的塊存在一個資料塊的偏移. 實際上dba 9,187.

BBED> p /d dba 9,188   ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       8
      ub2 kxidslt                           @70       21
      ub4 kxidsqn                           @72       25182
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       20972155
      ub2 kubaseq                           @80       1834
      ub1 kubarec                           @82       32
   ub2 ktbitflg                             @84       8194 (KTBFUPB)
   union _ktbitun, 2 bytes                  @86
      b2 _ktbitfsc                          @86       44
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~      
      ub2 _ktbitwrp                         @86       44
   ub4 ktbitbas                             @88       27402089

--//可以發現itl事務槽記錄的fsc=44.

BBED> assign dba 9,188 kdbh.kdbhavsp=7945
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
b2 kdbhavsp                                 @134      7945

--//人為造成一個錯誤,造成verify錯誤.

BBED> sum apply dba 9,188
Check value for File 9, Block 188:
current = 0xca36, required = 0xca36

BBED> verify dba 9,188
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF
BLOCK = 187

Block Checking: DBA = 37748923, Block Type = KTB-managed data block
data header at 0x2a1027c
kdbchk: the amount of space used is not equal to block size
        used=74 fsc=44 avsp=7945 dtl=8064
Block 187 failed with check code 6110

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

--//fsc=44.
--//我的錯誤在這裡,我一直人為這幾個引數關係是dtl-used+fsc = avsp,而實際上:
used=74 fsc=44 avsp=7945 dtl=8064.
dtl-used-fsc = avsp
~~~~~~~~~~~~~~~~~~~~=> 應該是減去fsc.我以前這裡一直是0,加減都是對的,^_^.嚴重錯誤.
--//或者是used+fsc+avsp=dtl.
8064-74-44 = 7946

--//更正後.一切ok.
BBED> assign dba 9,188 kdbh.kdbhavsp=7946
b2 kdbhavsp                                 @134      7946

BBED> sum apply dba 9,188
Check value for File 9, Block 188:
current = 0xca35, required = 0xca35

--//很奇怪的windows bbed,會自動更正檢查和.

BBED> verify dba 9,188
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF
BLOCK = 187
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

4.人為產生3個事務,覆蓋itl槽,這樣對應的fsc資訊被清除.
SCOTT@test01p> select * from t where deptno=30 for update ;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        30 SALES                CHICAGO

SCOTT@test01p> commit ;
Commit complete.

--//執行3次就ok了.
SCOTT@test01p> alter system checkpoint ;
System altered.

BBED> p /d dba 9,188  kdbh
struct kdbh, 14 bytes                       @124
   ub1 kdbhflag                             @124      0 (NONE)
   b1 kdbhntab                              @125      1
   b2 kdbhnrow                              @126      4
   sb2 kdbhfrre                             @128      0
   sb2 kdbhfsbo                             @130      26
   sb2 kdbhfseo                             @132      7972
   b2 kdbhavsp                              @134      7994
   b2 kdbhtosp                              @136      7994

--//前面kdbh.kdbhavsp=7946,fsc=44.
--// 7946+44 = 7990 .
--// 注意我以前測試過(注意計算不包括flag,lock的長度),這樣還差4個位元組.

BBED> assign dba 9,188 kdbh.kdbhavsp=7993
b2 kdbhavsp                                 @134      7993

--//人為造成一個錯誤,造成verify錯誤.

BBED> sum apply dba 9,188
Check value for File 9, Block 188:
current = 0x8516, required = 0x8516

BBED> verify dba 9,188
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF
BLOCK = 187

Block Checking: DBA = 37748923, Block Type = KTB-managed data block
data header at 0x215027c
kdbchk: the amount of space used is not equal to block size
        used=70 fsc=0 avsp=7993 dtl=8064
Block 187 failed with check code 6110

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

--//dtl-used-fsc = avsp 或者是used+fsc+avsp=dtl.
used=70 fsc=0 avsp=7993 dtl=8064
8064-70-0 = 7994

BBED> assign dba 9,188 kdbh.kdbhavsp=7994
b2 kdbhavsp                                 @134      7994

BBED> sum apply dba 9,188
Check value for File 9, Block 188:
current = 0x8515, required = 0x8515

BBED> verify dba 9,188
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF
BLOCK = 187
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0