[20190125]bbed恢復資料遇到延遲塊清除的問題3.txt

--//昨天分別做了2個測試,連結:

http://blog.itpub.net/267265/viewspace-2564716/=>[20190124]bbed恢復資料遇到延遲塊清除的問題.txt

http://blog.itpub.net/267265/viewspace-2564717/=>[20190124]bbed恢復資料遇到延遲塊清除的問題2.txt

--//我測試完感覺,oracle對於system表空間檢查更加嚴格,在修復資料塊時對於系統表空間的資料檔案特別要引起注意.

--//仔細想想system表空間oracle使用mssm,一般使用者的表空間是assm(多數使用者應該選擇這個模式),是否這兩種模式導致的問題呢?

--//自己還是測試看看.

1.環境:

SYSTEM@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

CREATE TABLESPACE mssm DATAFILE

'/mnt/ramdisk/book/mssm01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

LOGGING

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL

FLASHBACK ON;

--//使用SEGMENT SPACE MANAGEMENT MANUAL.

2.建立測試環境:

SCOTT@book> create table t tablespace mssm as select rownum id,'test' name from dual connect by level<=5;

Table created.

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

ROWID ID NAME

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

AAAWP2AAHAAAACBAAA 1 test

AAAWP2AAHAAAACBAAB 2 test

AAAWP2AAHAAAACBAAC 3 test

AAAWP2AAHAAAACBAAD 4 test

AAAWP2AAHAAAACBAAE 5 test

SCOTT@book> @ rowid AAAWP2AAHAAAACBAAA

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

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

91126 7 129 0 0x1C00081 7,129 alter system dump datafile 7 block 129 ;

--//建立在mssm表空間,使用mssm模式.

SCOTT@book> delete from t where id=2;

1 row deleted.

SYS@book> alter system flush buffer_cache;

System altered.

SYS@book> @ bh 7 129

HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME

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

0000000084D14E60 7 129 1 data block free 0 0 0 0 0 0 0000000069694000 T

0000000084D14E60 7 129 1 data block free 0 0 0 0 0 0 0000000069696000 T

0000000084D14E60 7 129 1 data block free 0 0 0 0 0 0 000000006D890000

0000000084D14E60 7 129 1 data block free 0 0 0 0 0 0 000000006D892000

0000000084D14E60 7 129 1 data block free 0 0 0 0 0 0 000000007068A000

--//注:我的測試支援IMU,必須檢查資料塊不在快取在再提交.

SCOTT@book> commit ;

Commit complete.

--//這個時候對應資料塊已經不在快取了,做延遲塊提交.

3.使用bbed恢復看看:

BBED> set dba 7,129

DBA 0x01c00081 (29360257 7,129)

BBED> x /rnc *kdbr[1]

rowdata[33] @8166

-----------

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

lock@8167: 0x02

cols@8168: 0

--//使用ITL槽2.看看ITL槽2(從0開始)的情況:

BBED> p ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x000a

ub2 kxidslt @70 0x0021

ub4 kxidsqn @72 0x00005305

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x00c01833

ub2 kubaseq @80 0x10dc

ub1 kubarec @82 0x0d

ub2 ktbitflg @84 0x0002 (NONE)

union _ktbitun, 2 bytes @86

sb2 _ktbitfsc @86 9

ub2 _ktbitwrp @86 0x0009

ub4 ktbitbas @88 0x00000000

--//可以發現ktbitflg=0x0002,表示沒有提交.有點奇怪為什麼是0x0002,應該是0x0001.

--//注:我前面測試視乎mssm表空間會出現這樣的情況.

--//ktbitbas=0x00000000,也就是沒有scn相關資訊寫入.

BBED> assign offset 8166=0x2c;

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

ub1 rowdata[0] @8166 0x2c

BBED> x /rnc *kdbr[1]

rowdata[33] @8166

-----------

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

lock@8167: 0x02

cols@8168: 2

col 0[2] @8169: 2

col 1[4] @8172: test

BBED> sum apply

Check value for File 7, Block 129:

current = 0xf398, required = 0xf398

BBED> verify

DBVERIFY - Verification starting

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

BLOCK = 129

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

data header at 0x7f3667031274

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

used=83 fsc=9 avsp=7989 dtl=8072

Block 129 failed with check code 6110

--//我以前測試提到過這樣恢復,讀取是沒有問題,雖然verify時包如上的錯誤.

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

ROWID ID NAME

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

AAAWP2AAHAAAACBAAA 1 test

AAAWP2AAHAAAACBAAB 2 test

AAAWP2AAHAAAACBAAC 3 test

AAAWP2AAHAAAACBAAD 4 test

AAAWP2AAHAAAACBAAE 5 test

--//OK沒有任何問題,也就是說明oracle對於system表空間檢測更加嚴格.

SYS@book> alter system flush buffer_cache;

System altered.

--//再次透過bbed觀察:

BBED> p ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x000a

ub2 kxidslt @70 0x0021

ub4 kxidsqn @72 0x00005305

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x00c01833

ub2 kubaseq @80 0x10dc

ub1 kubarec @82 0x0d

ub2 ktbitflg @84 0x8000 (KTBFCOM)

union _ktbitun, 2 bytes @86

sb2 _ktbitfsc @86 3

ub2 _ktbitwrp @86 0x0003

ub4 ktbitbas @88 0x17752c40

--//已經修改提交了.

BBED> x /rnc *kdbr[1]

rowdata[33] @8166

-----------

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

lock@8167: 0x00

cols@8168: 2

col 0[2] @8169: 2

col 1[4] @8172: test

--//lock標識也清除了.

[20190125]bbed恢復資料遇到延遲塊清除的問題3.txt

相關文章