Truncate异常恢复一-truncate前后对比
Truncate异常恢复一之Truncate前后对比
Truncate异常恢复二之DBMS恢复
Truncate异常恢复三之GDUL恢复
Truncate异常恢复四之BBED恢复
Truncate异常恢复四之BBED恢复
对象基础信息
构建测试表，测试前先记录表的基础信息便于分析
SQL> create table devin.test_trun as select * from dba_objects;
Table created.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system checkpoint ;
System altered.
SQL> SET LINESIZE 300
select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS,EXTENTS from dba_segments where segment_name='TEST_TRUN';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
-------------------- ----------- ------------ ---------- ----------
TEST_TRUN 9 154 1536 27
SQL> SELECT SEGMENT_NAME,EXTENT_ID,BLOCK_ID FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_TRUN';
SEGMENT_NAME EXTENT_ID BLOCK_ID
-------------------- ---------- ----------
TEST_TRUN 0 152
TEST_TRUN 1 168
TEST_TRUN 2 176
TEST_TRUN 3 184
TEST_TRUN 4 192
TEST_TRUN 5 200
TEST_TRUN 6 208
TEST_TRUN 7 216
TEST_TRUN 8 224
TEST_TRUN 9 232
TEST_TRUN 10 240
TEST_TRUN 11 248
TEST_TRUN 12 256
TEST_TRUN 13 264
TEST_TRUN 14 272
TEST_TRUN 15 280
TEST_TRUN 16 384
TEST_TRUN 17 512
TEST_TRUN 18 640
TEST_TRUN 19 768
TEST_TRUN 20 896
TEST_TRUN 21 1024
TEST_TRUN 22 1152
TEST_TRUN 23 1280
TEST_TRUN 24 1408
TEST_TRUN 25 1536
TEST_TRUN 26 1664
27 rows selected.
SQL> @tracefile
VALUE
-----------------------------------------------------------------
/u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_6772.trc
SQL> alter system dump datafile 9 block min 152 block max 154;
System altered.
复制Truncate前DUMP分析
[oracle@devin-enmo script]$ more /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_6772.trc
Trace file /u01/app/oracle/diag/rdbms/hkora/hkora/trace/hkora_ora_6772.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...
...
Start dump data blocks tsn: 9 file#:9 minblk 152 maxblk 154
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748888
Block dump from disk:
buffer tsn: 9 rdba: 0x02400098 (9/152)
scn: 0x2818bbb seq: 0x03 flg: 0x04 tail: 0x8bbb2003
frmt: 0x02 chkval: 0xb43a type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0463C29000 to 0x00007F0463C2B000
7F0463C29000 0000A220 02400098 02818BBB 04030000 [ .....@.........]
...
...
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x02400099 poffset: 0
unformatted: 0 total: 16 first useful block: 3
owning instance : 0
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0 nf5 0
Extent Map Block Offset: 4294967295
First free datablock : 16
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Dealloc scn(ub4/ub4): (0x00000000.02818b68)
Format scn: 0x0000000002818bbb
Flag: 0x00000280 (REJCTX/-/AUX/-/-/-/-/-)
Inc #: 0 Objd: 78581
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02400098 Length: 8 Offset: 0
0x024000a8 Length: 8 Offset: 8
0:Metadata 1:Metadata 2:Metadata 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
--------------------------------------------------------
ktspfsc -
nro:0 ncmp:0 nff:0 nxo:0 lastxs:0 nxid:0 ff:0
clntime: 0 addtime:0 spare1:0 spare2:0
ro: rejection opcode, xo: xid offset List
0. ro:0 xo:-1 1. ro:0 xo:-1 2. ro:0 xo:-1 3. ro:0 xo:-1
4. ro:0 xo:-1 5. ro:0 xo:-1 6. ro:0 xo:-1 7. ro:0 xo:-1
8. ro:0 xo:-1 9. ro:0 xo:-1 10. ro:0 xo:-1 11. ro:0 xo:-1
12. ro:0 xo:-1 13. ro:0 xo:-1 14. ro:0 xo:-1 15. ro:0 xo:-1
ktspfsc xidlist -
0. NULL 1. NULL 2. NULL
3. NULL 4. NULL 5. NULL
6. NULL 7. NULL 8. NULL
9. NULL 10. NULL 11. NULL
12. NULL 13. NULL 14. NULL
15. NULL 16. NULL 17. NULL
18. NULL 19. NULL 20. NULL
21. NULL 22. NULL 23. NULL
24. NULL 25. NULL 26. NULL
27. NULL 28. NULL 29. NULL
30. NULL 31. NULL 32. NULL
33. NULL 34. NULL 35. NULL
36. NULL 37. NULL 38. NULL
39. NULL 40. NULL 41. NULL
42. NULL 43. NULL 44. NULL
45. NULL 46. NULL 47. NULL
48. NULL 49. NULL 50. NULL
51. NULL 52. NULL 53. NULL
54. NULL 55. NULL 56. NULL
57. NULL 58. NULL 59. NULL
60. NULL 61. NULL 62. NULL
63. NULL
ktspfsr not configured -
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748889
Block dump from disk:
buffer tsn: 9 rdba: 0x02400099 (9/153)
scn: 0x2818bbb seq: 0x1d flg: 0x04 tail: 0x8bbb211d
frmt: 0x02 chkval: 0xb76d type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0463C29000 to 0x00007F0463C2B000
7F0463C29000 0000A221 02400099 02818BBB 041D0000 [!.....@.........]
...
...
Dump of Second Level Bitmap Block
number: 30 nfree: 2 ffree: 28 pdba: 0x0240009a
Inc #: 0 Objd: 78581 Flag: 3
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x02400098 Free: 1 Inst: 1
0x024000b0 Free: 1 Inst: 1
0x024000c0 Free: 1 Inst: 1
0x024000d0 Free: 1 Inst: 1
0x024000e0 Free: 1 Inst: 1
0x024000f0 Free: 1 Inst: 1
0x02400100 Free: 1 Inst: 1
0x02400110 Free: 1 Inst: 1
0x02400180 Free: 1 Inst: 1
0x02400181 Free: 1 Inst: 1
0x02400200 Free: 1 Inst: 1
0x02400201 Free: 1 Inst: 1
0x02400280 Free: 1 Inst: 1
0x02400281 Free: 1 Inst: 1
0x02400300 Free: 1 Inst: 1
0x02400301 Free: 1 Inst: 1
0x02400380 Free: 1 Inst: 1
0x02400381 Free: 1 Inst: 1
0x02400400 Free: 1 Inst: 1
0x02400401 Free: 1 Inst: 1
0x02400480 Free: 1 Inst: 1
0x02400481 Free: 1 Inst: 1
0x02400500 Free: 1 Inst: 1
0x02400501 Free: 1 Inst: 1
0x02400580 Free: 1 Inst: 1
0x02400581 Free: 1 Inst: 1
0x02400600 Free: 1 Inst: 1
0x02400601 Free: 1 Inst: 1
0x02400680 Free: 7 Inst: 1
0x02400681 Free: 7 Inst: 1
--------------------------------------------------------
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748890
BH (0x75bf1b78) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75ae4000
set: 20 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: 78581 tsn: [0/9] afn: 9 hint: f
hash: [0x767e19a8,0x6e7d4bb0] lru: [0x76fd9e18,0x767e2a58]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x2818f29],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x2818f29],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 13
01. sid:01 L965:MK_CRL:ulnk:objq 02. sid:01 L212:zib:bic:FSQ
03. sid:01 L122:zgb:set:st 04. sid:01 L830:olq1:clr:WRT+CKT
05. sid:01 L951:zgb:lnk:objq 06. sid:01 L372:zgb:set:MEXCL
07. sid:01 L123:zgb:no:FEN 08. sid:01 L083:zgb:ent:fn
09. sid:02 L965:MK_CRL:ulnk:objq 10. sid:02 L212:zib:bic:FSQ
11. sid:02 L122:zgb:set:st 12. sid:02 L830:olq1:clr:WRT+CKT
13. sid:02 L951:zgb:lnk:objq 14. sid:02 L372:zgb:set:MEXCL
15. sid:02 L123:zgb:no:FEN 16. sid:02 L896:z_mkfr:ulnk:objq
BH (0x767e18f8) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x7656c000
set: 19 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: 78581 tsn: [0/9] afn: 9 hint: f
hash: [0x75bdd0c8,0x75bf1c28] lru: [0x763e4b58,0x763e6db8]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x2818f28],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x2818f28],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 3
01. sid:01 L965:MK_CRL:ulnk:objq 02. sid:01 L212:zib:bic:FSQ
03. sid:01 L122:zgb:set:st 04. sid:01 L830:olq1:clr:WRT+CKT
05. sid:01 L951:zgb:lnk:objq 06. sid:01 L372:zgb:set:MEXCL
07. sid:01 L123:zgb:no:FEN 08. sid:01 L083:zgb:ent:fn
09. sid:02 L965:MK_CRL:ulnk:objq 10. sid:02 L212:zib:bic:FSQ
11. sid:02 L122:zgb:set:st 12. sid:02 L830:olq1:clr:WRT+CKT
13. sid:02 L951:zgb:lnk:objq 14. sid:02 L372:zgb:set:MEXCL
15. sid:02 L123:zgb:no:FEN 16. sid:02 L896:z_mkfr:ulnk:objq
BH (0x75bdd018) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75902000
set: 24 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x75bdb3e8,0x767e19a8] lru: [0x763d2a98,0x75bdf1f8]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x2818ea9],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x2818ea9],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 11
01. sid:02 L965:MK_CRL:ulnk:objq 02. sid:02 L212:zib:bic:FSQ
03. sid:02 L122:zgb:set:st 04. sid:02 L830:olq1:clr:WRT+CKT
05. sid:02 L951:zgb:lnk:objq 06. sid:02 L372:zgb:set:MEXCL
07. sid:02 L123:zgb:no:FEN 08. sid:02 L083:zgb:ent:fn
09. sid:09 L965:MK_CRL:ulnk:objq 10. sid:09 L212:zib:bic:FSQ
11. sid:09 L122:zgb:set:st 12. sid:09 L830:olq1:clr:WRT+CKT
13. sid:09 L951:zgb:lnk:objq 14. sid:09 L372:zgb:set:MEXCL
15. sid:09 L123:zgb:no:FEN 16. sid:09 L896:z_mkfr:ulnk:objq
BH (0x75bdb338) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x758d8000
set: 23 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x75bd3568,0x75bdd0c8] lru: [0x75bd8c38,0x76febed8]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x2818ea8],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x2818ea8],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 13
01. sid:02 L965:MK_CRL:ulnk:objq 02. sid:02 L212:zib:bic:FSQ
03. sid:02 L122:zgb:set:st 04. sid:02 L830:olq1:clr:WRT+CKT
05. sid:02 L951:zgb:lnk:objq 06. sid:02 L372:zgb:set:MEXCL
07. sid:02 L123:zgb:no:FEN 08. sid:02 L083:zgb:ent:fn
09. sid:09 L965:MK_CRL:ulnk:objq 10. sid:09 L212:zib:bic:FSQ
11. sid:09 L122:zgb:set:st 12. sid:09 L830:olq1:clr:WRT+CKT
13. sid:09 L951:zgb:lnk:objq 14. sid:09 L372:zgb:set:MEXCL
15. sid:09 L123:zgb:no:FEN 16. sid:09 L896:z_mkfr:ulnk:objq
BH (0x75bd34b8) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75820000
set: 22 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x75ff29e8,0x75bdb3e8] lru: [0x757db578,0x767eda58]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x2818ea7],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x2818ea7],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 15
01. sid:02 L965:MK_CRL:ulnk:objq 02. sid:02 L212:zib:bic:FSQ
03. sid:02 L122:zgb:set:st 04. sid:02 L830:olq1:clr:WRT+CKT
05. sid:02 L951:zgb:lnk:objq 06. sid:02 L372:zgb:set:MEXCL
07. sid:02 L123:zgb:no:FEN 08. sid:02 L083:zgb:ent:fn
09. sid:09 L965:MK_CRL:ulnk:objq 10. sid:09 L212:zib:bic:FSQ
11. sid:09 L122:zgb:set:st 12. sid:09 L830:olq1:clr:WRT+CKT
13. sid:09 L951:zgb:lnk:objq 14. sid:09 L372:zgb:set:MEXCL
15. sid:09 L123:zgb:no:FEN 16. sid:09 L083:zgb:ent:fn
BH (0x75ff2938) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75ef8000
set: 21 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x6e7d4bb0,0x75bd3568] lru: [0x76be36b8,0x75ff37d8]
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1 lfb: 43
cr: [scn: 0x2818ea2],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x2818ea2],[sfl: 0x0],[lc: 0x0]
flags:
Printing buffer operation history (latest change first):
cnt: 7
01. sid:02 L965:MK_CRL:ulnk:objq 02. sid:02 L212:zib:bic:FSQ
03. sid:02 L122:zgb:set:st 04. sid:02 L830:olq1:clr:WRT+CKT
05. sid:02 L951:zgb:lnk:objq 06. sid:02 L372:zgb:set:MEXCL
07. sid:02 L123:zgb:no:FEN 08. sid:02 L896:z_mkfr:ulnk:objq
09. sid:02 L083:zgb:ent:fn 10. sid:09 L338:zibmlt:set:MSHR
11. sid:09 L144:zibmlt:mk:EXCL 12. sid:09 L122:zgb:set:st
13. sid:09 L830:olq1:clr:WRT+CKT 14. sid:09 L951:zgb:lnk:objq
15. sid:09 L372:zgb:set:MEXCL 16. sid:09 L123:zgb:no:FEN
Block dump from disk:
buffer tsn: 9 rdba: 0x0240009a (9/154)
scn: 0x2818bbb seq: 0x03 flg: 0x04 tail: 0x8bbb2303
frmt: 0x02 chkval: 0x906d type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0463C29000 to 0x00007F0463C2B000
7F0463C29000 0000A223 0240009A 02818BBB 04030000 [#.....@.........]
...
...
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 27 #blocks: 1536
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x024006ac ext#: 26 blk#: 44 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1452
mapblk 0x00000000 offset: 26
Disk Lock:: Locked by xid: 0x0002.01a.00000a88
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x024006ac ext#: 26 blk#: 44 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1452
mapblk 0x00000000 offset: 26
Level 1 BMB for High HWM block: 0x02400680
Level 1 BMB for Low HWM block: 0x02400680
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x02400099
Last Level 1 BMB: 0x02400681
Last Level II BMB: 0x02400099
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 27 obj#: 78581 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x02400098 length: 8
0x024000a8 length: 8
0x024000b0 length: 8
0x024000b8 length: 8
0x024000c0 length: 8
0x024000c8 length: 8
0x024000d0 length: 8
0x024000d8 length: 8
0x024000e0 length: 8
0x024000e8 length: 8
0x024000f0 length: 8
0x024000f8 length: 8
0x02400100 length: 8
0x02400108 length: 8
0x02400110 length: 8
0x02400118 length: 8
0x02400180 length: 128
0x02400200 length: 128
0x02400280 length: 128
0x02400300 length: 128
0x02400380 length: 128
0x02400400 length: 128
0x02400480 length: 128
0x02400500 length: 128
0x02400580 length: 128
0x02400600 length: 128
0x02400680 length: 128
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02400098 Data dba: 0x0240009b
Extent 1 : L1 dba: 0x02400098 Data dba: 0x024000a8
Extent 2 : L1 dba: 0x024000b0 Data dba: 0x024000b1
Extent 3 : L1 dba: 0x024000b0 Data dba: 0x024000b8
Extent 4 : L1 dba: 0x024000c0 Data dba: 0x024000c1
Extent 5 : L1 dba: 0x024000c0 Data dba: 0x024000c8
Extent 6 : L1 dba: 0x024000d0 Data dba: 0x024000d1
Extent 7 : L1 dba: 0x024000d0 Data dba: 0x024000d8
Extent 8 : L1 dba: 0x024000e0 Data dba: 0x024000e1
Extent 9 : L1 dba: 0x024000e0 Data dba: 0x024000e8
Extent 10 : L1 dba: 0x024000f0 Data dba: 0x024000f1
Extent 11 : L1 dba: 0x024000f0 Data dba: 0x024000f8
Extent 12 : L1 dba: 0x02400100 Data dba: 0x02400101
Extent 13 : L1 dba: 0x02400100 Data dba: 0x02400108
Extent 14 : L1 dba: 0x02400110 Data dba: 0x02400111
Extent 15 : L1 dba: 0x02400110 Data dba: 0x02400118
Extent 16 : L1 dba: 0x02400180 Data dba: 0x02400182
Extent 17 : L1 dba: 0x02400200 Data dba: 0x02400202
Extent 18 : L1 dba: 0x02400280 Data dba: 0x02400282
Extent 19 : L1 dba: 0x02400300 Data dba: 0x02400302
Extent 20 : L1 dba: 0x02400380 Data dba: 0x02400382
Extent 21 : L1 dba: 0x02400400 Data dba: 0x02400402
Extent 22 : L1 dba: 0x02400480 Data dba: 0x02400482
Extent 23 : L1 dba: 0x02400500 Data dba: 0x02400502
Extent 24 : L1 dba: 0x02400580 Data dba: 0x02400582
Extent 25 : L1 dba: 0x02400600 Data dba: 0x02400602
Extent 26 : L1 dba: 0x02400680 Data dba: 0x02400682
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x02400099
End dump data blocks tsn: 9 file#: 9 minblk 152 maxblk 154
复制Truncate后dump分析
SQL> truncate table devin.test_trun; Table truncated. SQL> @flush_sga System altered. System altered. SQL> alter system dump datafile 9 block min 152 block max 154; System altered.复制
*** 2021-10-22T08:48:09.147698+08:00
Start dump data blocks tsn: 9 file#:9 minblk 152 maxblk 154
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748888
BH (0x763ed558) file#: 9 rdba: 0x02400098 (9/152) class: 8 ba: 0x7627e000
set: 21 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: 78581 tsn: [0/9] afn: 9 hint: f
hash: [0x6e7b9b50,0x6e7b9b50] lru: [0x76be6c58,0x6dd78430]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktspfwh51: ktspcdobj' fscn: 0x2818fef tch: 0 lfb: 15
flags: foreground_waiting
Printing buffer operation history (latest change first):
cnt: 15
01. sid:02 L896:z_mkfr:ulnk:objq 02. sid:02 L643:rlb:bis:REU
03. sid:12 L187:bwrdone:bic:FCW 04. sid:12 L936:zwrcl:swp:cq
05. sid:12 L206:zwrcl:bic:FBD 06. sid:12 L072:zwrcl:ent:ob
07. sid:12 L607:bxsv:bis:FFW 08. sid:02 L644:rlb:bis:FFG
09. sid:02 L464:chg1_mn:bic:FMS 10. sid:02 L778:chg1_mn:bis:FMS
11. sid:02 L464:chg1_mn:bic:FMS 12. sid:02 L614:chg1_mn:bis:FBD
13. sid:02 L922:klbc:sw:cq 14. sid:02 L778:chg1_mn:bis:FMS
15. sid:02 L144:zibmlt:mk:EXCL 16. sid:02 L210:zibmlt:bic:FSQ
Block dump from disk:
buffer tsn: 9 rdba: 0x02400098 (9/152)
scn: 0x2818fef seq: 0x02 flg: 0x04 tail: 0x8fef2002
frmt: 0x02 chkval: 0xa8b3 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0463C29000 to 0x00007F0463C2B000
7F0463C29000 0000A220 02400098 02818FEF 04020000 [ .....@.........]
...
...
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x02400099 poffset: 0
unformatted: 5 total: 8 first useful block: 3
owning instance : 0
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0 nf5 0
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 9
Locker xid: : 0x0009.006.00000ae6
Dealloc scn(ub4/ub4): (0x00000000.02818fef)
Format scn: 0x0000000002818bbb
Flag: 0x000002a1 (REJCTX/-/AUX/-/OBJD/-/-/-)
Inc #: 0 Objd: 78582
HWM Flag: HWM Set
Highwater:: 0x0240009b ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02400098 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
--------------------------------------------------------
ktspfsc -
nro:0 ncmp:0 nff:0 nxo:0 lastxs:0 nxid:0 ff:0
clntime: 0 addtime:0 spare1:0 spare2:0
ro: rejection opcode, xo: xid offset List
0. ro:0 xo:-1 1. ro:0 xo:-1 2. ro:0 xo:-1 3. ro:0 xo:-1
4. ro:0 xo:-1 5. ro:0 xo:-1 6. ro:0 xo:-1 7. ro:0 xo:-1
ktspfsc xidlist -
0. NULL 1. NULL 2. NULL
3. NULL 4. NULL 5. NULL
6. NULL 7. NULL 8. NULL
9. NULL 10. NULL 11. NULL
12. NULL 13. NULL 14. NULL
15. NULL 16. NULL 17. NULL
18. NULL 19. NULL 20. NULL
21. NULL 22. NULL 23. NULL
24. NULL 25. NULL 26. NULL
27. NULL 28. NULL 29. NULL
30. NULL 31. NULL 32. NULL
33. NULL 34. NULL 35. NULL
36. NULL 37. NULL 38. NULL
39. NULL 40. NULL 41. NULL
42. NULL 43. NULL 44. NULL
45. NULL 46. NULL 47. NULL
48. NULL 49. NULL 50. NULL
51. NULL 52. NULL 53. NULL
54. NULL 55. NULL 56. NULL
57. NULL 58. NULL 59. NULL
60. NULL 61. NULL 62. NULL
63. NULL
ktspfsr not configured -
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748889
Block dump from disk:
buffer tsn: 9 rdba: 0x02400099 (9/153)
scn: 0x2818ff1 seq: 0x02 flg: 0x04 tail: 0x8ff12102
frmt: 0x02 chkval: 0xbd85 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0463C29000 to 0x00007F0463C2B000
7F0463C29000 0000A221 02400099 02818FF1 04020000 [!.....@.........]
...
...
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x0240009a
Inc #: 0 Objd: 78582 Flag: 3
opcode:4
xid:
L1 Ranges :
--------------------------------------------------------
0x02400098 Free: 7 Inst: 1
--------------------------------------------------------
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748890
BH (0x75bf1b78) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75ae4000
set: 20 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: 78581 tsn: [0/9] afn: 9 hint: f
hash: [0x767e19a8,0x6e7d4bb0] lru: [0x757fa318,0x75bf1f18]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 69
flags:
Printing buffer operation history (latest change first):
cnt: 14
01. sid:02 L662:kflb:bis:REU 02. sid:01 L965:MK_CRL:ulnk:objq
03. sid:01 L212:zib:bic:FSQ 04. sid:01 L122:zgb:set:st
05. sid:01 L830:olq1:clr:WRT+CKT 06. sid:01 L951:zgb:lnk:objq
07. sid:01 L372:zgb:set:MEXCL 08. sid:01 L123:zgb:no:FEN
09. sid:01 L083:zgb:ent:fn 10. sid:02 L965:MK_CRL:ulnk:objq
11. sid:02 L212:zib:bic:FSQ 12. sid:02 L122:zgb:set:st
13. sid:02 L830:olq1:clr:WRT+CKT 14. sid:02 L951:zgb:lnk:objq
15. sid:02 L372:zgb:set:MEXCL 16. sid:02 L123:zgb:no:FEN
BH (0x767e18f8) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x7656c000
set: 19 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: 78581 tsn: [0/9] afn: 9 hint: f
hash: [0x75bdd0c8,0x75bf1c28] lru: [0x75bf0e98,0x76fd4b98]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 69
flags:
Printing buffer operation history (latest change first):
cnt: 4
01. sid:02 L662:kflb:bis:REU 02. sid:01 L965:MK_CRL:ulnk:objq
03. sid:01 L212:zib:bic:FSQ 04. sid:01 L122:zgb:set:st
05. sid:01 L830:olq1:clr:WRT+CKT 06. sid:01 L951:zgb:lnk:objq
07. sid:01 L372:zgb:set:MEXCL 08. sid:01 L123:zgb:no:FEN
09. sid:01 L083:zgb:ent:fn 10. sid:02 L965:MK_CRL:ulnk:objq
11. sid:02 L212:zib:bic:FSQ 12. sid:02 L122:zgb:set:st
13. sid:02 L830:olq1:clr:WRT+CKT 14. sid:02 L951:zgb:lnk:objq
15. sid:02 L372:zgb:set:MEXCL 16. sid:02 L123:zgb:no:FEN
BH (0x75bdd018) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75902000
set: 24 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x75bdb3e8,0x767e19a8] lru: [0x76bf3d58,0x75be0538]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 69
flags:
Printing buffer operation history (latest change first):
cnt: 12
01. sid:02 L662:kflb:bis:REU 02. sid:02 L965:MK_CRL:ulnk:objq
03. sid:02 L212:zib:bic:FSQ 04. sid:02 L122:zgb:set:st
05. sid:02 L830:olq1:clr:WRT+CKT 06. sid:02 L951:zgb:lnk:objq
07. sid:02 L372:zgb:set:MEXCL 08. sid:02 L123:zgb:no:FEN
09. sid:02 L083:zgb:ent:fn 10. sid:09 L965:MK_CRL:ulnk:objq
11. sid:09 L212:zib:bic:FSQ 12. sid:09 L122:zgb:set:st
13. sid:09 L830:olq1:clr:WRT+CKT 14. sid:09 L951:zgb:lnk:objq
15. sid:09 L372:zgb:set:MEXCL 16. sid:09 L123:zgb:no:FEN
BH (0x75bdb338) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x758d8000
set: 23 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x75bd3568,0x75bdd0c8] lru: [0x767f4858,0x75bd8d98]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 69
flags:
Printing buffer operation history (latest change first):
cnt: 14
01. sid:02 L662:kflb:bis:REU 02. sid:02 L965:MK_CRL:ulnk:objq
03. sid:02 L212:zib:bic:FSQ 04. sid:02 L122:zgb:set:st
05. sid:02 L830:olq1:clr:WRT+CKT 06. sid:02 L951:zgb:lnk:objq
07. sid:02 L372:zgb:set:MEXCL 08. sid:02 L123:zgb:no:FEN
09. sid:02 L083:zgb:ent:fn 10. sid:09 L965:MK_CRL:ulnk:objq
11. sid:09 L212:zib:bic:FSQ 12. sid:09 L122:zgb:set:st
13. sid:09 L830:olq1:clr:WRT+CKT 14. sid:09 L951:zgb:lnk:objq
15. sid:09 L372:zgb:set:MEXCL 16. sid:09 L123:zgb:no:FEN
BH (0x75bd34b8) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75820000
set: 22 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x75ff29e8,0x75bdb3e8] lru: [0x757db998,0x75bd69d8]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 69
flags:
Printing buffer operation history (latest change first):
cnt: 16
01. sid:02 L662:kflb:bis:REU 02. sid:02 L965:MK_CRL:ulnk:objq
03. sid:02 L212:zib:bic:FSQ 04. sid:02 L122:zgb:set:st
05. sid:02 L830:olq1:clr:WRT+CKT 06. sid:02 L951:zgb:lnk:objq
07. sid:02 L372:zgb:set:MEXCL 08. sid:02 L123:zgb:no:FEN
09. sid:02 L083:zgb:ent:fn 10. sid:09 L965:MK_CRL:ulnk:objq
11. sid:09 L212:zib:bic:FSQ 12. sid:09 L122:zgb:set:st
13. sid:09 L830:olq1:clr:WRT+CKT 14. sid:09 L951:zgb:lnk:objq
15. sid:09 L372:zgb:set:MEXCL 16. sid:09 L123:zgb:no:FEN
BH (0x75ff2938) file#: 9 rdba: 0x0240009a (9/154) class: 4 ba: 0x75ef8000
set: 21 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 78581 objn: -1 tsn: [0/9] afn: 9 hint: f
hash: [0x6e7d4bb0,0x75bd3568] lru: [0x767e9b18,0x763ee978]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL]
objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 69
flags:
Printing buffer operation history (latest change first):
cnt: 8
01. sid:02 L662:kflb:bis:REU 02. sid:02 L965:MK_CRL:ulnk:objq
03. sid:02 L212:zib:bic:FSQ 04. sid:02 L122:zgb:set:st
05. sid:02 L830:olq1:clr:WRT+CKT 06. sid:02 L951:zgb:lnk:objq
07. sid:02 L372:zgb:set:MEXCL 08. sid:02 L123:zgb:no:FEN
09. sid:02 L896:z_mkfr:ulnk:objq 10. sid:02 L083:zgb:ent:fn
11. sid:09 L338:zibmlt:set:MSHR 12. sid:09 L144:zibmlt:mk:EXCL
13. sid:09 L122:zgb:set:st 14. sid:09 L830:olq1:clr:WRT+CKT
15. sid:09 L951:zgb:lnk:objq 16. sid:09 L372:zgb:set:MEXCL
Block dump from disk:
buffer tsn: 9 rdba: 0x0240009a (9/154)
scn: 0x2819013 seq: 0x03 flg: 0x04 tail: 0x90132303
frmt: 0x02 chkval: 0x9131 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F0463C29000 to 0x00007F0463C2B000
7F0463C29000 0000A223 0240009A 02819013 04030000 [#.....@.........]
...
...
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0240009b ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Disk Lock:: Locked by xid: 0x0009.021.00000ae5
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0240009b ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x02400098
Level 1 BMB for Low HWM block: 0x02400098
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x02400099
Last Level 1 BMB: 0x02400098
Last Level II BMB: 0x02400099
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 78582 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x02400098 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02400098 Data dba: 0x0240009b
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x02400099
End dump data blocks tsn: 9 file#: 9 minblk 152 maxblk 154
复制Truncate前后对比
1，对基表的修改
修改obj$，tab$的dataobj#
修改seg$的对应信息如(extents,blocks,hwmincr等等)
删除tab_stats$对应对象的统计信息
2，对于segment header：
修改块的dataobj#
修改LHWM和HHWM
修改extent map、aux map以及extents,blk个数
3，对于L2位图块：
删除L1 ranges
修改L2块的dataobj#
4，对于第一个L1位图块：
修改第一个L1块的dataobj#
set hwm为ext#为0的第3+1个块(即段头块+1）
5，对于HWM block所属的L1位图块：
clear HWM flag
复制总结
truncate的实质是在不修改数据块的情况下，通过修改segment header的data_object_id,hwm,extent map，aux map等信息来实现清空表的目的，其中还涉及数据字典基表以及L1、L2位图块的修改。
通过10046跟踪我们可以发现，全表扫描会访问segment header，但是不会访问L1、L2，访问的数据字典基表包括user、obj、tab、tab_stats、ts、seg、ind、ind_stats、col、objauth、cdef、histgrm、hist_head，其中tab_stats(统计信息不用管)，seg经过测试只要block#，file#，ts#不被更改就无需理会，而truncate操作是不会修改seg的ts#、file#、block#的。
所以需要恢复的元数据块和数据字典基表以及内容为：
- segment header(dataobj#,LHWM,HHWM,extent map,aux map以及extents个数)
- tab$(dataobj#)
- obj$(dataobj#)
最后修改时间：2021-11-30 08:39:47
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
