[20161123]关于Unused block.txt

最新推荐文章于 2024-09-15 08:03:51 发布

cuixun7780

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量96

点赞数

文章标签：数据库

[20161123]关于Unused block.txt

--关于Unused block我的理解是从来没有使用的块，oracle建立数据文件时要'格式化'块，写入一些相关信息。
--对比另外一个概念，就是NULL block,我的理解曾经被使用过，由于truncate或者drop，空间被回收了。

--简单探究一下Unused block。

1.环境：
SCOTT@book> @ &r/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 SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2.探究:
BBED> set dba 6,128
DBA 0x01800080 (25165952 6,128)

BBED> dump /v dba 6,128 count 8192 offset 0
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 128 Offsets: 0 to 8191 Dba:0x01800080
-----------------------------------------------------------------------------------------------------------
00a20000 80000000 00000000 00000105 80a70000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
....
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 01000000 l ................................
<32 bytes per line>

BBED> map /v
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 128 Dba:0x01800080
------------------------------------------------------------
BBED-00400: invalid blocktype (00)

--可以发现bbed无法正常显示Unused block信息。

SCOTT@book> alter system dump datafile 6 block 128;
System altered.

--看不出任何相关信息。找一个有数据的块对比看看。
BBED> p kcbh dba 4,131
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x01000083
   ub4 bas_kcbh                             @8        0x000e0ee4
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x705d
   ub2 spare3_kcbh                          @18       0x0000

BBED> p tailchk dba 4,131
ub4 tailchk @8188 0x0ee40602

--如果比如填充的话：
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x00
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00000080
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x05
   ub2 chkval_kcbh                          @16       0xa780
   ub2 spare3_kcbh                          @18       0x0000

tailchk = 0x00000001

--seq_kcbh 初始化是0x01x。flg_kcbh 块的标识类型?? 05 也许表示未使用。
--rdba_kcbh 并没有文件号。执行dba 6,128比较一目了然。

BBED> set dba 6,128
DBA 0x01800080 (25165952 6,128)

3.如果未使用的块破坏，理论上讲没有什么问题。

SYS@book> @ &r/bbvi 6 128
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 1048576 -s 8192 /mnt/ramdisk/book/sugar01.dbf

--//我全部置为0看看。
$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Nov 23 15:38:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
Page 128 is marked corrupt
Corrupt block relative dba: 0x01800080 (file 6, block 128)
Completely zero block found during dbv:

SYS@book> create table t4 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e3;
Table created.

SYS@book> column PARTITION_NAME noprint
SYS@book> select * from dba_extents where segment_name='T4';
OWNER SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS    T4                   TABLE              SUGAR                    0          6        128      65536          8            6
SYS    T4                   TABLE              SUGAR                    1          6        136      65536          8            6

--你可以发现dba =6,128块一样被使用了。
SYS@book> alter system checkpoint ;
System altered.

BBED> dump /v dba 6,128 count 128 offset 0
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 128 Offsets: 0 to 127 Dba:0x01800080
-----------------------------------------------------------------------------------------------------------
20a20000 80008001 7fff6900 00000404 4cf10000 00000000 00000000 00000000 l .........i.....L...............
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 04000000 ffffffff 07000000 03000000 10000000 l ................................
02000100 00000000 00000000 00000000 00000000 09000000 00000000 00000000 l ................................
<32 bytes per line>

4.最后探究一下tailchk：
BBED> p tailchk dba 6,131
BBED-00400: invalid blocktype (32)

--现在是FIRST LEVEL BITMAP BLOCK，bbed无法识别。

BBED> dump /v dba 6,128 offset 8188
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 128 Offsets: 8188 to 8191 Dba:0x01800080
-----------------------------------------------------------------------------------------------------------
04207fff l . ..
<32 bytes per line>

--一般intel机器有大小头问题，实际上是ff7f2004
--bas_kcbh (块scn)的低4位对应tailchk开头。0x20 我估计是块类型也就是块的第0字节(0x20). 0x04 应该对应 kcbh.seq_kcbh(块的偏移量14的位置)。

5.验证自己的判断：
--找一个数据块看看。
SYS@book> select rowid,id,name from sys.t4 where id=1;
ROWID ID NAME
------------------ ---------- --------------------------------
AAAVwDAAGAAAACDAAA 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SYS@book> @ &r/rowid AAAVwDAAGAAAACDAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
89091 6 131 0 0x1800083 6,131 alter system dump datafile 6 block 131 ;

BBED> p tailchk dba 6,131
ub4 tailchk @8188 0x02d20602

BBED> p kcbh.bas_kcbh dba 6,131
ub4 bas_kcbh @8 0x006a02d2
--低4位0x02d2.

BBED> p kcbh.type_kcbh dba 6,131
ub1 type_kcbh @0 0x06

BBED> p kcbh.seq_kcbh dba 6,131
ub1 seq_kcbh @14 0x02

--如果我改动这个块，看看。

SYS@book> update t4 set name = lpad('B',32,'B') where id=2;
1 row updated.

SYS@book> commit ;
Commit complete.

SYS@book> alter system checkpoint ;
System altered.

BBED> p tailchk dba 6,131
ub4 tailchk @8188 0x05a60602

BBED> p kcbh.bas_kcbh dba 6,131
ub4 bas_kcbh @8 0x006a05a6

BBED> p kcbh.seq_kcbh dba 6,131
ub1 seq_kcbh @14 0x02

6.再回过头看看dba=6,128看看。

BBED> dump /v dba 6,128 count 4 offset 8188
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 128 Offsets: 8188 to 8191 Dba:0x01800080
-----------------------------------------------------------------------------------------------------------
04207fff l . ..
<32 bytes per line>

BBED> dump /v dba 6,128 count 63 offset 0
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 128 Offsets: 0 to 62 Dba:0x01800080
-----------------------------------------------------------------------------------------------------------
20a20000 80008001 7fff6900 00000404 4cf10000 00000000 00000000 00000000 l .........i.....L...............
00000000 00000000 00000000 00000000 00000000 00000000 00000000 000000 l ...............................
<32 bytes per line>

--看看dba=6,129
BBED> dump /v dba 6,129 count 64 offset 0
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 129 Offsets: 0 to 63 Dba:0x01800081
-----------------------------------------------------------------------------------------------------------
21a20000 81008001 79ff6900 00000204 4cda0000 00000000 00000000 00000000 l !.......y.i.....L...............
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
<32 bytes per line>

BBED> dump /v dba 6,129 count 4 offset 8188
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 129 Offsets: 8188 to 8191 Dba:0x01800081
-----------------------------------------------------------------------------------------------------------
022179ff l .!y.
<32 bytes per line>

--看看dba=6,130
BBED> dump /v dba 6,130 count 64 offset 0
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 130 Offsets: 0 to 63 Dba:0x01800082
-----------------------------------------------------------------------------------------------------------
23a20000 82008001 8dff6900 00000104 f0f60000 00000000 00000000 00000000 l #.........i.....................
00000000 02000000 10000000 9c0a0000 01000000 01000000 08000000 89008001 l ................................
<32 bytes per line>

BBED> dump /v dba 6,130 count 4 offset 8188
File: /mnt/ramdisk/book/sugar01.dbf (6)
Block: 130 Offsets: 8188 to 8191 Dba:0x01800082
-----------------------------------------------------------------------------------------------------------
01238dff l .#..
<32 bytes per line>

--kcbh.seq_kcbh 什么时候变化不是非常了解。
--以上完全是基于我的猜测，也许完全不对。

7.百度google一下，看看kcbh.seq_kcbh含义：

ub1 seq_kcbh => Sequence   number, incremented for every change made to the block at the same SCN
ub1 flg_kcbh :
Flag:
0x01   New Block
0x02   Delayed Logging Change advanced SCN/seq 0x04 Check value saved - block XOR's to zero
0x08   Temporary block

--seq_kcbh??什么意思?不理解?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2128953/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2128953/