[20231023]备库与alter system flush buffer_cache.txt

来源：这里教程网时间：2026-03-03 19:01:46 作者：

[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 --;

编辑推荐：

返回列表

[20231023]备库与alter system flush buffer_cache.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推