[20150913]檔案檢查點_熱備份.txt

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