« Oracle优化，云上启航 - 2017 RWP中国之旅圆满结束 | Blog首页 | 关于Oracle 12.2 Sharding，你想知道的都在这里 »

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

在《Oracle性能优化与诊断案例精选》一书中有一个测试案例，读者提出异议，我做了一个更详细的测试供读者参考。书中内容并无问题，但是过程省略了一些细节，导致读者的困惑，作为主编，我要表示歉意。

测试表明的结果是：DUMP Datafile不会引起脏数据块写入数据文件。

但是如何验证呢？

我们首先执行一个数据行插入提交，执行检查点，这条数据被写入磁盘；

再插入一条数据，提交，此时这条数据不会写入磁盘。

通过DUMP BLOCK，从数据文件转储，只能看到一行记录，通过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

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

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

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

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: enmoteam1

Release: 2.6.39-200.24.1.el6uek.x86_64

Version: #1 SMP Sat Jun 23 02:39:07 EDT 2012

Machine: x86_64

Instance name: enmot1

Redo thread mounted by this instance: 1

Oracle process number: 82

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

*** 2017-04-07 14:25:11.875

*** SESSION ID:(256.5763) 2017-04-07 14:25:11.875

*** CLIENT ID:() 2017-04-07 14:25:11.875

*** SERVICE NAME:(SYS$USERS) 2017-04-07 14:25:11.875

*** MODULE NAME:(SQL*Plus) 2017-04-07 14:25:11.875

*** 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

7F35FD4B9200 0000A206 05240D85 2AF3AC29 06010000 [......$.)..*....]

7F35FD4B9210 0000E737 00000001 0001A5B6 2AF3AC27 [7...........'..*]

7F35FD4B9220 00000000 00320002 05240D80 00110005 [......2...$.....]

7F35FD4B9230 000E5D0F 054000C9 001256BA 00002001 [.]....@..V... ..]

7F35FD4B9240 2AF3AC29 00000000 00000000 00000000 [)..*............]

7F35FD4B9250 00000000 00000000 00000000 00000000 [................]

7F35FD4B9260 00000000 00010100 0014FFFF 1F781F8C [..............x.]

7F35FD4B9270 00001F78 1F8C0001 5029F80F 5029F80F [x.........)P..)P]

7F35FD4B9280 00000000 00000000 00000000 00000000 [................]

7F35FD4B9290 008012A1 000000F4 00000000 00000000 [................]

7F35FD4B92A0 00000000 00000000 00000000 00000000 [................]

Repeat 1 times

7F35FD4B92C0 000018C3 000D873C 00000000 0084A200 [....<...........]

7F35FD4B92D0 00000100 00000000 00000000 00000000 [................]

7F35FD4B92E0 00000000 00000000 00000000 00000000 [................]

Repeat 9 times

7F35FD4B9380 00000000 00000000 00000000 21112211 [.............".!]

7F35FD4B9390 22111111 11121121 12112112 21111121 [..."!....!..!..!]

7F35FD4B93A0 11111111 11111111 11111112 21111111 [...............!]

7F35FD4B93B0 11111111 21121111 11212111 11111111 [.......!.!!.....]

7F35FD4B93C0 11211112 21221111 12111111 11111111 [..!..."!........]

7F35FD4B93D0 11112111 11111111 12112122 12111111 [.!......"!......]

7F35FD4B93E0 11111111 11111311 22111111 11222111 [...........".!".]

7F35FD4B93F0 11111111 11111131 11111112 11221111 [....1.........".]

7F35FD4B9400 11111211 11111231 11111111 00000000 [....1...........]

7F35FD4B9410 00000000 00000000 00000000 00000000 [................]

Repeat 477 times

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

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

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

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

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: enmoteam1

Release: 2.6.39-200.24.1.el6uek.x86_64

Version: #1 SMP Sat Jun 23 02:39:07 EDT 2012

Machine: x86_64

Instance name: enmot1

Redo thread mounted by this instance: 1

Oracle process number: 83

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

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: [0x64fecc98,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]

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

scn: 0x0000.2af3ac2e seq: 0x02 flg: 0x02 tail: 0xac2e0602

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

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

Dump of memory from 0x00000000641A0000 to 0x00000000641A2000

0641A0000 0000A206 05240D85 2AF3AC2E 02020000 [......$....*....]

0641A0010 00000000 00000001 0001A5B6 2AF3AC27 [............'..*]

0641A0020 00000000 00320002 05240D80 00110005 [......2...$.....]

0641A0030 000E5D0F 054000C9 001256BA 00002001 [.]....@..V... ..]

0641A0040 2AF3AC29 000C0007 000E6208 05400148 [)..*.....b..H.@.]

0641A0050 002D5CA1 00002001 2AF3AC2E 00000000 [.\-.. .....*....]

0641A0060 00000000 00020100 0016FFFF 1F6D1F84 [..............m.]

0641A0070 00001F6D 1F8C0002 50291F84 5029F80F [m.........)P..)P]

0641A0080 00000000 00000000 00000000 00000000 [................]

0641A0090 008012A1 000000F4 00000000 00000000 [................]

0641A00A0 00000000 00000000 00000000 00000000 [................]

Repeat 1 times

0641A00C0 000018C3 000D873C 00000000 0084A200 [....<...........]

0641A00D0 00000100 00000000 00000000 00000000 [................]

0641A00E0 00000000 00000000 00000000 00000000 [................]

Repeat 9 times

0641A0180 00000000 00000000 00000000 21112211 [.............".!]

0641A0190 22111111 11121121 12112112 21111121 [..."!....!..!..!]

0641A01A0 11111111 11111111 11111112 21111111 [...............!]

0641A01B0 11111111 21121111 11212111 11111111 [.......!.!!.....]

0641A01C0 11211112 21221111 12111111 11111111 [..!..."!........]

0641A01D0 11112111 11111111 12112122 12111111 [.!......"!......]

0641A01E0 11111111 11111311 22111111 11222111 [...........".!".]

0641A01F0 11111111 11111131 11111112 11221111 [....1.........".]

0641A0200 11111211 11111231 11111111 00000000 [....1...........]

0641A0210 00000000 00000000 00000000 00000000 [................]

Repeat 476 times

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

补充测试，供读者参考，学习。

By eygle on 2017-04-07 15:29 | Comments (0) | Internal | 3243 |

dump