[20161107]关于数据文件位图区.txt
--上个星期写一篇 [20161104]File Space Bitmap Block损坏能修复吗？.txt
链接：http://blog.itpub.net/267265/viewspace-2127826/
http://www.itpub.net/thread-2071023-1-1.html
--里面提到
--正常数据文件第1块OS。第2块是文件头，第3块开始-128块是位图区。
--我文件很小，估计在1块里面：
--8192*3=24576
--我这里犯了一个小错误：
--实际上准确的说法应该是：
数据文件块从0开始记数，第0块OS块，第1块是文件头，第2块位图区的头，第3-127块是位图区。这仅仅对11g有效,而且数据文件不能太
小。
1.环境：
SYS@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
SYS@book> select * from dba_extents where file_id=6 order by block_id;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T1 TABLE SUGAR 0 6 128 65536 8 6
SCOTT T1 TABLE SUGAR 1 6 136 65536 8 6
SCOTT T1 TABLE SUGAR 2 6 144 65536 8 6
SCOTT T1 TABLE SUGAR 3 6 152 65536 8 6
SCOTT T1 TABLE SUGAR 4 6 160 65536 8 6
....
--数据信息从128块开始写入。
2.再来看看第2,3块内容：
SYS@book> alter system dump datafile 6 block 2;
System altered.
SYS@book> alter system dump datafile 6 block 3;
System altered.
*** 2016-11-07 15:27:52.761
Start dump data blocks tsn: 7 file#:6 minblk 2 maxblk 2
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25165826
BH (0x6a7facc0) file#: 6 rdba: 0x01800002 (6/2) class: 13 ba: 0x6a7c8000
set: 62 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 24,28
dbwrid: 1 obj: -1 objn: 13 tsn: 7 afn: 6 hint: f
hash: [0x849ccd70,0x849ccd70] lru: [0x6a7faee8,0x6a3d7968]
ckptq: [NULL] fileq: [NULL] objq: [0x7cb877e8,0x7cb877e8] objaq: [0x7cb877d8,0x7cb877d8]
st: XCURRENT md: NULL fpin: 'kttewh00: ktte_get_file_info' tch: 4
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 7 rdba: 0x01800002 (6/2)
scn: 0x0000.00270924 seq: 0x01 flg: 0x04 tail: 0x09241d01
frmt: 0x02 chkval: 0x6789 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6769AEAA00 to 0x00007F6769AECA00
7F6769AEAA00 0000A21D 01800002 00270924 04010000 [........$.'.....]
7F6769AEAA10 00006789 00000006 00000008 00001400 [.g..............]
7F6769AEAA20 00000009 00000800 003FFFFE 0000007E [..........?.~...]
7F6769AEAA30 000013FF 00000190 000000E0 000E2008 [............. ..]
7F6769AEAA40 00000000 00000000 00000000 00000000 [................]
7F6769AEAA50 00000C80 00000080 00000000 00000000 [................]
7F6769AEAA60 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7F6769AEC9F0 00000000 00000000 00000000 09241D01 [..............$.]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 8, Size: 5120, Flag: 9
AutoExtend: YES, Increment: 2048, MaxSize: 4194302
Initial Area: 126, Tail: 5119, First: 400, Free: 224
Deallocation scn: 925704.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
*** 2016-11-07 15:27:55.161
Start dump data blocks tsn: 7 file#:6 minblk 3 maxblk 3
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25165827
BH (0x6a7dba48) file#: 6 rdba: 0x01800003 (6/3) class: 12 ba: 0x6a496000
set: 66 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 29,28
dbwrid: 2 obj: -1 objn: 13 tsn: 7 afn: 6 hint: f
hash: [0x84b857d0,0x84b857d0] lru: [0x6bbfa2b8,0x69bdfbd0]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktfbwh33: ktfbfvhlp' tch: 0 lfb: 33
flags:
Block dump from disk:
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0000.00270924 seq: 0x01 flg: 0x04 tail: 0x09241e01
frmt: 0x02 chkval: 0xb123 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6769AEAA00 to 0x00007F6769AECA00
7F6769AEAA00 0000A21E 01800003 00270924 04010000 [........$.'.....]
7F6769AEAA10 0000B123 00000006 00000080 00000000 [#...............]
7F6769AEAA20 00000190 0000F670 00000000 00000000 [....p...........]
7F6769AEAA30 00000000 00000000 FFFFFFFF FFFFFFFF [................]
7F6769AEAA40 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF [................]
Repeat 1 times
7F6769AEAA60 FFFFFFFF FFFFFFFF 0000FFFF 00000000 [................]
...
0000000000000000 0000000000000000 0000000000000000 0000000000000000
End dump data blocks tsn: 7 file#: 6 minblk 3 maxblk 3
--从转储可以看出：第2块是 0x1d=KTFB Bitmapped File Space Header，第3块 0x1e=KTFB Bitmapped File Space Bitmap.
3.实际上如果你建立想到另外的问题，11G下8k数据块，这样建立的数据文件最小是多少？
--如果数据文件小于1M呢？大家可以参考我以前写的blog：
http://blog.itpub.net/267265/viewspace-1399275/
http://blog.itpub.net/267265/viewspace-1399890/
http://blog.itpub.net/267265/viewspace-1404262/
http://blog.itpub.net/267265/viewspace-1400603/
--我的测试最小是88K。实际上数据文件大小88+8=96K(对于8K的数据块)，删除重建看看。
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 80k AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 88k AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SYS@book> alter system dump datafile 6 block min 2 block max 11;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_50921.trc
buffer tsn: 7 rdba: 0x01800002 (6/2)
frmt: 0x02 chkval: 0x6d74 type: 0x1d=KTFB Bitmapped File Space Header
buffer tsn: 7 rdba: 0x01800003 (6/3)
frmt: 0x02 chkval: 0x41b7 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 7 rdba: 0x00000004 (0/4)
frmt: 0x02 chkval: 0xa704 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000005 (0/5)
frmt: 0x02 chkval: 0xa705 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000006 (0/6)
frmt: 0x02 chkval: 0xa706 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000007 (0/7)
frmt: 0x02 chkval: 0xa707 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000008 (0/8)
frmt: 0x02 chkval: 0xa708 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000009 (0/9)
frmt: 0x02 chkval: 0xa709 type: 0x00=unknown
buffer tsn: 7 rdba: 0x0000000a (0/10)
frmt: 0x02 chkval: 0xa70a type: 0x00=unknown
buffer tsn: 7 rdba: 0x0000000b (0/11)
frmt: 0x02 chkval: 0xa70b type: 0x00=unknown
--第2块是位图区头KTFB Bitmapped File Space Header，第3块是位图区块KTFB Bitmapped File Space Bitmap。
SCOTT@book> create table xx tablespace sugar as select * from emp where rownum=1;
Table created.
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where file_id=6 order by block_id;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT XX TABLE SUGAR 0 6 4 65536 8 6
--从第4块开始放数据信息。
4.10g的情况有一些特殊：
--具体不测试了，注意从0开始记数：
--实际上10g上位图区块是3－8块，2块是位图块头。
--11G下建立文件>=1080时，保留128块，位图区块是3－127，2块是位图块头。
--11g下建立文件<1080K,>=120k,位图区块是3－7块，2块是位图块头.
--11g下建立文件<120K,>=88k,位图区块是3块，2块是位图块头.
--11g下建立文件<88K,无法建立。ORA-03214: File Size specified is smaller than minimum required
--注意88K这么小的数据文件，位图区才1块，能扩展到32G吗？看看以前我写的blog就知道了，实际上利用数据文件的尾部做为位图区。
--在位图文件头的tail记录最后1个数据块，后面跟着的就是位图区。例子：
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194290, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194280, First: 0, Free: 716
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
--我们可以看到位图区位于4194281,4194282,4194283.
--大家可以自行验证。
--测试方法很简单：
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE &1 k AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
alter system dump datafile 6 block min 2 block max &2;
--查询转储文件，执行如下：
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_51017.trc
buffer tsn: 7 rdba: 0x01800002 (6/2)
frmt: 0x02 chkval: 0x6d71 type: 0x1d=KTFB Bitmapped File Space Header
buffer tsn: 7 rdba: 0x01800003 (6/3)
frmt: 0x02 chkval: 0x41b7 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 7 rdba: 0x00000004 (0/4)
frmt: 0x02 chkval: 0xa704 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000005 (0/5)
frmt: 0x02 chkval: 0xa705 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000006 (0/6)
frmt: 0x02 chkval: 0xa706 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000007 (0/7)
frmt: 0x02 chkval: 0xa707 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000008 (0/8)
frmt: 0x02 chkval: 0xa708 type: 0x00=unknown
buffer tsn: 7 rdba: 0x00000009 (0/9)
frmt: 0x02 chkval: 0xa709 type: 0x00=unknown
buffer tsn: 7 rdba: 0x0000000a (0/10)
frmt: 0x02 chkval: 0xa70a type: 0x00=unknown
buffer tsn: 7 rdba: 0x0000000b (0/11)
frmt: 0x02 chkval: 0xa70b type: 0x00=unknown
buffer tsn: 7 rdba: 0x0000000c (0/12)
frmt: 0x02 chkval: 0xa70c type: 0x00=unknown
buffer tsn: 7 rdba: 0x0000000d (0/13)
frmt: 0x02 chkval: 0xa70d type: 0x00=unknown
buffer tsn: 7 rdba: 0x0000000e (0/14)
frmt: 0x02 chkval: 0xa70e type: 0x00=unknown
