dump ORACLE数据库内部结构-block

我们有时分析数据，不得不进行block的分析，Oracle里多是用block作为数据单元的，

我们这里首先一起看看data block的分析

分析数据文件块，转储数据文件n的块m (dump a block to trc)
alter system dump datafile n block m
或者
alter system dump dafile n block min m1 block max m2;

例子

SQL>create table test2.dumptable_test (
name varchar2(4000),
txt1 varchar2(4000),
txt2 varchar2(4000),
txt3 varchar2(4000));

SQL>insert into test2.dumptable_test values(
‘aaaaaaaaaa’,'bbbbbbbbbb’,'cccccccccc’,'dddddddddd’);

SQL>insert into test2.dumptable_test values(
‘AAAAAAAAAA’,'BBBBBBBBBB’,'CCCCCCCCCC’,'DDDDDDDDDD’);

SQL>commit;

SQL> select gettracename() from dual;

GETTRACENAME()
——————————————————————————–
/opt/oracle/admin/ORCL203/udump/orcl203_ora_4408.trc

SQL> select header_file, header_block from dba_segments where upper(segment_name) = upper(’dumptable_test’);

HEADER_FILE HEADER_BLOCK
———– ————
5 899

SQL> alter system dump datafile 5 block 899;

$vi /opt/oracle/admin/ORCL203/udump/orcl203_ora_4408.trc

Repeat 151 times
C7BC040 00000000 00000000 01400382 00000000 [..........@.....]
C7BC050 00000000 00000000 00000000 00000000 [................]
Repeat 185 times
C7BCBF0 00000000 00000000 00000000 321C2302 [.............#.2]
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0×00000000 #maps: 0 offset: 2716
Highwater:: 0×01400389 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr’s freelists: 0
#blocks below: 5
mapblk 0×00000000 offset: 0
Unlocked
——————————————————–
Low HighWater Mark :
Highwater:: 0×01400389 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr’s freelists: 0
#blocks below: 5
mapblk 0×00000000 offset: 0
Level 1 BMB for High HWM block: 0×01400381
Level 1 BMB for Low HWM block: 0×01400381
——————————————————–
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0×00001434
First Level 3 BMB: 0×00000000
L2 Hint for inserts: 0×01400382
Last Level 1 BMB: 0×01400381
Last Level II BMB: 0×01400382
Last Level III BMB: 0×00000000
Map Header:: next 0×00000000 #extents: 1 obj#: 51885 flag: 0×10000000
Inc # 0
Extent Map
—————————————————————–

SQL>select name from obj$ where obj# = 5927;

NAME
——————————
DUMPTABLE_TEST

SQL> select ora_rowscn, rowid from test2.dumptable_test;

ORA_ROWSCN ROWID
———- ——————
738392 AAAMqxAAFAAAAeMAAA
738392 AAAMqxAAFAAAAeMAAB

这个block是header的block，最重要的信息在Extent Control Header里描述出来

里面重要信息

Start dump data blocks tsn: 7 file#: 6 minblk 155 maxblk 155
buffer tsn: 7 rdba: 0×0180009b (6/155)
scn: 0×0000.000b786a seq: 0×02 flg: 0×04 tail: 0×786a2302
frmt: 0×02 chkval: 0×6081 type: 0×23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB802A200 to 0xB802C200
B802A200 0000A223 0180009B 000B786A 04020000 [#.......jx......]
B802A210 00006081 00000000 00000000 00000000 [.`..............]
B802A220 00000000 00000001 00000008 00000A9C [................]
B802A230 00000000 00000008 00000008 018000A1 [................]

关于scn，还没有研究出来，这里的scn和我数据库里查到的不一致哟。 以后需要关注这个问题。

The third line describes the SCN of the data block. In our case, the SCN is 0×0000.00046911. The tail of the data block is composed of the last two bytes of the SCN (6911) appended with the type (06) and the sequence (02). If the decomposition of the tail does not match these three values, then the system knows that the block is inconsistent and needs to be recovered. While this tail value shows up at the beginning of the block dump, it is physically stored at the end of the data block.

—————————————————————–
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0×00000000 #maps: 0 offset: 2716
Highwater:: 0×018000a1 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr’s freelists: 0
#blocks below: 5
mapblk 0×00000000 offset: 0
Unlocked
——————————————————–

Unlocked
——————————————————–
in seg. hdr’s freelists: 0
#block below: 5
mapblk 0×00000000 offset: 0
Level 1 BMB for High HWM block: 0×01800099
Level 1 BMB for Low HWM block: 0×01800099
——————————————————–
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0×00001434
First Level 3 BMB: 0×00000000
L2 Hint for inserts: 0×0180009a
Last Level 1 BMB: 0×01800099
Last Level II BMB: 0×0180009a
Last Level III BMB: 0×00000000
Map Header:: next 0×00000000 #extents: 1 obj#: 51973
flag: 0×10000000
Inc # 0
Extent Map

SQL> select * from all_objects t where t.object_id = 51973;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
—————————— —————————— —————————— ———- ————– ——————- ———– ————- ——————- ——- ——— ——— ———
TESTUSER

上面我们做例子的是个segment header block.

下面我们看看data的dump

buffer tsn: 7 rdba: 0×018000a4 (6/164)
scn: 0×0000.000b9350 seq: 0×02 flg: 0×04 tail: 0×93500602
frmt: 0×02 chkval: 0×12c5 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1

这里可以看到data的scn和header不一致，这里的倒是和ora_rowscn一致。
SQL> select ora_rowscn, rowid from testuser.testrowscn;

ORA_ROWSCN ROWID
———- ——————
758607 AAAMsGAAGAAAACkAAA

type和header也不一样 trans data

看看数据的信息

Block header dump: 0×018000a4
Object id on Block? Y
seg/obj: 0xcb06 csc: 0×00.b934f itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0×18000a1 ver: 0×01 opc: 0
inc: 0 exflg: 0

obj的信息放置的地方也不一样 这里是16进制的，header是10进制的

SQL> select * from all_objects t where t.object_id = 51974;

看看记录的scn，如果是rowdependencies建立的表，每个记录有自己的不同于block的scn。

block_row_dump:
tab 0, row 0, @0×1f64
tl: 28 fb: –H-FL– lb: 0×0 cc: 1
dscn 0×0000.000b934f
col 0: [18] 31 31 32 32 33 33 34 34 35 35 36 36 37 37 38 38 39 39
tab 0, row 1, @0×1f47
tl: 29 fb: –H-FL– lb: 0×0 cc: 1
dscn 0×0000.000b934f
col 0: [19] 32 32 31 31 33 33 34 34 20 35 35 36 36 37 37 38 38 39 39
tab 0, row 20, @0×1d7c
tl: 19 fb: –H-FL– lb: 0×0 cc: 1
dscn 0×0000.000b934f
col 0: [ 9] 31 32 33 31 32 33 31 32 33
end_of_block_dump

如果不是rowdependcies的就没有这个dscn了。和查出来的对照一下

select ora_rowscn, rowid from testuser.testrowscn;
———- ——————
758607 AAAMsGAAGAAAACkAAA

这里可以发现和block的scn不一样了，block的scn是758608，相差1.下面我们插入一条数据，再来看看。

SQL> insert into testuser.testrowscn values(’123123′);
SQL> commit;

ORA_ROWSCN ROWID
759296 AAAMsGAAGAAAAClAAA

SQL> select rowidinfo(’AAAMsGAAGAAAAClAAA’) from dual;
6==165==51974==TESTUSER==TESTROWSCN==0

可以发现已经扩展了新的block，那么block header的scn也将改变
dump出163到165 block
查看验证

可以发现164block没有改变，scn没有改变，block 163 header 头信息改名 scn改变为 b95ff(759295) , block 165新的block scn 值为b9600，新记录的dscn 0×0000.00000000，标示没有改变，和block一致，

update这个值试试

SQL> update testuser.testrowscn set mc=’12312312312312′ where rowid=’AAAMsGAAGAAAAClAAA’;
SQL> commit;

还是导出那3个block

根据scn号可以timestamp了
SQL> select scn_to_timestamp(’758607′) from dual;

segment block data structure reference：
http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c03block.htm