oracle rba一些小知識

　oracle rba相當重要,與oracle恢復及redo內容,檢查點程式工作機制,
有密不可分的關係與聯絡；理解它的構成,是進一步深入學習oracle
必備路途

摘貼網上基於rba的文章：
http://blog.csdn.net/tianlesoftware/article/details/6700080
http://space.itpub.net/9240380/viewspace-751510
---非常好，通俗易懂
RBA 由以下三部分組成：

       （1）the log file sequence number (4 bytes)

       （2）the log file block number (4 bytes)

       （3）the byte offset into the block at which the redo record starts (2bytes)
      
由上可知：
   1,rba與checkpoint queue關係很大，即與dbwr關係很大。dbwr每次在dirty buffer要把哪些buffer寫入到datefile中
   2,rba與lgwr也有很大的。
   3,rba與恢復也有關係.on-disk rb即例項恢復到這個rba，庫就可以執行了
   4,各種不同rba的獲取方式
   5,rba與controlfile

在上面提到的幾種RBA 值可以透過X$BH,和 X$KCCRT 檢視進行檢視：

       1,The low and high RBAs for dirty buffers can be seen in X$BH.(There is also a recovery RBA which is used to record the
         progress ofpartial block recovery by PMON.)

       2,The incremental checkpoint RBA, the target RBA and theon-disk RBA can all be seen in X$TARGETRBA.
       3,The incremental checkpointRBA and the on-disk RBA can also be seen in X$KCCCP.

       4,The full thread checkpoint RBA can be seen in X$KCCRT.
         
         
先看第1種方式：
　　   1,The low and high RBAs for dirty buffers can be seen in X$BH.(There is also a recovery RBA which is used to record the
         progress ofpartial block recovery by PMON.)
　　　　
　　　我的測試方法：
　　　　　1,構建測試表並插入一條記錄，但不提交
          2,檢視x$bh確認其rba相關資訊
          3,dump redo rba相關資訊

/*************測試開始***************************/ 
-----------會話１－－－－
SQL> insert into t_rba values(1)
  2  ;

1 row created.

SQL>
SQL>
SQL> select dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid) from t_rba;

DBMS_ROWID.ROWID_OBJECT(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------ ------------------------------------
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                         70184                                   10
                              276646
                             
                             
---會話2-----------
--------------檢視dirty buffer資訊
  --你發現cr相關的列全是0因為此時不需要構建cr塊                                
SQL> select TS#,FILE#,DBARFIL,DBABLK,CHANGES,OBJ,BA,CR_SCN_BAS,CR_SCN_WRP, CR_XID_USN ,CR_XID_SLT, CR_XID_SQN, CR_UBA_FIL ,CR_UBA_BLK, CR_UBA_SEQ ,CR_UBA_REC
  2  from x$bh where bj=(select object_id from dba_objects where wner='SCOTT' and object_name='T_RBA');
 
       TS#      FILE#    DBARFIL     DBABLK    CHANGES        OBJ BA               CR_SCN_BAS CR_SCN_WRP CR_XID_USN CR_XID_SLT CR_XID_SQN CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC
---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         8         10         10     276645          1      70184 000007FF01372000          0          0          0          0          0          0          0          0          0
         8         10         10     276642          1      70184 000007FF0135C000          0          0          0          0          0          0          0          0          0
         8         10         10     276647          1      70184 000007FF01E06000          0          0          0          0          0          0          0          0          0
         8         10         10     276644          1      70184 000007FF0123A000          0          0          0          0          0          0          0          0          0
         8         10         10     276641          1      70184 000007FF00A7E000          0          0          0          0          0          0          0          0          0
         8         10         10     276646          1      70184 000007FF00D4E000          0          0          0          0          0          0          0          0          0
         8         10         10     276643          1      70184 000007FF01DFE000          0          0          0          0          0          0          0          0          0
         8         10         10     276640          1      70184 000007FF01DFA000          0          0          0          0          0          0          0          0          0
 
8 rows selected

--我們別啟一個會話,執行select * from t_rba;
--cr列有資料了,而且與v$transaction聯絡起來了,CR_SCN_BAS即cr塊構建自這個scn開始構建
SQL> select TS#,FILE#,DBARFIL,DBABLK,CHANGES,OBJ,BA,CR_SCN_BAS,CR_SCN_WRP, CR_XID_USN ,CR_XID_SLT, CR_XID_SQN, CR_UBA_FIL ,CR_UBA_BLK, CR_UBA_SEQ ,CR_UBA_REC
  2  from x$bh where bj=(select object_id from dba_objects where wner='SCOTT' and object_name='T_RBA')
  3  ;
 
       TS#      FILE#    DBARFIL     DBABLK    CHANGES        OBJ BA               CR_SCN_BAS CR_SCN_WRP CR_XID_USN CR_XID_SLT CR_XID_SQN CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC
---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         8         10         10     276645          1      70184 000007FF01372000          0          0          0          0          0          0          0          0          0
         8         10         10     276642          1      70184 000007FF0135C000          0          0          0          0          0          0          0          0          0
         8         10         10     276647          1      70184 000007FF01E06000          0          0          0          0          0          0          0          0          0
         8         10         10     276644          1      70184 000007FF0123A000          0          0          0          0          0          0          0          0          0
         8         10         10     276646          1      70184 000007FF02F26000   10307069          0         72         15        134          3       7610        416         14
         8         10         10     276646          1      70184 000007FF02108000   10307068          0         72         15        134          3       7610        416         14
         8         10         10     276646          1      70184 000007FF00D4E000          0          0          0          0          0          0          0          0          0
         8         10         10     276643          1      70184 000007FF01DFE000          0          0          0          0          0          0          0          0          0
 
8 rows selected

/********轉儲data block***********/

---與上述的ba 000007FF00D4E000相對應,而這個ba 000007FF02108000是為了上述的select構造的cr block,它的cr_scn_bas即下述的LSCN
---HSCN即最新的scn,所以說cr block是根據current block構造的一個資料塊
BH (0x000007FF00FE4538) file#: 10 rdba: 0x028438a6 (10/276646) class: 1 ba: 0x000007FF00D4E000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  dbwrid: 0 obj: 70184 objn: 70184 tsn: 8 afn: 10 hint: f
  hash: [0x000007FF0C325E88,0x000007FF023E1C58] lru: [0x000007FF00BEF710,0x000007FF013DACF0]
  lru-flags: hot_buffer
  obj-flags: object_ckpt_list
  ckptq: [0x000007FF0C3F8AA8,0x000007FF0C3F8AA8] fileq: [0x000007FF0C3F8BE8,0x000007FF0C3F8BE8] objq: [0x000007FF0783B2F8,0x000007FF0783B2F8]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty block_written_once redo_since_read
  LRBA: [0x31d.69b8.0] LSCN: [0x0.9d45fc] HSCN: [0x0.9d4661] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

----------轉儲undo block-------------------------
*-----------------------------
* Rec #0xe  slt: 0x0f  objn: 70184(0x00011228)  objd: 70184  tblspc: 8(0x00000008)
*       Layer:  11 (Row)   opc: 1   rci 0x00   --rci可以判斷是否還往前回溯構造cr block(適用對一個記錄或多記錄update多次未提交),此地rci為0,不用再往上cr了
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c01dba.01a0.0d ctl max scn: 0x0000.009cbdf1 prv tx scn: 0x0000.009cbdf2
txn start scn: scn: 0x0000.009d36ed logon user: 61　
 prev brb: 12595255 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x028438a6  hdba: 0x028438a2
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) 

---同上--------------
SQL> select CR_SFL ,CR_CLS_BAS, CR_CLS_WRP,   LRBA_SEQ ,  LRBA_BNO ,  HSCN_BAS ,  HSCN_WRP,   HSUB_SCN
     from x$bh where bj=(select object_id from dba_objects where wner='SCOTT' and object_name='T_RBA');
 
    CR_SFL CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          0          0          0          0 4294967295      65535          1
         0          0          0          0          0 4294967295      65535          1
         0          0          0          0          0 4294967295      65535          1
         0          0          0          0          0 4294967295      65535          1
         0          0          0          0          0 4294967295      65535          1
         0          0          0          0          0 4294967295      65535          2
         0          0          0          0          0 4294967295      65535          1
         0          0          0          0          0 4294967295      65535          1
 
8 rows selected                   
---查詢事務----------
SQL> select * from v$transaction;
 
ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS           START_TIME           START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR               FLAG SPACE RECURSIVE NOUNDO PTX NAME         PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DATE   DSCN_BASE  DSCN_WRAP  START_SCN DEPENDENT_SCN XID              PRV_XID          PTX_XID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- ----- --------- ------ --- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------
000007FF0B146238         72         15        134          3       7610        416         14 ACTIVE           03/24/13 13:34:35      10303213          0          0            3         7610          416           14 000007FF0C86B270       7683 NO    NO        NO     NO                  0          0          0          0          0          0          0          0          1          1         46          0          2          0 2013/3/24 1          0          0   10303213             0 48000F0086000000 0000000000000000 0000000000000000
 
----檢視當前redo
SQL> select * from v$log where status='CURRENT';
 
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
         7          1        797  209715200        512          1 NO       CURRENT               10299796 2013/3/24 1 281474976710
 
 
SQL>  select * from v$logfile where group#=( select group# from v$log where status='CURRENT');
 
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         7         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO07.LOG                                       NO
        
---看看lrba的狀態變化 

---如果你未在其它會話cr或者dml，看不到lrba     
SQL> select ba,obj,lrba_seq,lrba_bno from x$bh where bj=(select object_id from dba_objects where wner='SCOTT' and object_name='T_RBA');  
BA                      OBJ   LRBA_SEQ   LRBA_BNO
---------------- ---------- ---------- ----------
000007FF01372000      70184          0          0
000007FF0135C000      70184          0          0
000007FF01E06000      70184          0          0
000007FF0123A000      70184          0          0
000007FF02106000      70184          0          0
000007FF01DFE000      70184          0          0
 
6 rows selected     

---如何看到lrba呢，在另一會話dml,再回到上述會話，

SQL> /
 
BA                      OBJ   LRBA_SEQ   LRBA_BNO
---------------- ---------- ---------- ----------
000007FF01372000      70184          0          0
000007FF0135C000      70184          0          0
000007FF01E06000      70184          0          0
000007FF0123A000      70184          0          0
000007FF02CBE000      70184        798      35439 即對應 v$log.sequence#
000007FF02CC4000      70184          0          0
000007FF01DFE000      70184          0          0
 
7 rows selected
  
   小結：可看到lrba，但未在x$bh看到hrba
 
我們看看第2種方式：  
2,The incremental checkpoint RBA, the target RBA and the on-disk RBA can all be seen in X$TARGETRBA.  

最後小結一下：
1,rba由三部分組成：
     seqno  序列號 4byte
     blockno 塊號 4byte
     startbyte 起始（或叫偏移量)位元組　2個位元組
    
2,lrba即資料塊第一次變更時在redo logfile中的rba
  引：ckpt-q程式即檢查點程式根據lrba的升序維護log buffer
      ckpt-q程式會按lbra升序的方式從最小的lrba寫出這些dirty buffer
     
  哪麼ckpt-q寫出的lrba就是checkpoint 的位置.
  從這個位置lrba一直應用到redo file的on disk rba，這個過程就是
  前滾（例項恢復）     
 
3,說了lrba,自然少不了hrba,它就是對資料塊最近一次變更在redo
   logfile中的rba
  (注：如果hrba大於on-disk rba，那麼hrba對應的redo entries還在
       log buffer,這樣一旦資料庫掛掉，哈哈，hrba就不能被恢復了)
       所以只能從lrba恢復到on-disk rba,別急：on-disk rba在後面說說)

4,checkpoint rba，這個上面已經提過了，就是ckpt-q程式要寫入的髒資料塊的
　lrba,而這lrba之前的髒資料說明已全部寫入disk;
  換句話說，ckpt就是每次寫入上次checkpoint rba到這次checkpoint rba的資料到磁碟的
 
  還有個問題：ckpt程式每次要從哪個rba開始寫入到磁碟呢，其實啊，它工作時會記錄
  要寫的第一個rba,這個rba就是checkpoint rba,這樣每次就會有針對性的去寫了。其實就是
  個限制寫入的範圍
 
5,再來說下on-disk rba,它就是lgwr由log buffer寫到log file最後面的一個rba;
  也就是例項恢復前滾的終點（自lrba到on-disk rba）  
 
 
6,target rba，是不是有些暈，哈哈；這個其實是checkpoint事件結束的rba
  也就是說：從ckpt開始產生檢查點事件，自checkpoint rba開始寫入到終止寫入的哪個
  checkpoint rba;
 
  oracle為了提升恢復的效能和時間（例項恢復）,dbwr程式會盡力把checkpoint rba設定長一點；
  （原因很簡單：這樣每次寫入到磁碟多一些；恢復起來就快得多了） 
  它與引數:fast_start_mttr_target有關
 
 
7,相關檢視：
　　　1,增量檢查點checkpoint rba及on-disk rba
      select * from x$kcccp;
      注：cpdrt列是檢查點佇列中髒塊數量
      　　cpods列是on disk rba的scn
          cpodt列是on disk rba的時間戳
          cphbt列是心跳
　　　2,lrba及hrba
         select lrba_seq,lrba_bno from x$bh;
        
         select hxfil,fhrba_seq,fhrba_bno,fhrba_bof from x$kcvfh;--x$kcvfh即v$datafile_header的基表
        
　　　3,檢視target rba
          select target_rba_seq,target_rba_bno,target_rba_bof from x$targetrba;
   
      4,完全檢查點checkpoint rba
         select rtckp_rba_seq,rtckp_rba,rtckp_rba_bof from x$kccrt;
        

8,再總結下：
　　　1,redo和checkpoint關係很大
      2,配置log_checkpoint_to_alert=true;checkpoint會寫入到alert
      3,資料檔案頭包括checkpoint_change#，
      　更重要還包括了checkpoint_change#所在logfile的sequence#,這樣恢復就知道要用哪個archive log或log