请问怎么才能dump出一条记录

赵宇 · 发表于 2010-5-21 16:35

SQL>  create table test (I number) initrans 10;

Table created.

SQL> insert into test values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                 4                               174
[oracle@omr trace]$ pwd
/u01/app/oracle/diag/rdbms/emrep/emrep/trace

Start dump data blocks tsn: 4 file#:4 minblk 174 maxblk 174
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777390
BH (0x267e200c) file#: 4 rdba: 0x010000ae (4/174) class: 1 ba: 0x26402000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  dbwrid: 0 obj: 81867 objn: 81867 tsn: 4 afn: 4 hint: f
  hash: [0x3eee32b0,0x3eee32b0] lru: [0x263f9068,0x263e62f8]
  obj-flags: object_ckpt_list
  ckptq: [0x29bf6cf4,0x267e33ec] fileq: [0x3ef7a60c,0x267e33f4] objq: [0x263f9080,0x263e6310]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x62.10dea.0] LSCN: [0x0.20f6b1] HSCN: [0x0.20f6b3] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x010000ae (4/174)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xa6ae type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0xF6FBC600 to 0xF6FBE600
F6FBC600 0000A200 010000AE 00000000 05010000  [................]
F6FBC610 0000A6AE 00000000 00000000 00000000  [................]
F6FBC620 00000000 00000000 00000000 00000000  [................]
      Repeat 508 times
F6FBE5F0 00000000 00000000 00000000 00000001  [................]
Dump of memory from 0xF6FBC614 to 0xF6FBE5FC
F6FBC610       00000000 00000000 00000000    [............]
F6FBC620 00000000 00000000 00000000 00000000  [................]
      Repeat 508 times
F6FBE5F0 00000000 00000000 00000000          [............]
End dump data blocks tsn: 4 file#: 4 minblk 174 maxblk 174

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 174;

System altered.

Start dump data blocks tsn: 4 file#:4 minblk 174 maxblk 174
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777390
BH (0x267e200c) file#: 4 rdba: 0x010000ae (4/174) class: 1 ba: 0x26402000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  dbwrid: 0 obj: 81867 objn: 81867 tsn: 4 afn: 4 hint: f
  hash: [0x3eee32b0,0x3eee32b0] lru: [0x263f9068,0x263e62f8]
  ckptq: [NULL] fileq: [NULL] objq: [0x263f9080,0x263e6310]
  st: XCURRENT md: NULL tch: 2
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x010000ae (4/174)
scn: 0x0000.0020f6b3 seq: 0x01 flg: 0x06 tail: 0xf6b30601
frmt: 0x02 chkval: 0x81c3 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xF6FBC600 to 0xF6FBE600
F6FBC600 0000A206 010000AE 0020F6B3 06010000  [.......... .....]
F6FBC610 000081C3 00000001 00013FCB 0020F6B1  [.........?.... .]
F6FBC620 00000000 0032000A 010000A8 0015000A  [......2.........]
F6FBC630 00000748 00C00114 002601CD 00002001  [H.........&.. ..]
F6FBC640 0020F6B3 00000000 00000000 00000000  [.. .............]
F6FBC650 00000000 00000000 00000000 00000000  [................]
      Repeat 12 times
F6FBC720 00000000 00010100 0014FFFF 1EBB1ED2  [................]
F6FBC730 00001EBB 1ED20001 07CA0799 082C07FB  [..............,.]
F6FBC740 08930862 08FF08C9 09660935 09D2099C  [b.......5.f.....]
F6FBC750 0A360A08 0AA20A6C 0B0E0AD8 0B7A0B44  [..6.l.......D.z.]
F6FBC760 0BE60BB0 0C4A0C1C 0CB60C80 0D220CEC  [......J.......".]

...........

Block header dump:  0x010000ae
Object id on Block? Y
seg/obj: 0x13fcb  csc: 0x00.20f6b1  itc: 10  flg: E  typ: 1 - DATA
   brn: 0  bdba: 0x10000a8 ver: 0x01 opc: 0
   inc: 0  exflg: 0

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0x000a.015.00000748  0x00c00114.01cd.26  --U- 1  fsc 0x0000.0020f6b3
0x02 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x03 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x04 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x05 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x06 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x07 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x08 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x09 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x0a 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
bdba: 0x010000ae
data_block_dump,data header at 0xf6fbc724
===============
tsiz: 0x1ed8
hsiz: 0x14
pbl: 0xf6fbc724
   76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1ed2
avsp=0x1ebb
tosp=0x1ebb
0xe

ti[0] nrow=1 offs=0
0x12

ri[0]    offs=0x1ed2
block_row_dump:
tab 0, row 0, @0x1ed2
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 0b
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 174 maxblk 174

开始找不到数据，就是数据文件还没有同步

zergduan · 发表于 2010-5-21 16:50

赵老板如何dump的？我和lz的dump文件中都没有Block dump from cache:这一段~

ccsnmoracle · 发表于 2010-5-21 20:46

zergduan sir

我补充一个问题。。。

当执行了commit操作后，如果立即dump数据块的话，由于DBWn还没有发生，dump出的内容能够及时反映出变化吗？？
对于buffer cache的flush操作，应该会触发check point把！！？？？

作Delayded Cleanout实验的时候，好像每次dump出的内容都是commit以后的内容！！
所以DBWn一定是发生了。。。

Yong Huang · 发表于 2010-5-22 00:59

> 我和lz的dump文件中都没有Block dump from cache:这一段~

It's new in 11g, including 11gR1.

> 当执行了commit操作后，如果立即dump数据块的话，由于DBWn还没有发生，dump出的内容能够及时反映出变化吗？？

If the changed data only exists in buffer cache, obviously a regular dump won't show the new data. But I remember there's a difference between using file name and file number after "alter system dump datafile" and one of them is said to dump from the buffer cache, the other from the file. I didn't verify.

> 对于buffer cache的flush操作，应该会触发check point把！！？？？

Not all DBWn writes are checkpoint. "alter system flush buffer_cache" does not checkpoint. Run these commands:

select * from v$sysstat where name like '%checkpoint%';
alter system flush buffer_cache;
select * from v$sysstat where name like '%checkpoint%';

and compare the statistic values.

Yong Huang

zergduan · 发表于 2010-5-24 14:27

原帖由 Yong Huang 于 2010-5-22 00:59 发表
> 我和lz的dump文件中都没有Block dump from cache:这一段~

It's new in 11g, including 11gR1.

> 当执行了commit操作后，如果立即dump数据块的话，由于DBWn还没有发生，dump出的内容能够及时反映出变化吗？？

If the changed data only exists in buffer cache, obviously a regular dump won't show the new data. But I remember there's a difference between using file name and file number after "alter system dump datafile" and one of them is said to dump from the buffer cache, the other from the file. I didn't verify.

> 对于buffer cache的flush操作，应该会触发check point把！！？？？

Not all DBWn writes are checkpoint. "alter system flush buffer_cache" does not checkpoint. Run these commands:

select * from v$sysstat where name like '%checkpoint%';
alter system flush buffer_cache;
select * from v$sysstat where name like '%checkpoint%';

and compare the statistic values.

Yong Huang

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@MYDB10G AS SYSDBA>col name for a60
SYS@MYDB10G AS SYSDBA>select * from mydb.b;

A B C D
- - - -
a    b

SYS@MYDB10G AS SYSDBA>with t as
  2    (select rowid rid from mydb.b)
  3    select dbms_rowid.rowid_object(rid) data_object_id#,
  4    dbms_rowid.rowid_relative_fno(rid) rfile#,
  5    dbms_rowid.rowid_block_number(rid) block#,
  6    dbms_rowid.rowid_row_number(rid) row# from t;

DATA_OBJECT_ID#    RFILE#    BLOCK#    ROW#
--------------- ---------- ---------- ----------
      19529       4    14056       0

SYS@MYDB10G AS SYSDBA>select rfile#,name from v$datafile;

RFILE# NAME
---------- ------------------------------------------------------------
      1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\SYSTEM01.DBF
      2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\UNDOTBS01.DBF
      3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\SYSAUX01.DBF
      4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\TEST01.DBF
      5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\USERTABLE01.DBF
      6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\USERINDEX01.DBF

已选择6行。

SYS@MYDB10G AS SYSDBA>select tch
  2       from x$bh
  3    where dbarfil = 4
  4       and dbablk = 14056;

   TCH
----------
      4

SYS@MYDB10G AS SYSDBA>alter system dump datafile 4 block 14056;

系统已更改。

已用时间:  00: 00: 02.95
SYS@MYDB10G AS SYSDBA>select tch
  2       from x$bh
  3    where dbarfil = 4
  4       and dbablk = 14056;

   TCH
----------
      4

SYS@MYDB10G AS SYSDBA>alter system dump datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\TEST01.DBF' block 14056;

系统已更改。

已用时间:  00: 00: 00.07
SYS@MYDB10G AS SYSDBA>select tch
  2       from x$bh
  3    where dbarfil = 4
  4       and dbablk = 14056;

   TCH
----------
      4

SYS@MYDB10G AS SYSDBA>select * from mydb.b;

A B C D
- - - -
a    b

SYS@MYDB10G AS SYSDBA>select tch
  2       from x$bh
  3    where dbarfil = 4
  4       and dbablk = 14056;

   TCH
----------
      5

似乎无论是用rfile#还是name,alter system dump datafile block都不会touch,db_cache中的block

zergduan · 发表于 2010-5-24 15:01

根据
http://dbsnake.com/2010/02/dump-block-in-bc.html
中的第一段话:
"在oracle里，假如我们去dump一个快，我们通常都会用到alter system dump datafile XXX block XXX，当你执行上述命令的时候，oracle会这么做：
首先判断一下你要dump的这个block是否在buffer cache中，如果在，则buffer cache中这个block的内容就会是你的dump文件中的内容，这时候oracle不会产生实际的物理I/O去相应的datafile中读这个block（所以这里有可能会存在不一致的情况，这种情况用BBED非常容易模拟）；如果不在，oracle会产生实际的物理I/O去相应的datafile中读这个block，读到的内容就是你的dump文件中的内容，注意此时oracle不会把读到的那个block缓存在buffer cache中。"

似乎和我上面的测试相反~ 按照我的测试就算块在buffer_cache中，dump也不会去读取buffer_cache中的内容~
如果不在buffer_cahce中，dump 块后，这个块也不会被读入buffer_cahce中
SYS@MYDB10G AS SYSDBA>startup force
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                1295896 bytes
Variable Size          163580392 bytes
Database Buffers          37748736 bytes
Redo Buffers             7090176 bytes
数据库装载完毕。
数据库已经打开。
SYS@MYDB10G AS SYSDBA>select tch
  2       from x$bh
  3    where dbarfil = 4
  4       and dbablk = 14056;

未选定行

SYS@MYDB10G AS SYSDBA>alter system dump datafile 4 block 14056;

系统已更改。

SYS@MYDB10G AS SYSDBA>select tch
  2       from x$bh
  3    where dbarfil = 4
  4       and dbablk = 14056;

未选定行

SYS@MYDB10G AS SYSDBA>alter system dump datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB10G\TEST01.DBF' block 14056;

系统已更改。

SYS@MYDB10G AS SYSDBA>select tch
  2       from x$bh
  3    where dbarfil = 4
  4       and dbablk = 14056;

未选定行

〇〇 · 发表于 2010-5-24 19:49

谢谢大家的解答，alter system checkpoint;
应该保证dump提交后的内容吧？

zss0109211 · 发表于 2010-6-1 22:45

按照楼主的步骤做的dump，发现更奇怪的问题,两条SQL查询的FILE_ID,BLOCK_ID居然不一样
select file_id,block_id from dba_extents where segment_name='B'
union all
select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from PAR_TEST.b
————————————————
FILE_ID BLOCK_ID
6 41
6 42

Yong Huang · 发表于 2010-6-2 06:32

原帖由 zss0109211 于 2010-6-1 08:45 发表
按照楼主的步骤做的dump，发现更奇怪的问题,两条SQL查询的FILE_ID,BLOCK_ID居然不一样
select file_id,block_id from dba_extents where segment_name='B'
union all
select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from PAR_TEST.b
————————————————
FILE_ID BLOCK_ID
6 41
6 42

Block_id of dba_extents is the *starting* block of the extent, not necessarily the block that contains the data.

Yong Huang

zss0109211 · 发表于 2010-6-2 16:33

但我表里只有一条记录，怎么会产生偏差呀的。