如何转储数据文件和Buffer Cache中的数据块
这篇文章是为了补充《Oracle性能优化与诊断案例精选》一书中的案例而写的，但是想想，也许还可以扩展一下，对于刚接触 Oracle 数据库的朋友们，试着回答一下以下几个问题，看看自己能否找到正确的答案：
当我们 insert 一条记录，不提交，这个数据在内存还是磁盘？
当我们 insert 一条记录，提交，这个数据在内存还是磁盘？
当我们 insert 一条记录，不提交，检查点，这个数据在内存还是磁盘？
当我们 insert 一条记录，提交，检查点，这个数据在内存还是磁盘？
先看看我的测试：
我们首先执行一个数据行插入，提交，执行检查点，这条数据被写入磁盘；
再插入一条数据，提交，此时（在检查点发生之前）这条数据不会写入磁盘；
当然 DUMP 之类的操作也不会引起这条数据写入磁盘；
那么如何验证：
通过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
*** 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 文件中的所有输出，那就是具有相当水准的专家风范了。
加入"云和恩墨大讲堂"微信群，参与讨论学习
搜索 盖国强（Eygle）微信号：eyygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。