如何阅读oracle数据块的dump文件
答案:1 悬赏:70
解决时间 2021-01-13 10:10
- 提问者网友：未信
- 2021-01-13 03:34
如何阅读oracle数据块的dump文件
最佳答案
- 二级知识专家网友：像个废品
- 2021-01-13 04:02
这个没法细说 涉及到太多东西网上有这种详细的文章 下面大概说一下
SQL> conn clark/oracle
Connected.
SQL> select * from test;
ID NAME
---------- ----------
1 test
2 cs
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno,id,name from test;
FNOBNO ID NAME
---------- ---------- ---------- ----------
6 42431 test
6 42432 cs
SQL> alter system dump datafile 6 block 4243;
System altered.
SQL> select value from v$diag_info where name like '%Default Trace%';
VALUE
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_19139.trc
返回操作系统
more /opt/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_19139.trc
Trace file /opt/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_19139.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/11.2.0/db
System name:Linux
Node name:rhel3
Release:2.6.18-308.el5
Version:#1 SMP Fri Jan 27 17:17:51 EST 2012
Machine:x86_64
VM name:VMWare Version: 6
Instance name: testdb
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 19139, image: oracle@rhel3 (TNS V1-V3)
*** 2015-01-16 14:23:23.802
*** SESSION ID:(33.1271) 2015-01-16 14:23:23.802
*** CLIENT ID:() 2015-01-16 14:23:23.802
*** SERVICE NAME:(SYS$USERS) 2015-01-16 14:23:23.802
*** MODULE NAME:(SQL*Plus) 2015-01-16 14:23:23.802
*** ACTION NAME:() 2015-01-16 14:23:23.802
Start dump data blocks tsn: 7 file#:6 minblk 4243 maxblk 4243
下面这部分主要是 buffer cache 里面的buffer的一些状态 比如在什么链表上之类的信息
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25170067
BH (0x89f90a48) file#: 6 rdba: 0x01801093 (6/4243) class: 1 ba: 0x89496000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 248,28
dbwrid: 0 obj: 89512 objn: 89512 tsn: 7 afn: 6 hint: f
hash: [0x96fd5170,0xa834b7e0] lru: [0x89f850c0,0x89f84aa8]
ckptq: [NULL] fileq: [NULL] objq: [0x89f850e8,0x89f84ad0] objaq: [0x89f850f8,0x89f84ae0]
st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 3
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
下面是磁盘上块的数据头部也是属性 状态之类的中间是一大堆乱码 (类似杯子装水 先填底部,上面空的 有用的数据在底部)
Block dump from disk:
buffer tsn: 7 rdba: 0x01801093 (6/4243)
scn: 0x0000.ebfa652b seq: 0x01 flg: 0x06 tail: 0x652b0601
frmt: 0x02 chkval: 0xff2e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002AF9F9BB1A00 to 0x00002AF9F9BB3A00
2AF9F9BB1A00 0000A206 01801093 EBFA652B 06010000[........+e......]
2AF9F9BB1A10 0000FF2E 00160001 00015DA8 EBFA6528[.........]..(e..]
2AF9F9BB1A20 1FE80000 00321302 01801090 00170001[......2.........]
2AF9F9BB1A30 00000693 00C00751 001C034C 00008000[....Q...L.......]
2AF9F9BB1A40 EBFA635C 00010008 0000078A 00C00211[\c..............]
2AF9F9BB1A50 00040310 00002001 EBFA652B 00000000[..... ..+e......]
2AF9F9BB1A60 00000000 00020100 0016FFFF 1F6E1F7B[............{.n.]
2AF9F9BB1A70 00001F6E 1F7B0002 00811F86 00811ED3[n.....{.........]
2AF9F9BB1A80 00000006 00001F60 10010FD6 1057102C[....`.......,.W.]
2AF9F9BB1A90 10AD1082 10FE10D8 11541129 11A9117F[........).T.....]
2AF9F9BB1AA0 11FA11D4 124B1220 12A11276 12F212C7[.... .K.v.......]
2AF9F9BB1AB0 1348131D 139E1373 13EF13C4 1445141A[..H.s.........E.]
.....................
2AF9F9BB39A0 F21C8100 C5060C00 2C4E2F1B 02C2032A[........./N,*...]
2AF9F9BB39B0 02C1023A 7A6B310D 68793631 33393973[:....1kz16yhs993]
2AF9F9BB39C0 80013268 4E3229C5 C5061C0C 0F320527[h2...)2N....'.2.]
2AF9F9BB39D0 1BC50646 2A2C4E2F 022BC102 2C0602C1[F.../N,*..+....,]
2AF9F9BB39E0 C1020202 65740402 002C7473 03C10202[......test,.....]
2AF9F9BB39F0 2C736302 C1020200 6B630202 652B0601[.cs,......ck..+e]
dump出来的块头信息
Block header dump:0x01801093
Object id on Block? Y
seg/obj: 0x15da8csc: 0x00.ebfa6528itc: 2flg: Etyp: 1 - DATA
brn: 0bdba: 0x1801090 ver: 0x01 opc: 0
inc: 0exflg: 0
事务信息
Itl XidUba FlagLckScn/Fsc
0x01 0x0001.017.000006930x00c00751.034c.1cC---0scn 0x0000.ebfa635c
0x02 0x0008.001.0000078a0x00c00211.0310.04--U-1fsc 0x0000.ebfa652b
bdba: 0x01801093
*** 2015-01-16 14:23:23.903
data_block_dump,data header at 0x2af9f9bb1a64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2af9f9bb1a64
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f7b
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0]nrow=2offs=0
0x12:pri[0] offs=0x1f7b
0x14:pri[1] offs=0x1f86
block_row_dump:
块中数据行信息
第一行
tab 0, row 0, @0x1f7b
tl: 11 fb: --H-FL-- lb: 0x2cc: 2
col0: [ 2]c1 02
col1: [ 4]74 65 73 74
第二行
tab 0, row 1, @0x1f86
tl: 9 fb: --H-FL-- lb: 0x0cc: 2
col0: [ 2]c1 03
col1: [ 2]63 73
这些col后面的数据用函数转换一下就是表中的数据
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 4243 maxblk 4243
SQL> conn clark/oracle
Connected.
SQL> select * from test;
ID NAME
---------- ----------
1 test
2 cs
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno,id,name from test;
FNOBNO ID NAME
---------- ---------- ---------- ----------
6 42431 test
6 42432 cs
SQL> alter system dump datafile 6 block 4243;
System altered.
SQL> select value from v$diag_info where name like '%Default Trace%';
VALUE
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_19139.trc
返回操作系统
more /opt/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_19139.trc
Trace file /opt/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_19139.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/11.2.0/db
System name:Linux
Node name:rhel3
Release:2.6.18-308.el5
Version:#1 SMP Fri Jan 27 17:17:51 EST 2012
Machine:x86_64
VM name:VMWare Version: 6
Instance name: testdb
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 19139, image: oracle@rhel3 (TNS V1-V3)
*** 2015-01-16 14:23:23.802
*** SESSION ID:(33.1271) 2015-01-16 14:23:23.802
*** CLIENT ID:() 2015-01-16 14:23:23.802
*** SERVICE NAME:(SYS$USERS) 2015-01-16 14:23:23.802
*** MODULE NAME:(SQL*Plus) 2015-01-16 14:23:23.802
*** ACTION NAME:() 2015-01-16 14:23:23.802
Start dump data blocks tsn: 7 file#:6 minblk 4243 maxblk 4243
下面这部分主要是 buffer cache 里面的buffer的一些状态 比如在什么链表上之类的信息
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25170067
BH (0x89f90a48) file#: 6 rdba: 0x01801093 (6/4243) class: 1 ba: 0x89496000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 248,28
dbwrid: 0 obj: 89512 objn: 89512 tsn: 7 afn: 6 hint: f
hash: [0x96fd5170,0xa834b7e0] lru: [0x89f850c0,0x89f84aa8]
ckptq: [NULL] fileq: [NULL] objq: [0x89f850e8,0x89f84ad0] objaq: [0x89f850f8,0x89f84ae0]
st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 3
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
下面是磁盘上块的数据头部也是属性 状态之类的中间是一大堆乱码 (类似杯子装水 先填底部,上面空的 有用的数据在底部)
Block dump from disk:
buffer tsn: 7 rdba: 0x01801093 (6/4243)
scn: 0x0000.ebfa652b seq: 0x01 flg: 0x06 tail: 0x652b0601
frmt: 0x02 chkval: 0xff2e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002AF9F9BB1A00 to 0x00002AF9F9BB3A00
2AF9F9BB1A00 0000A206 01801093 EBFA652B 06010000[........+e......]
2AF9F9BB1A10 0000FF2E 00160001 00015DA8 EBFA6528[.........]..(e..]
2AF9F9BB1A20 1FE80000 00321302 01801090 00170001[......2.........]
2AF9F9BB1A30 00000693 00C00751 001C034C 00008000[....Q...L.......]
2AF9F9BB1A40 EBFA635C 00010008 0000078A 00C00211[\c..............]
2AF9F9BB1A50 00040310 00002001 EBFA652B 00000000[..... ..+e......]
2AF9F9BB1A60 00000000 00020100 0016FFFF 1F6E1F7B[............{.n.]
2AF9F9BB1A70 00001F6E 1F7B0002 00811F86 00811ED3[n.....{.........]
2AF9F9BB1A80 00000006 00001F60 10010FD6 1057102C[....`.......,.W.]
2AF9F9BB1A90 10AD1082 10FE10D8 11541129 11A9117F[........).T.....]
2AF9F9BB1AA0 11FA11D4 124B1220 12A11276 12F212C7[.... .K.v.......]
2AF9F9BB1AB0 1348131D 139E1373 13EF13C4 1445141A[..H.s.........E.]
.....................
2AF9F9BB39A0 F21C8100 C5060C00 2C4E2F1B 02C2032A[........./N,*...]
2AF9F9BB39B0 02C1023A 7A6B310D 68793631 33393973[:....1kz16yhs993]
2AF9F9BB39C0 80013268 4E3229C5 C5061C0C 0F320527[h2...)2N....'.2.]
2AF9F9BB39D0 1BC50646 2A2C4E2F 022BC102 2C0602C1[F.../N,*..+....,]
2AF9F9BB39E0 C1020202 65740402 002C7473 03C10202[......test,.....]
2AF9F9BB39F0 2C736302 C1020200 6B630202 652B0601[.cs,......ck..+e]
dump出来的块头信息
Block header dump:0x01801093
Object id on Block? Y
seg/obj: 0x15da8csc: 0x00.ebfa6528itc: 2flg: Etyp: 1 - DATA
brn: 0bdba: 0x1801090 ver: 0x01 opc: 0
inc: 0exflg: 0
事务信息
Itl XidUba FlagLckScn/Fsc
0x01 0x0001.017.000006930x00c00751.034c.1cC---0scn 0x0000.ebfa635c
0x02 0x0008.001.0000078a0x00c00211.0310.04--U-1fsc 0x0000.ebfa652b
bdba: 0x01801093
*** 2015-01-16 14:23:23.903
data_block_dump,data header at 0x2af9f9bb1a64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2af9f9bb1a64
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f7b
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0]nrow=2offs=0
0x12:pri[0] offs=0x1f7b
0x14:pri[1] offs=0x1f86
block_row_dump:
块中数据行信息
第一行
tab 0, row 0, @0x1f7b
tl: 11 fb: --H-FL-- lb: 0x2cc: 2
col0: [ 2]c1 02
col1: [ 4]74 65 73 74
第二行
tab 0, row 1, @0x1f86
tl: 9 fb: --H-FL-- lb: 0x0cc: 2
col0: [ 2]c1 03
col1: [ 2]63 73
这些col后面的数据用函数转换一下就是表中的数据
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 4243 maxblk 4243
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息！
大家都在看
推荐信息