[20161107]關於資料檔案點陣圖區.txt

[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