分享程序员开发的那些事...
更新时间：2022-08-23 14:16:21
[20150913]文件检查点_热备份.txt
--oracle从8i以后基本使用增量检查点取代原来的检查点模式,但是如果一个表空间处于热备份模式以及offline时，要将这些表空间包含
--的数据文件所涉及到的脏块写数据文件.这个叫file level Checkpoint(感觉叫tablespace level checkpoint更加合适一些),如何验证
--这个过程,实际上很简单仅仅需要检查v$bh或者x$bh视图,或者转储脏块看看里面的内容是否存在变化,来验证这个过程,还是通过例子来
--说明:
--我个人喜欢使用bbed观察，效果与块转储一直。
1.运行环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> show parameter alert
NAME TYPE VALUE
------------------------- -------- --------
log_checkpoints_to_alert boolean TRUE
--设置log_checkpoints_to_alert=true,主要是为了测试需要，如果存在full checkpoint以及增量检查会写alert日志文件。
alter system set log_checkpoint_timeout=3600 scope=memory;
--设置的目的主要延长增量检查点的时间间隔(我的测试机器业务很小，我仅仅自己使用)，这样便于测试，参考链接：
--http://blog.itpub.net/267265/viewspace-1796999/
create table ta tablespace test as select 1 id1 ,cast('cccc' as varchar2(20)) name from dual ;
create table tb tablespace users as select 2 id1 ,cast('dddd' as varchar2(20)) name from dual ;
SCOTT@test> select rowid,ta.* from ta;
ROWID ID1 NAME
------------------ ---------- --------------------
AABMyRAAIAAAACDAAA 1 cccc
SCOTT@test> @rowid AABMyRAAIAAAACDAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
314513 8 131 0 8,131 alter system dump datafile 8 block 131 ;
SCOTT@test> select rowid,tb.* from tb;
ROWID ID1 NAME
------------------ ---------- --------------------
AABMySAAEAAAAIzAAA 2 dddd
SCOTT@test> @rowid AABMySAAEAAAAIzAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
314514 4 563 0 4,563 alter system dump datafile 4 block 563 ;
alter system checkpoint;
--查看alert日志:
Mon Sep 14 08:29:02 2015
Beginning global checkpoint up to RBA [0xe2f.ba90.10], SCN: 13201301276
Completed checkpoint up to RBA [0xe2f.ba90.10], SCN: 13201301276
2.测试热备份模式：
--session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
206 625 13755 alter system kill session '206,625' immediate;
SCOTT@test> @bh 8 131
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9BF878 8 131 1 data block xcur 1 0 0 0 0 0 000000008F5BA000 TA
SCOTT@test> @bh 4 563
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9B2358 4 563 1 data block xcur 1 0 0 0 0 0 000000009CB96000 TB
update ta set name=upper(name) where id1=1;
update tb set name=upper(name) where id1=2;
--不提交。
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
14 67 15274 alter system kill session '14,67' immediate;
SCOTT@test> select * from V$SESSION_EVENT where sid=14;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
14 Disk file operations I/O 1 0 0 .04 0 385 166678035 1740759767 8 User I/O
14 log file sync 1 0 2 2.09 2 20927 1328744198 3386400367 5 Commit
14 db file sequential read 5 0 2 .47 1 23321 2652584166 1740759767 8 User I/O
14 SQL*Net message to client 16 0 0 0 0 73 2067390145 2000153315 7 Network
14 SQL*Net message from client 15 0 4371 291.4 4177 43710526 1421975091 2723168908 6 Idle
SCOTT@test> alter tablespace test begin backup;
Tablespace altered.
SCOTT@test> select * from V$SESSION_EVENT where sid=14;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
14 Disk file operations I/O 4 0 0 .03 0 1239 166678035 1740759767 8 User I/O
14 control file sequential read 24 0 0 0 0 355 3213517201 4108307767 9 System I/O
14 control file parallel write 7 0 8 1.12 1 78313 4078387448 4108307767 9 System I/O
14 log file sync 4 0 4 1.06 2 42202 1328744198 3386400367 5 Commit
14 db file sequential read 8 0 2 .29 1 23355 2652584166 1740759767 8 User I/O
14 db file single write 2 0 2 .9 1 17964 1307477558 1740759767 8 User I/O
14 SQL*Net message to client 21 0 0 0 0 102 2067390145 2000153315 7 Network
14 SQL*Net message from client 20 0 8831 441.55 4177 88309144 1421975091 2723168908 6 Idle
14 SQL*Net break/reset to client 4 0 0 .01 0 252 1963888671 4217450380 1 Application
14 events in waitclass Other 5 0 3 .65 2 32610 1736664284 1893977003 0 Other
10 rows selected.
--session 1:
SCOTT@test> @bh 8 131
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9BF878 8 131 1 data block xcur 1 0 0 0 0 0 00000000A20D2000 TA
00000000BC9BF878 8 131 1 data block cr 1 316399473 3 0 0 0 000000008F5BA000 TA
SCOTT@test> @bh 4 563
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC9B2358 4 563 1 data block xcur 1 0 0 0 0 0 000000009B390000 TB
00000000BC9B2358 4 563 1 data block cr 1 316399474 3 0 0 0 000000009CB96000 TB
3.bbeg观察：
BBED> set dba 8,131
DBA 0x02000083 (33554563 8,131)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: CCCC
BBED> set dba 4,563
DBA 0x01000233 (16777779 4,563)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 2
col 1[4] @8183: dddd
--可以发现dba=8，131的数据块记录的name是大写，表示已经写盘。而dba=4,563的数据块记录的name是小写，没有写盘。
4.继续测试:
--session 1 :
rollback ;
alter tablespace test end backup;
--bbed观察：
BBED> set dba 8,131
DBA 0x02000083 (33554563 8,131)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: CCCC
BBED> set dba 4,563
DBA 0x01000233 (16777779 4,563)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 2
col 1[4] @8183: dddd
--可以确定结束热备份并不会发出file level Checkpoint.