明明白白使用数据块 ----数据块格式深入解析

lwp542 · 发表于 2013-4-26 11:08

这个是不是有点问题？ oracle 11g 中没有alter system checkpoint 打印的不是你这个建表语句的信息。

guoyJoe · 发表于 2013-4-26 12:01

lwp542 发表于 2013-4-26 11:08
这个是不是有点问题？ oracle 11g 中没有alter system checkpoint 打印的不是你这个建表语句的信息。

说清楚点，没听懂你在说什么?

lwp542 · 发表于 2013-4-26 13:21

本帖最后由 lwp542 于 2013-4-26 13:22 编辑

guoyJoe 发表于 2013-4-26 12:01
说清楚点，没听懂你在说什么?

我跟着楼主做的实验，但是没有看到flag=5的情况，另外楼主的alter sytem dump datafile block 时没有运行alter system checkpoint ，这样不会打印真实数据块上的数据；

SQL> create table t20(id number,name varchar2(20)) segment creation immediate;

Table created.

SQL> select extent_id,file_id,block_id from dba_extents where segment_name='T20';

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
      0       4       304

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 304;

System altered.

Start dump data blocks tsn: 4 file#:4 minblk 304 maxblk 304
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777520
BH (0x433e34c0) file#: 4 rdba: 0x01000130 (4/304) class: 8 ba: 0x43034000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 21,28
  dbwrid: 0 obj: 76911 objn: 76911 tsn: 4 afn: 4 hint: f
  hash: [0x51bcc980,0x51bcc980] lru: [0x433e38bc,0x433e3640]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x01000130 (4/304)
scn: 0x0000.00578905 seq: 0x02 flg: 0x04 tail: 0x89052002
frmt: 0x02 chkval: 0xf5b3 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00C0B200 to 0x00C0D200
C0B200 0000A220 01000130 00578905 04020000  [ ...0.....W.....]
C0B210 0000F5B3 00000000 00000000 00000000  [................]
C0B220 00000000 00000000 00000000 00000000  [................]
      Repeat 1 times
C0B240 00000000 00000000 00000000 00000004  [................]
C0B250 FFFFFFFF 00000005 00000003 00000008  [................]
C0B260 00010001 00000000 00000000 00000000  [................]
C0B270 00000000 00000003 00000000 00000000  [................]
C0B280 00000000 00000000 00000000 00000000  [................]
C0B290 01000131 00000000 00000000 00000003  [1...............]
C0B2A0 00000008 01000133 00000000 00000000  [....3...........]
C0B2B0 00000000 00000000 00000000 00000001  [................]
C0B2C0 00012C6F 00524FE0 00000000 01000130  [o,...OR.....0...]
C0B2D0 00000008 00000000 00000000 00000000  [................]
C0B2E0 00000000 00000000 00000000 00000000  [................]
      Repeat 9 times
C0B380 00000000 00000000 00000000 00001011  [................]
C0B390 00000000 00000000 00000000 00000000  [................]
      Repeat 485 times
C0D1F0 00000000 00000000 00000000 89052002  [............. ..]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1       parent dba:  0x01000131 poffset: 0
unformatted: 5    total: 8       first useful block: 3
owning instance : 1
instance ownership changed at
Last successful Search
Freeness Status:  nf1 0    nf2 0    nf3 0    nf4 0

Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid:    :  0x0000.000.00000000
Inc #: 0 Objd: 76911
  HWM Flag: HWM Set
   Highwater::  0x01000133  ext#: 0    blk#: 3    ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
0x01000130  Length: 8    Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
  --------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 304 maxblk 304

lwp542 · 发表于 2013-4-26 13:27

楼主：我跟这你做的实验，怎么情况不一样的呢？？？

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter deferr

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation          boolean    TRUE
SQL> create table t10(id int,name varchar2(20)) segment creation immediate;

Table created.

SQL> select extent_id,file_id,block_id from dba_extents where segment_name='T10';

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
      0       4       312

SQL> alter system dump datafile 4 block 312;

System altered.

Trace file /home/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11113.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /home/opt/oracle/product/OraHome
System name: Linux
Node name:    localhost.localdomain
Release:       2.6.18-164.el5
Version:       #1 SMP Thu Sep 3 03:33:56 EDT 2009
Machine:       i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 11113, image: oracle@localhost.localdomain (TNS V1-V3)

*** 2013-04-25 22:25:41.201
*** SESSION ID

1.14) 2013-04-25 22:25:41.201
*** CLIENT ID

) 2013-04-25 22:25:41.201
*** SERVICE NAME

SYS$USERS) 2013-04-25 22:25:41.201
*** MODULE NAME

SQL*Plus) 2013-04-25 22:25:41.201
*** ACTION NAME

) 2013-04-25 22:25:41.201

Start dump data blocks tsn: 4 file#:4 minblk 312 maxblk 312
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777528
BH (0x2dfef9d8) file#: 4 rdba: 0x01000138 (4/312) class: 8 ba: 0x2de10000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 867,28
  dbwrid: 0 obj: 76996 objn: 76996 tsn: 4 afn: 4 hint: f
  hash: [0x51bb7880,0x51bb7880] lru: [0x2dfefb58,0x2dfef9b0]
  obj-flags: object_ckpt_list
  ckptq: [0x3cbe2780,0x2dfefb80] fileq: [0x51460b98,0x2dfefb88] objq: [0x2dfefb70,0x4ee47d04]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xef.22c2.0] LSCN: [0x0.57d9c4] HSCN: [0x0.57d9c4] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x01000138 (4/312)
scn: 0x0000.004e8109 seq: 0x02 flg: 0x04 tail: 0x81092002
frmt: 0x02 chkval: 0x2eba type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00445200 to 0x00447200
445200 0000A220 01000138 004E8109 04020000  [ ...8.....N.....]
445210 00002EBA 00000000 00000000 00000000  [................]
445220 00000000 00000000 00000000 00000000  [................]
      Repeat 1 times
445240 00000000 00000000 00000000 00000004  [................]
445250 FFFFFFFF 00000000 00000001 00000010  [................]
445260 00010002 00000000 00000000 00000000  [................]
445270 00000000 00000010 512EBD7E 512EBD7E  [........~..Q~..Q]
445280 00000000 00000000 00000000 00000000  [................]
445290 010000C9 00000005 00000000 00000000  [................]
4452A0 00000000 00000000 00000000 00000000  [................]
      Repeat 1 times
4452C0 00012980 004E8099 00000000 01000138  [.)....N.....8...]
4452D0 00000008 00000000 01000140 00000008  [........@.......]
4452E0 00000008 00000000 00000000 00000000  [................]
4452F0 00000000 00000000 00000000 00000000  [................]
      Repeat 8 times
445380 00000000 00000000 00000000 11111111  [................]
445390 11111111 00000000 00000000 00000000  [................]
4453A0 00000000 00000000 00000000 00000000  [................]
      Repeat 484 times
4471F0 00000000 00000000 00000000 81092002  [............. ..]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2       parent dba:  0x010000c9 poffset: 5
unformatted: 0    total: 16       first useful block: 1
owning instance : 1
instance ownership changed at 02/27/2013 18:14:22
Last successful Search 02/27/2013 18:14:22
Freeness Status:  nf1 0    nf2 0    nf3 0    nf4 0

Extent Map Block Offset: 4294967295
First free datablock : 16
Bitmap block lock opcode 0
Locker xid:    :  0x0000.000.00000000
Inc #: 0 Objd: 76160
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
0x01000138  Length: 8    Offset: 0
0x01000140  Length: 8    Offset: 8

0:Metadata 1:FULL 2:FULL 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
  --------------------------------------------------------
End dump data blocks tsn: 4 file#: 4 minblk 312 maxblk 312

lwp542 · 发表于 2013-4-26 13:28

我实验的结果是flag=4的情况，跟你的实验flag=5的情况不一样？？

guoyJoe · 发表于 2013-4-26 13:48

lwp542 发表于 2013-4-26 13:28
我实验的结果是flag=4的情况，跟你的实验flag=5的情况不一样？？

flg：0x01 (新建块)0x2(数据块延迟清洗推进scn和seq) 0X04(设置校验和) 0x08(临时块)

如果是 flag=5,那是0x01+0x04的组合，没有单独的0x05

lwp542 · 发表于 2013-4-26 13:56

guoyJoe 发表于 2013-4-26 13:48
flg：0x01 (新建块)0x2(数据块延迟清洗推进scn和seq) 0X04(设置校验和) 0x08(临时块)

如果是 flag=5,那 ...

[oracle@ocm trace]$ cd /u01/app/oracle/diag/rdbms/ocm/ocm/trace
[oracle@ocm trace]$ ls -lFtr
[oracle@ocm trace]$ more ocm_ora_12910.trc
贴出数据块头的信息：
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xa798 type: 0x00=unknown

从上面看出flg是flg: 0x05，就是0x01 +0x04的组合；

你实验出的结果是flg: 0x05 为什么我实验的是flg: 0x04呢？请指点一下！

guoyJoe · 发表于 2013-4-26 18:35

本帖最后由 guoyJoe 于 2013-4-26 18:35 编辑

lwp542 发表于 2013-4-26 13:56
[oracle@ocm trace]$ cd /u01/app/oracle/diag/rdbms/ocm/ocm/trace
[oracle@ocm trace]$ ls -lFtr
[or ...

好我再来测一把：
sys@OCM> create tablespace tp2 datafile '/u01/app/oracle/oradata/ocm/tp2.dbf' size 10M;
Tablespace created.
sys@OCM> conn gyj/gyj
Connected.
gyj@OCM> create table t10(id int,name varchar2(20)) segment creation immediate tablespace tp2;

Table created.
gyj@OCM> gyj@OCM> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='T10';

EXTENT_ID FILE_ID BLOCK_ID    BLOCKS
---------- ---------- ---------- ----------
      0       4       128       8

sys@OCM> alter system dump datafile 4 block 128;
System altered.

[oracle@ocm trace]$ more ocm_ora_4869.trc
Trace file /u01/app/oracle/diag/rdbms/ocm/ocm/trace/ocm_ora_4869.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0
System name: Linux
Node name:    ocm
Release:       2.6.18-164.el5
Version:       #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:       i686
Instance name: ocm
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 4869, image: oracle@ocm (TNS V1-V3)

*** 2013-04-26 18:32:40.125
*** SESSION ID:(133.44) 2013-04-26 18:32:40.125
*** CLIENT ID:() 2013-04-26 18:32:40.125
*** SERVICE NAME:(SYS$USERS) 2013-04-26 18:32:40.125
*** MODULE NAME:(sqlplus@ocm (TNS V1-V3)) 2013-04-26 18:32:40.125
*** ACTION NAME:() 2013-04-26 18:32:40.125

Start dump data blocks tsn: 28 file#:4 minblk 128 maxblk 128
Block dump from cache:
Dump of buffer cache at level 4 for tsn=28, rdba=16777344
BH (0x383f891c) file#: 4 rdba: 0x01000080 (4/128) class: 8 ba: 0x3836a000
  set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
  dbwrid: 0 obj: 78444 objn: 78444 tsn: 28 afn: 4 hint: f
  hash: [0x4af261b0,0x4af261b0] lru: [0x383f8820,0x383f8c44]
  obj-flags: object_ckpt_list
  ckptq: [0x3c3f69a4,0x383f8774] fileq: [0x4a8e5d4c,0x383f877c] objq: [0x383f8838,0x458343ac]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0x8.356.0] LSCN: [0x0.1a0d285] HSCN: [0x0.1a0d285] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 28 rdba: 0x00000080 (0/128)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xa780 type: 0x00=unknown
Hex dump of corrupt header 4 = CORRUPT
Dump of memory from 0x007D2600 to 0x007D2614
7D2600 0000A200 00000080 00000000 05010000  [................]
7D2610 0000A780                            [....]
Hex dump of block: st=4, typ_found=0
Dump of memory from 0x007D2600 to 0x007D4600
7D2600 0000A200 00000080 00000000 05010000  [................]
7D2610 0000A780 00000000 00000000 00000000  [................]
7D2620 00000000 00000000 00000000 00000000  [................]
      Repeat 508 times
7D45F0 00000000 00000000 00000000 00000001  [................]
End dump data blocks tsn: 28 file#: 4 minblk 128 maxblk 128

你新建一个干净的表空间，再建表，再试试吧。

guoyJoe · 发表于 2013-4-26 18:36

lwp542 发表于 2013-4-26 11:08
这个是不是有点问题？ oracle 11g 中没有alter system checkpoint 打印的不是你这个建表语句的信息。

11g块中可以dump buffer cache了，如：
Dump of buffer cache at level 4 for tsn=28, rdba=16777344
BH (0x383f891c) file#: 4 rdba: 0x01000080 (4/128) class: 8 ba: 0x3836a000
  set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
  dbwrid: 0 obj: 78444 objn: 78444 tsn: 28 afn: 4 hint: f
  hash: [0x4af261b0,0x4af261b0] lru: [0x383f8820,0x383f8c44]
  obj-flags: object_ckpt_list
  ckptq: [0x3c3f69a4,0x383f8774] fileq: [0x4a8e5d4c,0x383f877c] objq: [0x383f8838,0x458343ac]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0x8.356.0] LSCN: [0x0.1a0d285] HSCN: [0x0.1a0d285] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

lwp542 · 发表于 2013-4-27 09:19

to guoyJoe:

tks, 按你说的新建立表空间后实验得出了flag=5的情况。

[精华] 明明白白使用数据块 ----数据块格式深入解析

浏览过的版块