[20150515]关于块转储问题.txt-CSDN博客

[20150515]关于块转储问题.txt

--我自己在学习oracle有时候使用块转储时,发现转储的内容跟我自己的想象不一样.
--正好前一阵子ITPUB有人也遇到类似的问题,自己做一个简单探究:

1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> select rowid,depty.* from depty ;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AABKCeAAEAAAAXzAAA         10 ACCOUNTING     NEW YORK
AABKCeAAEAAAAXzAAB         20 RESEARCH       DALLAS1
AABKCeAAEAAAAXzAAC         30 SALES          CHICAGO
AABKCeAAEAAAAXzAAD         40 OPERATIONS     BOSTON
AABKCeAAEAAAAXzAAE         50 MARKETING      LONDON

SCOTT@test> @ lookup_rowid AABKCeAAEAAAAXzAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
303262 4 1523 0 4,1523 alter system dump datafile 4 block 1523

2.开始测试:
SCOTT@test> update depty set loc=lower(loc) where deptno=50;
1 row updated.

SCOTT@test> alter system dump datafile 4 block 1523;
System altered.

--仔细查看转储,可以发现并没有看到修改的内容,说明读取块转储并没有从内存.
--而是直接从块里面转储出来的.

2A9714B980 00000000 00000000 0203002C 4D0933C1 [........,....3.M]
2A9714B990 454B5241 474E4954 4E4F4C06 2C4E4F44 [ARKETING.LONDON,]
2A9714B9A0 C1020300 504F0A29 54415245 534E4F49 [....).OPERATIONS]
2A9714B9B0 534F4206 2C4E4F54 C1020300 4153051F [.BOSTON,......SA]
2A9714B9C0 0753454C 43494843 2C4F4741 C1020300 [LES.CHICAGO,....]
2A9714B9D0 45520815 52414553 44074843 414C4C41 [..RESEARCH.DALLA]
2A9714B9E0 002C3153 0BC10203 4343410A 544E554F [S1,......ACCOUNT]
2A9714B9F0 08474E49 2057454E 4B524F59 99B70602 [ING.NEW YORK....]

--如果使用文件代替结果一样:
alter system dump datafile '/u01/app/oracle11g/oradata/test/users01.dbf' block 1523;

3.做一个跟踪也能充分说明问题:

SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
203 5695 7856 alter system kill session '203,5695' immediate;

$ cd /proc/7856/fd
$ ll
total 15
lr-x------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 0 -> /dev/null
l-wx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 1 -> /dev/null
l-wx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 10 -> pipe:[75386288]
l-wx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 11 -> /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_7856_127_0_0_1.trm
l-wx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 2 -> /dev/null
lrwx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 256 -> /u01/app/oracle11g/oradata/test/system01.dbf
lrwx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 257 -> /u01/app/oracle11g/oradata/test/users01.dbf
lrwx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 258 -> /u01/app/oracle11g/oradata/test/temp01.dbf
lr-x------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 3 -> /dev/null
lr-x------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 4 -> /dev/null
lr-x------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 5 -> /u01/app/oracle11g/product/11.2.0/db_2/rdbms/mesg/oraus.msb
lr-x------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 6 -> /proc/7856/fd
lr-x------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 7 -> pipe:[75386287]
lr-x------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 8 -> /dev/zero
l-wx------ 1 oracle11g oinstall 64 2015-05-15 08:50:50 9 -> /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_7856_127_0_0_1.trc

$ strace -f -p 7856 -o /tmp/aaa.txt
--重复上面操作:alter system dump datafile 4 block 1523;查看/tmp/aaa.txt内容:

7856 gettimeofday({1431651017, 311090}, NULL) = 0
7856 gettimeofday({1431651017, 311142}, NULL) = 0
7856 pread(257, "\6\242\0\0\363\5\0\1\267\231o\364\2\0\2\4\315\240\0\0\1"..., 8192, 12476416) = 8192
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
7856 gettimeofday({1431651017, 311279}, NULL) = 0
7856 gettimeofday({1431651017, 311333}, NULL) = 0
7856 write(9, "Block dump from disk:", 21) = 21
7856 write(9, "\n", 1) = 1

--注意看~,这里就是读取的数据块的函数. 1523*8192=12476416,偏移量正好对上.
$ man pread
PREAD(2) Linux Programmer's Manual PREAD(2)

NAME
pread, pwrite - read from or write to a file descriptor at a given offset

--充分说明是从文件读取的.

4.如果要看到修改的内容,简单的方式是:
alter system checkpoint;
alter system flush buffer_cache;

--再查看就ok了.

--这个时候就能看到修改的内容:
2A9714B980 00000000 00000000 0203022C 4D0933C1 [........,....3.M]
2A9714B990 454B5241 474E4954 6E6F6C06 2C6E6F64 [ARKETING.london,]
2A9714B9A0 C1020300 504F0A29 54415245 534E4F49 [....).OPERATIONS]
2A9714B9B0 534F4206 2C4E4F54 C1020300 4153051F [.BOSTON,......SA]
2A9714B9C0 0753454C 43494843 2C4F4741 C1020300 [LES.CHICAGO,....]
2A9714B9D0 45520815 52414553 44074843 414C4C41 [..RESEARCH.DALLA]
2A9714B9E0 002C3153 0BC10203 4343410A 544E554F [S1,......ACCOUNT]
2A9714B9F0 08474E49 2057454E 4B524F59 84590601 [ING.NEW YORK..Y.]

Block header dump: 0x010005f3
Object id on Block? Y
seg/obj: 0x4a09e csc: 0x02.f46f99b5 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10005f0 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0xffff.000.00000000 0x00000000.0000.00 C---    0 scn 0x0002.f46f99b5
0x02   0x0005.012.0000c730 0x00c040f1.318f.24 ----    1 fsc 0x0000.00000000
0x03   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000

--也就是讲oracle做块转储,仅仅从数据文件读取.无论在何种情况下.