<< 返回文章列表

如何转储数据文件和Buffer Cache中的数据块

2017年12月12日
盖国强
1872

试着回答一下以下几个问题,看看自己能否找到正确的答案:

  1. 当我们 insert 一条记录,不提交,这个数据在内存还是磁盘?

  2. 当我们 insert 一条记录,提交,这个数据在内存还是磁盘?

  3. 当我们 insert 一条记录,不提交,检查点,这个数据在内存还是磁盘?

  4. 当我们 insert 一条记录,提交,检查点,这个数据在内存还是磁盘?


先看看我的测试:

  1. 我们首先执行一个数据行插入,提交,执行检查点,这条数据被写入磁盘;

  2. 再插入一条数据,提交,此时(在检查点发生之前)这条数据不会写入磁盘;

  3. 当然 DUMP 之类的操作也不会引起这条数据写入磁盘;


那么如何验证:

  1. 通过DUMP BLOCK,从数据文件转储,只能看到一行记录;

  2. 通过DUMP Buffer Cache Block,能够看到两条记录。


这里的知识点是,如何DUMP Buffer Cache中的数据块,书中未提及这个知识点,但是隐含的用到了。


1.以下步骤执行了数据写入和DUMP,我们看到DUMP Datafile时只有一条记录存在于数据文件之上


[eygle@enmoteam1 ~]$ sqlplus eygle/eygle

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 7 14:24:00 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE eygle (n varchar2(20));

Table created.

SQL> INSERT INTO eygle VALUES('ENMOTECH');

1 row created.

SQL> commit;

Commit complete.

SQL> ALTER system checkpoint;

System altered.

SQL> INSERT INTO eygle VALUES('YHEM');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid)fno,

2 dbms_rowid.rowid_block_number (rowid) block# from eygle;

FNO BLOCK#

---------- ----------

 20 2362757

 20 2362757

SQL> alter system dump datafile 20 block 2362757;

System altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

----------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9165.trc

SQL> ! cat /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9165.trc

*** ACTION NAME:() 2017-04-07 14:25:11.875

Start dump data blocks tsn: 4 file#:20 minblk 2362757 maxblk 2362757

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4 rdba=86248837

BH (0x643e75e8) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x641a0000

set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0

dbwrid: 0 obj: 107958 objn: 107958 tsn: 4 afn: 20 hint: f

hash: [0x647f09d8,0x8fc75b48] lru: [0x62fefbd0,0x63fee080]

obj-flags: object_ckpt_list

ckptq: [0x8ff1f8f0,0x647de048] fileq: [0x8ff1fb70,0x8ff1fb70] objq: [0x87841758,0x87841758] objaq: [0x64feb008,0x87841738]

st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 1

flags: buffer_dirty block_written_once redo_since_read

LRBA: [0x346.21360.0] LSCN: [0x0.2af3ac2e] HSCN: [0x0.2af3ac2e] HSUB: [1]

BH (0x647f0928) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x64698000

set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0

dbwrid: 0 obj: 107957 objn: 107957 tsn: 4 afn: 20 hint: f

hash: [0x8fc75b48,0x643e7698] lru: [0x637ecb20,0x653f7750]

lru-flags: on_auxiliary_list

ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]

st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33

flags:

Block dump from disk:

buffer tsn: 4 rdba: 0x05240d85 (20/2362757)

scn: 0x0000.2af3ac29 seq: 0x01 flg: 0x06 tail: 0xac290601

frmt: 0x02 chkval: 0xe737 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x00007F35FD4B9200 to 0x00007F35FD4BB200

7F35FD4BB1F0 0801012C 4F4D4E45 48434554 AC290601 [,...ENMOTECH..).]

Block header dump: 0x05240d85

Object id on Block? Y

seg/obj: 0x1a5b6 csc: 0x00.2af3ac27 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x5240d80 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0005.011.000e5d0f 0x054000c9.56ba.12 --U- 1 fsc 0x0000.2af3ac29

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x05240d85

data_block_dump,data header at 0x7f35fd4b9264

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x7f35fd4b9264

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f8c

avsp=0x1f78

tosp=0x1f78

0xe:pti[0] nrow=1 offs=0

0x12:pri[0] offs=0x1f8c

block_row_dump:

tab 0, row 0, @0x1f8c

tl: 12 fb: --H-FL-- lb: 0x1 cc: 1

col 0: [ 8] 45 4e 4d 4f 54 45 43 48

end_of_block_dump

End dump data blocks tsn: 4 file#: 20 minblk 2362757 maxblk 2362757


2.进一步的DUMP Buffer Cache Block,这里有两个命令:


ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level level'; -- 这里的 level 是 表空间号 +1;


ALTER SESSION SET EVENTS 'immediate trace name buffer level level'; -- 这里的 level 是 相对文件号 * 4194304 + BlockNumber


经过转储可以看到Cache中的两条记录:

SQL> connect eygle/eygle

Connected.

SQL> select dbms_rowid.rowid_relative_fno(rowid)fno,

2 dbms_rowid.rowid_block_number (rowid) block# from eygle;

FNO BLOCK#

---------- ----------

 20 2362757

 20 2362757

SQL> select 20*4194304 +2362757 from dual;

20*4194304+2362757

------------------

  86248837

SQL> select default_tablespace from dba_users where username='EYGLE';

DEFAULT_TABLESPACE

------------------------------

USERS

SQL> SELECT ts# FROM sys.ts$ WHERE name = 'USERS';

TS#

----------

  4

SQL> ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level 5';

Session altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name buffer level 86248837';

Session altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

-----------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9214.trc

SQL> ! cat /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9214.trc


Unix process pid: 9214, image: oracle@enmoteam1 (TNS V1-V3)

*** 2017-04-07 14:26:33.824

*** SESSION ID:(377.2369) 2017-04-07 14:26:33.824

*** CLIENT ID:() 2017-04-07 14:26:33.824

*** SERVICE NAME:(SYS$USERS) 2017-04-07 14:26:33.824

*** MODULE NAME:(SQL*Plus) 2017-04-07 14:26:33.824

*** ACTION NAME:() 2017-04-07 14:26:33.824

Dump of buffer cache at level 10 for tsn=4 rdba=86248837

0641A1FE0 00000000 00000000 0401022C 4D454859 [........,...YHEM]

0641A1FF0 0801012C 4F4D4E45 48434554 AC2E0602 [,...ENMOTECH....]

Block header dump: 0x05240d85

Object id on Block? Y

seg/obj: 0x1a5b6 csc: 0x00.2af3ac27 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x5240d80 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0005.011.000e5d0f 0x054000c9.56ba.12 --U- 1 fsc 0x0000.2af3ac29

0x02 0x0007.00c.000e6208 0x05400148.5ca1.2d --U- 1 fsc 0x0000.2af3ac2e

bdba: 0x05240d85

data_block_dump,data header at 0x641a0064

===============

tsiz: 0x1f98

hsiz: 0x16

pbl: 0x641a0064

76543210

flag=--------

ntab=1

nrow=2

frre=-1

fsbo=0x16

fseo=0x1f84

avsp=0x1f6d

tosp=0x1f6d

0xe:pti[0] nrow=2 offs=0

0x12:pri[0] offs=0x1f8c

0x14:pri[1] offs=0x1f84

block_row_dump:

tab 0, row 0, @0x1f8c

tl: 12 fb: --H-FL-- lb: 0x1 cc: 1

col 0: [ 8] 45 4e 4d 4f 54 45 43 48

tab 0, row 1, @0x1f84

tl: 8 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 4] 59 48 45 4d

end_of_block_dump

BH (0x647f0928) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x64698000

set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0

dbwrid: 0 obj: 107957 objn: 107957 tsn: 4 afn: 20 hint: f

hash: [0x8fc75b48,0x643e7698] lru: [0x637ecb20,0x653f7750]

lru-flags: on_auxiliary_list

ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]

st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33

flags:

Buffer contents not dumped


这样的动手测试验证,非常有助于理解 Oracle 的运行原理,以及在这后台 Redo 的作用。三天不练手生,大家应当动手不懈。而且如果能够看到以上 TRACE 文件中的所有输出,那就是具有相当水准的专家风范了。

000.jpg