dump ORACLE数据库内部结构-block

网友投稿 288 2022-11-05


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.trcSQL> select header_file, header_block from dba_segments where upper(segment_name) = upper(’dumptable_test’);HEADER_FILE HEADER_BLOCK———– ————5 899SQL> alter system dump datafile 5 block 899;$vi /opt/oracle/admin/ORCL203/udump/orcl203_ora_4408.trcRepeat 151 timesC7BC040 00000000 00000000 01400382 00000000 [..........@.....]C7BC050 00000000 00000000 00000000 00000000 [................]Repeat 185 timesC7BCBF0 00000000 00000000 00000000 321C2302 [.............#.2]Extent Control Header—————————————————————–Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8last map 0×00000000 #maps: 0 offset: 2716Highwater:: 0×01400389 ext#: 0 blk#: 8 ext size: 8#blocks in seg. hdr’s freelists: 0#blocks below: 5mapblk 0×00000000 offset: 0Unlocked——————————————————–Low HighWater Mark :Highwater:: 0×01400389 ext#: 0 blk#: 8 ext size: 8#blocks in seg. hdr’s freelists: 0#blocks below: 5mapblk 0×00000000 offset: 0Level 1 BMB for High HWM block: 0×01400381Level 1 BMB for Low HWM block: 0×01400381——————————————————–Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0L2 Array start offset: 0×00001434First Level 3 BMB: 0×00000000L2 Hint for inserts: 0×01400382Last Level 1 BMB: 0×01400381Last Level II BMB: 0×01400382Last Level III BMB: 0×00000000Map Header:: next 0×00000000 #extents: 1 obj#: 51885 flag: 0×10000000Inc # 0Extent Map—————————————————————–SQL>select name from obj$ where obj# = 5927;NAME——————————DUMPTABLE_TESTSQL> select ora_rowscn, rowid from test2.dumptable_test;ORA_ROWSCN ROWID———- ——————738392 AAAMqxAAFAAAAeMAAA738392 AAAMqxAAFAAAAeMAAB

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

里面重要信息

Start dump data blocks tsn: 7 file#: 6 minblk 155 maxblk 155buffer tsn: 7 rdba: 0×0180009b (6/155)scn: 0×0000.000b786a seq: 0×02 flg: 0×04 tail: 0×786a2302frmt: 0×02 chkval: 0×6081 type: 0×23=PAGETABLE SEGMENT HEADERHex dump of block: st=0, typ_found=1Dump of memory from 0xB802A200 to 0xB802C200B802A200 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: 8last map 0×00000000 #maps: 0 offset: 2716Highwater:: 0×018000a1 ext#: 0 blk#: 8 ext size: 8#blocks in seg. hdr’s freelists: 0#blocks below: 5mapblk 0×00000000 offset: 0Unlocked——————————————————–Unlocked——————————————————–in seg. hdr’s freelists: 0#block below: 5mapblk 0×00000000 offset: 0Level 1 BMB for High HWM block: 0×01800099Level 1 BMB for Low HWM block: 0×01800099——————————————————–Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0L2 Array start offset: 0×00001434First Level 3 BMB: 0×00000000L2 Hint for inserts: 0×0180009aLast Level 1 BMB: 0×01800099Last Level II BMB: 0×0180009aLast Level III BMB: 0×00000000Map Header:: next 0×00000000 #extents: 1 obj#: 51973flag: 0×10000000Inc # 0Extent MapSQL> 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×93500602frmt: 0×02 chkval: 0×12c5 type: 0×06=trans dataHex 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 AAAMsGAAGAAAACkAAAtype和header也不一样 trans data

看看数据的信息

Block header dump: 0×018000a4Object id on Block? Yseg/obj: 0xcb06 csc: 0×00.b934f itc: 3 flg: E typ: 1 - DATAbrn: 0 bdba: 0×18000a1 ver: 0×01 opc: 0inc: 0 exflg: 0obj的信息放置的地方也不一样 这里是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×1f64tl: 28 fb: –H-FL– lb: 0×0 cc: 1dscn 0×0000.000b934fcol 0: [18] 31 31 32 32 33 33 34 34 35 35 36 36 37 37 38 38 39 39tab 0, row 1, @0×1f47tl: 29 fb: –H-FL– lb: 0×0 cc: 1dscn 0×0000.000b934fcol 0: [19] 32 32 31 31 33 33 34 34 20 35 35 36 36 37 37 38 38 39 39tab 0, row 20, @0×1d7ctl: 19 fb: –H-FL– lb: 0×0 cc: 1dscn 0×0000.000b934fcol 0: [ 9] 31 32 33 31 32 33 31 32 33end_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 ROWID759296 AAAMsGAAGAAAAClAAASQL> 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​​


版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:Java使用fill()数组填充的实现
下一篇:data block的信息
相关文章

 发表评论

暂时没有评论,来抢沙发吧~