ITPub博客

首页 > 数据库 > Oracle > [20201231]RAC buffer states: XCUR, SCUR, PI,CR.txt

[20201231]RAC buffer states: XCUR, SCUR, PI,CR.txt

原创 Oracle 作者:lfree 时间:2021-01-01 20:21:22 0 删除 编辑

[20201231]RAC buffer states: XCUR, SCUR, PI,CR.txt

--//别人问及rac buffer状态PI,按照文档介绍就是past image.

■ Note If you have SYS privileges you can run the following query to see how many blocks you have in what
state: select state, count(*) from x$bh group by state; The commonest states are:  0—free, 1—XCUR
(exclusive current), 2—SCUR (shared current), 3—CR (available only for consistent read), 8—PI (past image). This
isn't a nice thing to do to your buffer cache, so resist the temptation to do it on a busy production system with a
large cache.

--//RAC涉及缓存融合以及资源掌控方面的问题,搞的比单实例的数据库要复杂N多。
--//不过我找到一篇blog。http://www.dbi-services.com/index.php/blog/entry/rac-buffer-states-xcur-scur-pi-ci
--//注:后面的ci我估计作者笔误,应该不是CI而是CR。我在自己的环境下重复测试,说明问题。

1.环境:
SYS@192.168.90.18:1521/fyhis/fyhis1> @ ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ cat bhy.sql
SELECT inst_id
        ,class#
        ,status
        ,lock_element_addr
        ,dirty
        ,temp
        ,ping
        ,stale
        ,direct
        ,new
    FROM gv$bh
   WHERE     objd = (SELECT data_object_id
                       FROM dba_objects
                      WHERE owner = 'SCOTT' AND object_name = 'DEPT')
         AND status != 'free'
ORDER BY inst_id;

$ cat bh.sql
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b# state
--------------------------------------------------------------------------------
col object_name format a20
col state format a10

select
b.inst_id,
b.hladdr,
  b.dbarfil,
  b.dbablk,
  b.class,
  decode(b.class,1,'data block',2,'sort block',3,'save undo block', 4,
  'segment header',5,'save undo header',6,'free list',7,'extent map',
  8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
  12,'bitmap index block',13,'file header block',14,'unused',
  15,'system undo header',16,'system undo block', 17,'undo header',
  18,'undo block') class_type,
  decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated') as state,
  b.tch,
  cr_scn_bas,
  cr_scn_wrp,
  cr_uba_fil,
  cr_uba_blk,
  cr_uba_seq,
  ba,
  b.LE_ADDR,
  (select object_name from dba_objects where data_object_id = b.obj) as object_name
from x$bh b
where
  dbarfil = &1 and
  dbablk = &2
;
--//增加LE_ADDR,inst_id字段,我发现在单实例上LE_ADDR输出是00.

SYS@192.168.90.18:1521/fyhis/fyhis1> select rowid from scott.dept where deptno=10;
ROWID
------------------
AAAVRCAAEAAAACHAAA

SYS@192.168.90.18:1521/fyhis/fyhis1> @ rowid AAAVRCAAEAAAACHAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     87106          4        135          0  0x1000087           4,135                alter system dump datafile 4 block 135 ;

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
no rows selected
--//没有输出正常,因为并没有访问对应的数据块。

2.测试:
--//session 1:
SYS@192.168.90.18:1521/fyhis/fyhis1> select * from scott.dept where rowid='AAAVRCAAEAAAACHAAA';
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135

   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         scur                1          0          0          0          0          0 0000000461890000 0000000117F2B4D8 DEPT

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bhy 4 135
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 scur       0000000117F2B4D8 N N N N N N
--//仅仅出现在1个数据库实例上。
--//注意state=scur,而不是像单实例出现的是state=xcur.也许这个是RAC的特性.

--//session 2,执行呢?
SYS@192.168.90.18:1521/fyhis/fyhis2> select * from scott.dept where rowid='AAAVRCAAEAAAACHAAA';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         2 00000004E239D750          4        135          1 data block         scur                2          0          0          0          0          0 000000037465A000 0000000223FB8D68 DEPT

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bhy 4 135
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 scur       0000000117F2B4D8 N N N N N N
         2          1 scur       0000000223FB8D68 N N N N N N
--//没有修改块,两边都是共享的。state=scur.

3.继续测试:
--//session 1,做一个修改,注我的测试与原链接不同,我没有刷新数据缓存:
SYS@192.168.90.18:1521/fyhis/fyhis1> select * from scott.dept where rowid='AAAVRCAAEAAAACHAAA' for update;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         xcur                2          0          0          0          0          0 00000000A076A000 0000000117F2B4D8 DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  2 3703090073          7          0          0          0 0000000461890000 00               DEPT

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bhy
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 xcur       0000000117F2B4D8 Y N N N N N
         1          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N

--//session 2:
SYS@192.168.90.18:1521/fyhis/fyhis2> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090072          7          0          0          0 000000037465A000 00               DEPT

--//看看在两个实例上执行bh.sql脚本的输出。
--//实例2的STATE从scur->CR,而CR_SCN_BAS从0变成了3703090072。
--//实例1的STATE从scur->CR,而CR_SCN_BAS从0变成了3703090073。也就是先要修改实例2的状态在修改实例1的状态们(从scn信息可以看出来)。
--//实例1上生成新块state=xcur.
--//在session 2继续执行:

SYS@192.168.90.18:1521/fyhis/fyhis2> select * from scott.dept where rowid='AAAVRCAAEAAAACHAAA';
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090708          7          0          0          0 0000000384620000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090072          7          0          0          0 000000037465A000 00               DEPT
--//增加1行,state=cr.

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bhy
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 xcur       0000000117F2B4D8 Y N N N N N
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N

--//session 1:
SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         cr                  0 3703090708          7          3       4278      36326 0000000190F96000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         xcur                2          0          0          0          0          0 00000000A076A000 0000000117F2B4D8 DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  2 3703090073          7          0          0          0 0000000461890000 00               DEPT
--//你可以发现现在实例1上构造出CR_SCN_BAS=3703090708,然后在传输到实例2上。

--//session 2,注意修改发生在实例1:
SYS@192.168.90.18:1521/fyhis/fyhis2> alter system checkpoint;
System altered.

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bhy
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 xcur       0000000117F2B4D8 N N N N N N
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
--//dirty标识清除。发生在实例1.

4.测试STATE=PI的状态如何出现:
--//session 1:
SYS@192.168.90.18:1521/fyhis/fyhis1> commit ;
Commit complete.

SYS@192.168.90.18:1521/fyhis/fyhis1> alter system checkpoint;
System altered.

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bhy
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 xcur       0000000117F2B4D8 Y N N N N N
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
--//变成了脏块.
--//session 2:
SYS@192.168.90.18:1521/fyhis/fyhis2> select * from scott.dept where rowid='AAAVRCAAEAAAACHAAA' for update;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bhy
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         1          1 pi         0000000117F2B4D8 Y N N N N N
         2          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 xcur       0000000223FB8D68 Y N N N N N
7 rows selected.
--//注意看dirty标识,两个依旧标识为Y。实例1原来的state 从xcur->pi,也就是post image.

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         2 00000004E239D750          4        135          1 data block         xcur                1          0          0          0          0          0 00000003C8108000 0000000223FB8D68 DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000003B5A72000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090708          7          0          0          0 0000000384620000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090072          7          0          0          0 000000037465A000 00               DEPT

--//session 1查看:
SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         cr                  0 3703090708          7          3       4278      36326 0000000190F96000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         pi                  2          0          0          0          0          0 00000000A076A000 0000000117F2B4D8 DEPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        
         1 00000004E22F9FD8          4        135          1 data block         cr                  2 3703090073          7          0          0          0 0000000461890000 00               DEPT
--//实例1的原来state=xcur  变成了 state = pi.

--//session 2:
SYS@192.168.90.18:1521/fyhis/fyhis2> alter system checkpoint;
System altered.

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bhy
   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 cr         00               N N N N N N
         2          1 xcur       0000000223FB8D68 N N N N N N
7 rows selected.
--//state=pi标识清除,变成了cr,而且lock_element_addr=00.并且标识dirty变成Y->N(2个实例)

--//session 1:
SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         cr                  0 3703090708          7          3       4278      36326 0000000190F96000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000000A076A000 00               DEPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         1 00000004E22F9FD8          4        135          1 data block         cr                  2 3703090073          7          0          0          0 0000000461890000 00               DEPT
--//特别注意下划线的CR_SCN_BAS=3703092337.原来是0. 很奇怪的是TCH从2变成了1,LE_ADDR=00.

--//session 2:
SYS@192.168.90.18:1521/fyhis/fyhis2> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         2 00000004E239D750          4        135          1 data block         xcur                1          0          0          0          0          0 00000003C8108000 0000000223FB8D68 DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000003B5A72000 00               DEPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090708          7          0          0          0 0000000384620000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090072          7          0          0          0 000000037465A000 00               DEPT
--//注意看下划线以及CR_SCN_BAS列的变化。你可以猜测大概操作流程,先从实例1传输或者拷贝过来,然后在修改。
--//从实例1 CR_SCN_BAS=0 变成 3703092337,就可以看出来。

5.继续测试:
--//从实例2读取特定scn的情况。
--//7,3703090073 = scn(10): 33767861145 = scn(16): 0x7dcb8ab99
--//session 2:
SYS@192.168.90.18:1521/fyhis/fyhis2> commit ;
Commit complete.

SYS@192.168.90.18:1521/fyhis/fyhis2> select * from scott.dept as of scn 33767861145 where rowid='AAAVRCAAEAAAACHAAA' ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         2 00000004E239D750          4        135          1 data block         xcur                1          0          0          0          0          0 00000003C8108000 0000000223FB8D68 DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000003B5A72000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  2 3703090708          7          0          0          0 0000000384620000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090072          7          0          0          0 000000037465A000 00               DEPT

--//session 1:
SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         cr                  0 3703090708          7          3       4278      36326 0000000190F96000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000000A076A000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  2 3703090073          7          0          0          0 0000000461890000 00               DEPT
--//没有变化,甚至tch值。视乎这样的查询仅仅通过实例1传输过来。
--//session 1,重复上面的查询:
SYS@192.168.90.18:1521/fyhis/fyhis1> select * from scott.dept as of scn 33767861145 where rowid='AAAVRCAAEAAAACHAAA' ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         cr                  0 3703090708          7          3       4278      36326 0000000190F96000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000000A076A000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  3 3703090073          7          0          0          0 0000000461890000 00               DEPT
--//可以发现TCH从2 -> 3.

--//session 2,改变scn看看呢?
SYS@192.168.90.18:1521/fyhis/fyhis2> select * from scott.dept as of scn 33767861146 where rowid='AAAVRCAAEAAAACHAAA' ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
--//33767861146    = scn_wrap,scn_base(10): 7,3703090074 = scn_wrap,scn_base(16): 0x7,0xdcb8ab9a

SYS@192.168.90.18:1521/fyhis/fyhis2> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         2 00000004E239D750          4        135          1 data block         xcur                1          0          0          0          0          0 00000003C8108000 0000000223FB8D68 DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000003B5A72000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  3 3703090708          7          0          0          0 0000000384620000 00               DEPT
         2 00000004E239D750          4        135          1 data block         cr                  1 3703090072          7          0          0          0 000000037465A000 00               DEPT
--//如果你仔细观察就能发现CR_SCN_BAS=3703090708的TCH从2->3.也就是构造从当前实例CR_SCN_BAS=3703090708 反向操作会 scn= 7,3703090074.
--//session 1:

SYS@192.168.90.18:1521/fyhis/fyhis1> @ bh 4 135
   INST_ID HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               LE_ADDR          OBJECT_NAME
---------- ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- --------------------
         1 00000004E22F9FD8          4        135          1 data block         cr                  0 3703090708          7          3       4278      36326 0000000190F96000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  1 3703092337          7          0          0          0 00000000A076A000 00               DEPT
         1 00000004E22F9FD8          4        135          1 data block         cr                  3 3703090073          7          0          0          0 0000000461890000 00               DEPT

6.总结:
--//至于为什么出现PI,按照文档介绍节省recovery的恢复时间,一些细节操作我的分析能力。
--//测试还是有点乱....
--//http://www.dbi-services.com/index.php/blog/entry/rac-buffer-states-xcur-scur-pi-ci
Here are the states we have seen here:

XCUR: current version of the block - holding an exclusive lock for it
SCUR: current version of the block that can be share because no modification were done
CR: only valid for consistent read, after applying the necessary undo to get it back to requried SCN
PI: past image of a modified current block, kept until the latest version is checkpointed

and the other possible states:

FREE: The buffer is not currently in use.
READ: when the block is being read from disk
MREC: when the block is being recovered for media recovery
IREC: when the block is being recovered for crash recovery

--//实际上rac并非是什么好东西,处理不好比单实例要慢,特别是原来单实例就存在性能问题的情况下,rac环境可能放大这样的效果.
--//而且从上面测试也可以看出几点:
--//1.事务应该还是尽可能的快速提交.
--//2.内联的网络传输能力要尽量的块.尽可能进行业务分割.相同的业务在1个实例处理.我还见过内联网络使用hub的情况.
--//  这种网络设备一般就是100M,全双工也就是200M,最大传输能力就是20M/秒.这样如果内联流量很大,出现问题的可能性很大.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2747003/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2823
  • 访问量
    6620754