[20231023]备库与alter system flush buffer_cache.txt
--//测试遇到的问题,在备库执行alter system flush buffer_cache;刷新数据缓存命令无效.
--//通过例子验证:
1.环境：
[email protected]:1521/orcl> @ ver1
[email protected]: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.测试:
--//在备库测试:
[email protected]:1521/orcldg> select rowid,id,substr(vc,3498,3) from t1 where id=1;
ROWID ID SUBSTR
------------------ ---------- ------
AAA8CMAAHAAAErrAAA 1 1T1
[email protected]: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
[email protected]: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
[email protected]:1521/orcldg> alter system flush buffer_cache ;
System altered.
[email protected]: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 ;,根本无法刷新这些数据块从数据缓存。
--//在主库测试:
[email protected]:1521/orcl> select rowid,id,substr(vc,3498,3) from t1 where id=1;
ROWID ID SUBSTR
------------------ ---------- ------
AAA8CMAAHAAAErrAAA 1 1T1
[email protected]: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
[email protected]:1521/orcl> alter system flush buffer_cache;
System altered.
[email protected]: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的原因。
[email protected]:1521/orcldg> @ bh1 7 19179
no rows selected
[email protected]:1521/orcldg> select rowid,id,substr(vc,3498,3) from t1 where id=1;
ROWID ID SUBSTR
------------------ ---------- ------
AAA8CMAAHAAAErrAAA 1 1T1
[email protected]: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.
[email protected]:1521/orcldg> alter system flush buffer_cache ;
System altered.
[email protected]: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状态的不行.
[email protected]: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
[email protected]: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
--;