[20231023]備庫與alter system flush buffer_cache.txt

[20231023]備庫與alter system flush buffer_cache.txt

--//測試遇到的問題,在備庫執行alter system flush buffer_cache;重新整理資料快取命令無效.
--//透過例子驗證:

1.環境：
SYS@192.168.100.235:1521/orcl> @ ver1
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.
--//235 主庫，237 備庫、

2.建立測試例子：
create table t1  tablespace users pctfree 99 as select level id, lpad(level, 3500, 'T1') vc from dual connect by level <= 500 order by dbms_random.random;
--//建立的表T1非常特殊1塊1條記錄,這樣便於後面的分析。

3.測試:
--//在備庫測試:
SYS@192.168.100.237:1521/orcldg> select rowid,id,substr(vc,3498,3) from t1 where id=1;
ROWID                      ID SUBSTR
------------------ ---------- ------
AAA8CMAAHAAAErrAAA          1 1T1

SYS@192.168.100.237:1521/orcldg> @ rowid AAA8CMAAHAAAErrAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
    245900          7      19179          0  0x1C04AEB           7,19179              alter system dump datafile 7 block 19179

SYS@192.168.100.237:1521/orcldg> @ bh1 7 19179
DBARFIL      DBABLK CLASS CLASS_TYPE         STATE STATE1        TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
      7       19179     1 data block             5 mrec            0          0          0          0          0          0          0          0          0          0          0          0          0     524288          0          0          0 T1

SYS@192.168.100.237:1521/orcldg> alter system flush buffer_cache ;
System altered.

SYS@192.168.100.237:1521/orcldg> @ bh1 7 19179
DBARFIL      DBABLK CLASS CLASS_TYPE         STATE STATE1        TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
      7       19179     1 data block             5 mrec            0          0          0          0          0          0          0          0          0          0          0          0          0     524288          0          0          0 T1
--//備庫上記錄的STATE1=mrec，也就是在備庫執行alter system flush buffer_cache ;,根本無法重新整理這些資料塊從資料快取。

--//在主庫測試:
SYS@192.168.100.235:1521/orcl> select rowid,id,substr(vc,3498,3) from t1 where id=1;
ROWID                      ID SUBSTR
------------------ ---------- ------
AAA8CMAAHAAAErrAAA          1 1T1

SYS@192.168.100.235:1521/orcl> @ bh1 7 19179
DBARFIL      DBABLK CLASS CLASS_TYPE         STATE STATE1        TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
      7       19179     1 data block             1 xcur            2          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0      65535 T1
      7       19179     1 data block             0 free            0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 T1
      7       19179     1 data block             0 free            0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 T1
--//STATE1=xcur

SYS@192.168.100.235:1521/orcl> alter system flush buffer_cache;
System altered.

SYS@192.168.100.235:1521/orcl> @ bh1 7 19179
DBARFIL      DBABLK CLASS CLASS_TYPE         STATE STATE1        TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
      7       19179     1 data block             0 free            0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 T1
      7       19179     1 data block             0 free            0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 T1
      7       19179     1 data block             0 free            0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 T1
--//重新整理資料快取後變成了STATE1=free.

--//補充測試，經歷幾天，該資料塊應該不再資料快取，繼續測試看看，驗證是否是state1=mrec的原因。
SYS@192.168.100.237:1521/orcldg> @ bh1 7 19179
no rows selected

SYS@192.168.100.237:1521/orcldg> select rowid,id,substr(vc,3498,3) from t1 where id=1;
ROWID                      ID SUBSTR
------------------ ---------- ------
AAA8CMAAHAAAErrAAA          1 1T1

SYS@192.168.100.237:1521/orcldg> @ bh1 7 19179
DBARFIL      DBABLK CLASS CLASS_TYPE         STATE STATE1        TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
      7       19179     1 data block             1 xcur            1          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0      65535 T1
--//注意看現在狀態是xcur.

SYS@192.168.100.237:1521/orcldg> alter system flush buffer_cache ;
System altered.

SYS@192.168.100.237:1521/orcldg> @ bh1 7 19179
DBARFIL      DBABLK CLASS CLASS_TYPE         STATE STATE1        TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
      7       19179     1 data block             0 free            0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 T1
--//可以發現state1=free,這樣是可以重新整理出共享池的.mrec狀態的不行.

SYS@192.168.100.237:1521/orcldg> SELECT /*+ leading (t1 t2) use_nl(t2) */ t1.id,substr(t1.vc,3496,5) vc1,substr(t2.vc,3496,5) vc2 FROM t1 , t2 WHERE t1.id = t2.id AND t1.id between 1 and 10;
        ID VC1        VC2
---------- ---------- ----------
         1 1T1T1      2T2T1
         2 1T1T2      2T2T2
        10 1T110      2T210
         9 1T1T9      2T2T9
         3 1T1T3      2T2T3
         4 1T1T4      2T2T4
         5 1T1T5      2T2T5
         6 1T1T6      2T2T6
         7 1T1T7      2T2T7
         8 1T1T8      2T2T8
10 rows selected.
--//這樣可以再現我連結遇到的情況.id=10,9在前面輸出.
--// http://blog.itpub.net/267265/viewspace-2990586/ =>[20231020]為什麼重新整理快取後輸出記錄順序發生變化5.txt

SYS@192.168.100.237:1521/orcldg> select * from V$SESSION_WAIT_HISTORY where sid=1276 and event ='db file parallel read';
       SID       SEQ#     EVENT# EVENT                 P1TEXT      P1 P2TEXT      P2 P3TEXT      P3  WAIT_TIME WAIT_TIME_MICRO TIME_SINCE_LAST_WAIT_MICRO     CON_ID
---------- ---------- ---------- --------------------- ---------- --- ---------- --- ---------- --- ---------- --------------- -------------------------- ----------
      1276          1        184 db file parallel read files        1 blocks       7 requests     7          0              37                         94          0
      1276          4        184 db file parallel read files        1 blocks       8 requests     8          0              42                         70          0
--//確實會出現2次db file parallel read.

5.附上bh1.sql指令碼:
 $ cat bh1.sql
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b#
--------------------------------------------------------------------------------

col object_name format a12
col state1 format a6
col dbarfil format 999999
col dbablk  format 9999999999
col state format 99
col class format 99

select
  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,
  state,
  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 state1,
  b.tch,
  cr_scn_bas,
  cr_scn_wrp,
  cr_uba_fil,
  cr_uba_blk,
  cr_uba_seq,
  cr_uba_rec,
  cr_xid_usn,
  cr_xid_slt,
  cr_xid_sqn,
  cr_cls_bas,
  cr_cls_wrp,
  lrba_seq,
  lrba_bno,
  hscn_bas,
  hscn_wrp,
  hsub_scn,
  (select object_name from dba_objects where object_id = b.obj) as object_name
from x$bh b
where
  dbarfil = &1 and
  dbablk = &2
;
--select b.*,
--  decode(b.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,
--  (select object_name from dba_objects where object_id = b.obj) as object_name
--from x$bh b
--where
--  dbarfil = &1 and
--  dbablk = &2
--;