[20201231]單例項data buffer states.txt
[20201231]單例項data buffer states.txt
--//別人問的一個問題PI是什麼狀態，PI表示past image.是rac環境特有的狀態，不會出現在單例項的資料庫中。
--//既然提到這個問題，今天就是探究一下單例項data buffer states。
1.環境：
SCOTT@book> @ 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 bh.sql
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b#
--------------------------------------------------------------------------------
col object_name format a20
col state format a10
select
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,
(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
;
2.測試：
--//重啟資料庫略。
--//session 1:
SCOTT@book> select rowid from dept where deptno=10;
ROWID
------------------
AAAVRCAAEAAAACHAAA
SCOTT@book> @ rowid AAAVRCAAEAAAACHAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87106 4 135 0 0x1000087 4,135 alter system dump datafile 4 block 135 ;
--//session 2:
SYS@book> @ bh 4 135
no rows selected
--//現在沒有查詢到資訊是正常的，我前面的查詢僅僅輸出rowid，透過主鍵索引就可以定位，並沒有訪問對應的資料塊dba=4,135.
--//session 1:
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block xcur 1 0 0 0 0 0 0000000076D08000 DEPT
--//STATE=xcur，tch=1.
--//session 3:
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block xcur 2 0 0 0 0 0 0000000076D08000 DEPT
--//STATE=xcur,TCH=2,只要session 3的執行與前面間隔3秒，就出現tch增加。
3.繼續測試：
--//session 1：
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA' for update;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block xcur 2 0 0 0 0 0 000000007484C000 DEPT
0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT
--//注意看ba 資料塊地址，可以發現原來的STATE變成了cr。而新增加1個快取，state=xcur.也就是當前狀態。
--//session 1,再次執行查詢呢？
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT
--//注意看ba以及tch欄位，tch增加到2.可以發現實際上訪問的是BA=000000007484C000 的資料快取。
--// session 3,如果這個時候訪問該塊呢？
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT
--//增加一個資料快取，state=CR,也就是透過state=xcur透過undo構造出來的資料快取塊。
--// session 3,如果再次執行呢？
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT
--//再次增加1個資料快取塊。當然不會無限增加下去，受隱含引數_db_block_max_cr_dba的控制。
SYS@book> @ hide _db_block_max_cr_dba
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------- -------------------------------------------- ------------- ------------- ------------ ----- ---------
_db_block_max_cr_dba Maximum Allowed Number of CR buffers per dba TRUE 6 6 FALSE FALSE
--//session 3:
SCOTT@book> select dbms_flashback.get_system_change_number,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA';
GET_SYSTEM_CHANGE_NUMBER DEPTNO DNAME LOC
------------------------ ---------- -------------- -------------
1.3310E+10 10 ACCOUNTING NEW YORK
SCOTT@book> set numw 12
SCOTT@book> select dbms_flashback.get_system_change_number,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA';
GET_SYSTEM_CHANGE_NUMBER DEPTNO DNAME LOC
------------------------ ------------ -------------- -------------
13309689283 10 ACCOUNTING NEW YORK
--//13309689283 = scn_wrap,scn_base(10): 3,424787395 = scn_wrap,scn_base(16): 0x3,0x1951bdc3
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787384 3 3 620 18543 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
0000000084D25320 4 135 1 data block cr 2 424785950 3 0 0 0 0000000076D08000 DEPT
6 rows selected.
--//注意看CR_SCN_BAS列，實際上僅僅與上面的查詢 dbms_flashback.get_system_change_number相差1。
--//3,424787394 = scn(10): 13309689282 = scn(16): 0x31951bdc2
--//session 3,訪問scn=3,424787384時的狀態呢？
--//3,424787384 = scn(10): 13309689272 = scn(16): 0x31951bdb8
SCOTT@book> select * from dept as of scn 13309689272 where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2：
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
--//注意看下劃線那行，可以發現並沒有增加記錄，而該行TCH變成2.
4.繼續測試:
--//session 3,繼續執行：
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
6 rows selected.
--//session 3,繼續執行：
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424787951 3 3 620 18543 0000000073ADE000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786276 3 3 620 18543 0000000073C72000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
7 rows selected.
--//session 3,繼續執行：
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424787987 3 3 620 18543 0000000073EE4000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787951 3 3 620 18543 0000000073ADE000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
7 rows selected.
--//可以發現STATE=CR的行不再增加，刪除了CR_SCN_BAS最小的那行。
5.如果髒塊寫盤呢？
--//session 2:
SYS@book> alter system checkpoint;
System altered.
--//可以執行多次,避免IMU的影響。
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424787987 3 3 620 18543 0000000073EE4000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787951 3 3 620 18543 0000000073ADE000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787921 3 3 620 18543 0000000073AB8000 DEPT
0000000084D25320 4 135 1 data block cr 1 424787394 3 3 620 18543 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block cr 2 424787384 3 3 620 18543 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block cr 1 424786450 3 3 620 18543 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block xcur 3 0 0 0 0 0 000000007484C000 DEPT
7 rows selected.
--//可以發現state並沒有變化。將髒塊寫盤，可以發現state狀態不會改變。
--//重新整理資料快取呢？
SYS@book> alter system flush BUFFER_CACHE;
System altered.
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073EE4000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073ADE000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073AB8000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 000000007484C000 DEPT
7 rows selected.
--//flush BUFFER_CACHE後，state全表變成free。
6.繼續：
--//session 1，注意我沒有提交:
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
0000000084D25320 4 135 1 data block xcur 1 0 0 0 0 0 0000000074290000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073EE4000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073ADE000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073AB8000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 000000007484C000 DEPT
8 rows selected.
SYS@book> alter system flush BUFFER_CACHE;
System altered.
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000074290000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073EE4000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073ADE000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073AB8000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073A82000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 0000000073D5C000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 00000000738D0000 DEPT
0000000084D25320 4 135 1 data block free 0 0 0 0 0 0 000000007484C000 DEPT
8 rows selected.
--//session 3，換成session 3執行呢？
SCOTT@book> select * from dept where rowid='AAAVRCAAEAAAACHAAA';
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT
0000000084D25320 4 135 1 data block xcur 0 0 0 0 0 0 00000000748DE000 DEPT
--//可以發現產生2條記錄，先從資料檔案讀取塊在透過undo構造新的塊，出現2行記錄。
--//session 3
SCOTT@book> select CURRENT_SCN,dept.* from dept,v$database where dept.rowid='AAAVRCAAEAAAACHAAA';
CURRENT_SCN DEPTNO DNAME LOC
------------ ------------ -------------- -------------
13309691673 10 ACCOUNTING NEW YORK
--//13309691673 = scn_wrap,scn_base(10): 3,424789785 = scn_wrap,scn_base(16): 0x3,0x1951c719
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424789783 3 3 620 18543 0000000077012000 DEPT
0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT
0000000084D25320 4 135 1 data block xcur 0 0 0 0 0 0 00000000748DE000 DEPT
--//3,424789783 = scn(10): 13309691671 = scn(16): 0x31951c717
--//透過訪問v$database的CURRENT_SCN欄位，可以發現增加2.注：訪問v$database檢視的CURRENT_SCN每次訪問都會遞增1.大家可以自行測試。
7.補充測試：
--//session 1,注意我的會話中的事務沒有提交。
SCOTT@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.4.82650
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 4 82650; 10 4 82650 3 620 18543 1 ACTIVE 1 1 0A000400DA420100 0000000081CE1568 2020-12-31 09:00:14 3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 620;
SYS@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
System altered.
********************************************************************************
Undo Segment: _SYSSMU10_1197734989$ (10)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c0026c ext#: 2 blk#: 108 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c00111 length: 7
0x00c000c8 length: 8
0x00c00200 length: 128
0x00c00b00 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1609369220
Extent Number:1 Commit Time: 1609369220
Extent Number:2 Commit Time: 1609369220
Extent Number:3 Commit Time: 1609369220
TRN CTL:: seq: 0x486f chd: 0x0001 ctl: 0x001d inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c0026c.486f.01 scn: 0x0003.1951b4d0
--//0x0003.1951b4d0 = scn(10): 13309686992 = scn(16): 0x31951b4d0
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.486f.26 ext: 0x2 spc: 0x2f6
uba: 0x00000000.486f.49 ext: 0x2 spc: 0x420
uba: 0x00000000.486f.03 ext: 0x2 spc: 0x1760
uba: 0x00000000.2ec6.04 ext: 0x2 spc: 0x1e02
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x142e6 0x000e 0x0003.1951b61e 0x00c00263 0x0000.000.00000000 0x00000003 0x00000000 1609375975
0x01 9 0x00 0x142f3 0x0011 0x0003.1951b4d6 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375949
0x02 9 0x00 0x142fd 0x0009 0x0003.1951b545 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375975
0x03 9 0x00 0x142ed 0x0000 0x0003.1951b566 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375975
0x04 10 0x80 0x142da 0x0002 0x0003.1951b736 0x00c0026c 0x0000.000.00000000 0x00000001 0x00000000 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x05 9 0x00 0x142f0 0x001a 0x0003.1951b759 0x00c0026b 0x0000.000.00000000 0x00000001 0x00000000 1609376224
....
0x20 9 0x00 0x142dd 0x0007 0x0003.1951b656 0x00c00269 0x0000.000.00000000 0x00000003 0x00000000 1609375975
0x21 9 0x00 0x13d82 0x0016 0x0003.1951b4de 0x00c00261 0x0000.000.00000000 0x00000001 0x00000000 1609375949
--// 0x00c0026c= set dba 3,620 = alter system dump datefile 3 block 620 = 12583532
--// 0x3.1951b736 = scn(10): 13309687606 = scn(16): 0x31951b736
--// 13309687606 = scn_wrap,scn_base(10): 3,424785718 = scn_wrap,scn_base(16): 0x3,0x1951b736
SYS@book> SELECT * FROM x$bh WHERE dbarfil=4 AND dbablk = 135 and state=1
2 @ prxx
==============================
ADDR : 00007F7120754080
INDX : 7139
INST_ID : 1
HLADDR : 0000000084D25320
BLSIZ : 8192
NXT_HASH : 0000000084D260C0
PRV_HASH : 00000000743F6BA8
NXT_REPL : 00000000844F5630
PRV_REPL : 00000000787DD128
FLAG : 33554433
FLAG2 : 0
LOBID : 0
RFLAG : 0
SFLAG : 0
LRU_FLAG : 0
TS# : 4
FILE# : 4
DBARFIL : 4
DBABLK : 135
CLASS : 1
STATE : 1
MODE_HELD : 0
CHANGES : 1
CSTATE : 0
LE_ADDR : 00
DIRTY_QUEUE : 0
SET_DS : 00000000844F5148
OBJ : 87106
BA : 00000000748DE000
CR_SCN_BAS : 0
CR_SCN_WRP : 0
CR_XID_USN : 0
CR_XID_SLT : 0
CR_XID_SQN : 0
CR_UBA_FIL : 0
CR_UBA_BLK : 0
CR_UBA_SEQ : 0
CR_UBA_REC : 0
CR_SFL : 0
CR_CLS_BAS : 0
CR_CLS_WRP : 0
LRBA_SEQ : 1891
LRBA_BNO : 10967
HSCN_BAS : 424789783
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~僅僅記錄訪問的最高scn號。
HSCN_WRP : 3
HSUB_SCN : 1
US_NXT : 0000000074BDE648
US_PRV : 0000000074BDE648
WA_NXT : 0000000074BDE658
WA_PRV : 0000000074BDE658
OQ_NXT : 000000007D281650
OQ_PRV : 000000007D281650
AQ_NXT : 000000007D281630
AQ_PRV : 000000007D281630
OBJ_FLAG : 242
TCH : 0
TIM : 0
CR_RFCNT : 0
SHR_RFCNT : 0
PL/SQL procedure successfully completed.
--//提交呢？
--//session 1:
SCOTT@book> commit ;
Commit complete.
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424791676 3 3 620 18543 0000000071C3A000 DEPT
0000000084D25320 4 135 1 data block cr 1 424789783 3 3 620 18543 0000000077012000 DEPT
0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT
0000000084D25320 4 135 1 data block xcur 0 0 0 0 0 0 00000000748DE000 DEPT
--//注：在提交前我在session 3多執行1次查詢，確定state=1的HSCN_BAS是否記錄查詢時最高scn。
--//session 3, 再次查詢呢？
SCOTT@book> select CURRENT_SCN,dept.* from dept,v$database where dept.rowid='AAAVRCAAEAAAACHAAA';
CURRENT_SCN DEPTNO DNAME LOC
------------ ------------ -------------- -------------
13309693848 10 ACCOUNTING NEW YORK
--//session 2:
SYS@book> @ bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
0000000084D25320 4 135 1 data block cr 1 424791676 3 3 620 18543 0000000071C3A000 DEPT
0000000084D25320 4 135 1 data block cr 1 424789783 3 3 620 18543 0000000077012000 DEPT
0000000084D25320 4 135 1 data block cr 1 424789613 3 3 620 18543 000000007435C000 DEPT
0000000084D25320 4 135 1 data block xcur 1 0 0 0 0 0 00000000748DE000 DEPT
--//注意看訪問的是stare=XCUR的塊，TCH從0->1.
SYS@book> SELECT HSCN_BAS,HSCN_WRP FROM x$bh WHERE dbarfil=4 AND dbablk = 135 and state=1;
HSCN_BAS HSCN_WRP
------------ ------------
424791721 3
--//可以確定這個scn就是提交時的scn號。
SYS@book> alter system checkpoint;
System altered.
--//透過bbed觀察,可以確定。
BBED> p /d kcbh dba 4,135
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 6
ub1 frmt_kcbh @1 162
ub1 spare1_kcbh @2 0
ub1 spare2_kcbh @3 0
ub4 rdba_kcbh @4 16777351
ub4 bas_kcbh @8 424791721
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub2 wrp_kcbh @12 3
ub1 seq_kcbh @14 1
ub1 flg_kcbh @15 6 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 37837
ub2 spare3_kcbh @18 0
--// 16777351 = set dba 4,135 = alter system dump datefile 4 block 135 = 0x1000087
總結：
1.測試有點亂，不過如果按照測試做，許多概念還是可以理解清楚的。
2.希望這個測試對與大家理解oracle的基本概念有用。
3.昨晚查了一些資料，發現：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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2746757/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210104]單例項data buffer states 2.txt單例
- Oracle Data BufferOracle
- 單例項刪除ASM例項單例ASM
- 單例與單例項之爭單例
- websocket簡單例項Web單例
- RAC 例項 遷移到 單例項 -- 使用RMAN Duplicate單例
- 11GR2 Active Duplicate過程(單例項對單例項)單例
- JavaScript單例模式概念與例項JavaScript單例模式
- 單例模式 - 只有一個例項單例模式
- PHP 完整表單例項PHP單例
- 5.6單例項安裝單例
- RAC起單例項make單例
- oracle單例項轉RACOracle單例
- data buffer cache的一點總結。
- tf.data.Dataset.shuffle(buffer_size)中buffer_size的理解
- js選項卡簡單程式碼例項JS
- opengl簡單入門例項
- RAC+DG(asm單例項)ASM單例
- rac恢復到單例項單例
- Vue單檔案模板例項Vue
- javascript事件冒泡簡單例項JavaScript事件單例
- Spark 簡單例項（基本操作）Spark單例
- mysql 5.7單例項安裝MySql單例
- javascript this用法和簡單例項JavaScript單例
- 搭建RAC到單例項DG單例
- 單例項恢復至RAC單例
- zabbix監控單例項redis單例Redis
- 單例項的duplicate(non ASM)單例ASM
- 從Data Buffer中讀資料是單塊讀，還是多塊讀？
- jQuery data-*賦值或者獲取值程式碼例項jQuery賦值
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項OracleLinux
- 使用點陣圖選單項——點陣圖選單項例項 (轉)
- 單例項和單點故障怎麼辦？單例
- data buffer cache的一點總結 -- 轉
- [20201231]RAC連線特定例項.txt
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項-3OracleLinux
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項 -2OracleLinux
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項 -1OracleLinux