分享程序员开发的那些事...
更新时间：2022-08-22 16:41:16
oracle rba相当重要,与oracle恢复及redo内容,检查点进程工作机制,
有密不可分的关系与联系；理解它的构成,是进一步深入学习oracle
必备路途
摘贴网上基于rba的文章：
http://blog.csdn.net/tianlesoftware/article/details/6700080
http://space.itpub.net/9240380/viewspace-751510
http://www.doc88.com/p-331776598706.html ---非常好，通俗易懂
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