[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
--;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2991951/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ALTER SYSTEM FLUSH BUFFER_POOL
- 2.7.6.2.1 ALTER SYSTEM SET語句中的SCOPE子句
- alter system set event和set events的區別
- alter system set ... scope=... 中的scope的含義是什麼？
- alter table move與shrink space
- ORACLE之手動註冊監聽listener。alter system set local_listener="XXX"Oracle
- MySQL 資料庫 ALTER命令講解MySql資料庫
- cache操作：clean、invalidate與flush的含義
- 備份與恢復：polardb資料庫備份與恢復資料庫
- MySQL alter table時執行innobackupex全備再看Seconds_Behind_MasterMySqlAST
- Netty原始碼解析 -- ChannelOutboundBuffer實現與Flush過程Netty原始碼
- mysql關於FLUSH TABLES和FLUSH TABLES WITH READ LOCK的理解MySql
- SQL__ALTERSQL
- MySQL ALTER命令MySql
- Oracle ADG 備庫新增備庫Oracle
- JPA 實體髒檢查與儲存同步(Dirty & Flush)
- 【Mongodb】資料庫備份與還原MongoDB資料庫
- postgresql備份與恢復資料庫SQL資料庫
- SYSTEM 表空間管理及備份恢復
- mysql加快alter操作MySql
- 資料庫備份與恢復技術資料庫
- openGauss備庫wal-replay與query衝突
- [20231023]共享伺服器的問題2.txt伺服器
- [20231023]生成bbed的執行指令碼(bash shell).txt指令碼
- SQL Server2019資料庫備份與還原指令碼，資料庫可批量備份SQLServer資料庫指令碼
- MySQL：unblock with 'mysqladmin flush-hosts'MySqlBloC
- MySQL5.7 Waiting FOR TABLE FLUSHMySqlAI
- innodb_flush_log_at_trx_commitMIT
- InnoDB引擎之flush髒頁
- alter database disable thread 2Databasethread
- mysql的ALTER TABLE命令MySql
- alter table set unused column
- 達夢資料庫的備份與還原資料庫
- MySQL-19.資料庫備份與恢復MySql資料庫
- mysql資料庫-備份與還原實操MySql資料庫
- mongodb資料庫備份與恢復（資料庫資料遷移）MongoDB資料庫
- Linux下MySQL資料庫的備份與恢復LinuxMySql資料庫
- alter table nologging /*+APPEND PARALLEL(n)*/APPParallel