Oracle dirty block的一些探讨(三)

繼續前面的話題：

如果block在buffer cache中發生了變化（也就意味着dirty block），執行alter system flush buffer_cache會将此block重新整理至資料檔案

當磁盤的block scn大于buffer cache中block scn時，刷出動作是否也會進行？Oracle buffer cache刷出至datafile時，是否會做scn判斷。

用bbed修改block scn高4位，從000a修改至000b，確定datafile block scn大于buffer cache block scn。

BBED> modify 0x000b

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /oradata/mcstar/zhoul01.dbf (0)

Block: 15511 Offsets: 12 to 523 Dba:0x00000000

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

000b0206 b06b0000 01002900 36160200 36640908 000ae81f 021f3200 913cc001

05002b00 750c0000 12048000 32051000 0080000a 77570908 07002c00 df0b0000

b02c8000 b7020300 01200000 3f640908 00000000 00000000 00010300 ffff1800

7a1f621f 621f0000 03008e1f 841f7a1f 311fff1e cb1e5c1e 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 0, Block 15511:

current = 0x6ab0, required = 0x6ab0

觀察目前在datafile字段依然為797979，轉換成字元串就是yyy。

BBED> dump offset 8185

File: /oradata/mcstar/zhoul01.dbf (0)

Block: 15511 Offsets: 8185 to 8191 Dba:0x00000000

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

79797902 063f64

<32 bytes per line>

在記憶體中可以字段也依然為yyy

SQL> select * from zhoul;

I NAME

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

1 yyy

2 bbb

3 ccc

在buffer cache中将yyy改為xxx，也就意味着15511為dirty block。

SQL> update zhoul set name='xxx' where name='yyy';

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

将其刷出buffer cache之後，再次讀取業務表格zhoul，可以看到name列為xxx，并不是之前實體上修改的yyy

SQL> select * from zhoul;

I NAME

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

1 xxx

2 bbb

3 ccc

在實體上也看到是xxx

BBED> dump block 15511 offset 8185

File: /oradata/mcstar/zhoul01.dbf (0)

Block: 15511 Offsets: 8185 to 8191 Dba:0x00000000

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

78787802 062e88

<32 bytes per line>

經過以上測試，我們可以推出以下結論：

[color=red] 當Oracle 認為block已為dirty狀态時，當它刷出記憶體時，實體上block scn作為判斷條件。也就意味着Oracle隻要認為block dirty，一旦執行alter system flush buffer_cache，就會刷出至資料檔案。[/color]