oracle redo和undo系列一

wisdomone1發表於2013-03-22

oracle undo與redo系列(一)

1,測試目的:
     1,進一步理解undo和redo相關概念
     2,把undo和redo統一聯絡起來;
2,準備工作
   1,轉儲日誌檔案的命令:
                           alter system dump logfile '處於current狀態的日誌檔案組名稱';
      2,日誌檔案的構成要素:
                           重作記錄
                           重作向量
                           重作記錄與重作向量的關係
   2,轉儲undo segment block的命令:
                           alter system dump datafile x block y;
        或者:
        --由v$rollstat之usn關聯v$transaction獲取
        alter system dump undo header '事務所佔用的回滾段';
      3,轉儲undo block命令:
                           --x,y由undo header block可知或v$transaction可知
                           alter system dump datafile x block y;
3,開始測試
     /*****************************構建測試表*******************/
SQL> create table t_redo(a int);
 
Table created
 
SQL> select object_id from dba_objects where object_name='T_REDO';
 
 OBJECT_ID
----------
     70020

/***********10046 trace,為了觀察到單塊讀,flush buffer_cache************************/
SQL> alter session set events '10046 trace name context forever,level 12';
 
Session altered
 
SQL> insert into t_redo values(1);
 
1 row inserted
 
SQL> alter session set events '10046 trace name context off';
 
Session altered

/******************檢視生成的trace檔案*****************************/
SQL> select * from v$diag_info where name like '%Default Trace File%';
 
   INST_ID NAME                                                             VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Default Trace File                                               d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace\orcl_s001_4500.trc

/*******************生成的trace檔案**********************************/
WAIT #1: nam='db file sequential read' ela= 527 file#=10 block#=276633 blocks=1 obj#=70020 tim=126724965619
WAIT #1: nam='db file sequential read' ela= 26638 file#=3 block#=139965 blocks=1 obj#=0 tim=126724992359

/*********************檢視錶的物件號,檔案號,資料塊號********************/
SQL> select dbms_rowid.rowid_object(rowid) as object_id,dbms_rowid.rowid_relative_fno(rowid) as file_id,dbms_rowid.rowid_block_number(rowid) as block_id from t_redo;
 
 OBJECT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
     70020         10     276638

/*********************檢視錶的段頭塊*********************/    
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='T_REDO';
 
SEGMENT_NAME                                                                     HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------- ----------- ------------
T_REDO                                                                                    10       276634 

/***************事務所涉及的回滾段*********************/
SQL> select * from v$rollname where  usn=( select xidusn from v$transaction);
 
       USN NAME
---------- ------------------------------
        51 _SYSSMU51_1137245832$

/**************事務所涉及的撤消檔案編號及撤消塊編號*******從trace知道第二次讀了此塊****/       
SQL> select ubafil,ubablk from v$transaction;
 
    UBAFIL     UBABLK
---------- ----------
         3     139965

/*************表分配的區相關情況**********從trace先讀取了表的第2個塊***/
SQL> select segment_name,extent_id,block_id,blocks from dba_extents where segment_name='T_REDO';
 
SEGMENT_NAME                                                                      EXTENT_ID   BLOCK_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ---------- ----------
T_REDO                                                                                    0     276632          8


        
/**********************開始依次dump上述10046的的塊******************************/
WAIT #1: nam='db file sequential read' ela= 527 file#=10 block#=276633 blocks=1 obj#=70020 tim=126724965619

/****************alter system dump datafile 10 block 276633**************************/
--可知先讀取是二級點陣圖管理塊
frmt: 0x02 chkval: 0xac2c type: 0x21=SECOND LEVEL BITMAP BLOCK

Dump of Second Level Bitmap Block
  /***********下述的pdba經轉換即段頭塊地址(select to_number('00284389a','xxxxxxxxxxx') from dual;*******************
   select dbms_utility.data_block_address_file(42219674),dbms_utility.data_block_address_block(42219674) from dual;
   ***************************************************************************/
   number: 1       nfree: 1       ffree: 0      pdba:     0x0284389a  
   Inc #: 0 Objd: 70020
  opcode:0
 xid:
  L1 Ranges :
  --------------------------------------------------------
   0x02843898  Free: 5 Inst: 1  --這個free我理解是一個區extent未使用過的塊個數
   (小結:說明oracle其實先是讀取了header block,然後才讀取level 2 bitmap block)
  
 dbwrid: 0 obj: 70020 objn: 70020 tsn: 8 afn: 10 hint: f --hint即v$bh的cachehint即在buffer cache命中的次數
 
/***************繼續dump10046第二個單塊讀******************/
/*************WAIT #1: nam='db file sequential read' ela= 26638 file#=3 block#=139965 blocks=1 obj#=0 tim=126724992359************/
/*****************alter system dump 3 block     139965;******************/

SQL> select name from v$rollname where usn=(select xidusn from v$transaction);
 
NAME
------------------------------
_SYSSMU51_1137245832$

SQL> select segment_name,owner,tablespace_name,segment_id,file_id,block_id from dba_rollback_segs where segment_name='_SYSSMU51_1137245832$';
 
SEGMENT_NAME                   OWNER  TABLESPACE_NAME                SEGMENT_ID    FILE_ID   BLOCK_ID
------------------------------ ------ ------------------------------ ---------- ---------- ----------
_SYSSMU51_1137245832$          PUBLIC UNDOTBS1                               51          3       7104


/*******看來第二個單塊讀不是讀取的事務回滾段的段頭塊**************/
/************不管怎麼樣我們先trace下事務讀取的回滾塊****************/
alter system dump datafile 3 block 139965;

scn: 0x0000.00922f1d seq: 0x01 flg: 0x04 tail: 0x2f1d0201
frmt: 0x02 chkval: 0x8be0 type: 0x02=KTU UNDO BLOCK --可知是具體的undo block,而非undo header block

UNDO BLK: 
xid: 0x0033.01a.00000085  seq: 0x17a cnt: 0x21  irb: 0x21  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f18     0x02 0x1e6c     0x03 0x1dc4     0x04 0x1d0c     0x05 0x1cac    
0x06 0x1c5c     0x07 0x1bb0     0x08 0x1b08     0x09 0x1a78     0x0a 0x19d0    
0x0b 0x1928     0x0c 0x1880     0x0d 0x17fc     0x0e 0x17a4     0x0f 0x1738    
0x10 0x168c     0x11 0x15e0     0x12 0x1548     0x13 0x14c0     0x14 0x1450    
0x15 0x13a4     0x16 0x12ac     0x17 0x1204     0x18 0x1158     0x19 0x10d0    
0x1a 0x1060     0x1b 0x0fb8     0x1c 0x0f0c     0x1d 0x0ea0     0x1e 0x0e38    
0x1f 0x0dc0     0x20 0x0d58     0x21 0x0cec


*-----------------------------
* Rec #0x21  slt: 0x1a  objn: 70020(0x00011184)  objd: 70020  tblspc: 8(0x00000008)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c222bd.017a.1c ctl max scn: 0x0000.00915f03 prv tx scn: 0x0000.00916782 --之前事務的scn
txn start scn: scn: 0x0000.00922f1d logon user: 61 --事務開始的scn
 prev brb: 12682835 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: 0x0284389e  hdba: 0x0284389a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) --因為這裡是insert未提交,故無前映象

/************************再trace下undo header block******************************/
共同2種方式
     alter system dump undo header '_SYSSMU51_1137245832$';
     alter system dump datafile 3 block 7104;

 /*********先看第一種方式結果*************/
*** 2013-03-22 21:23:04.903
*** SESSION ID:(157.203) 2013-03-22 21:23:04.903
 
********************************************************************************
Undo Segment:  _SYSSMU51_1137245832$ (51) ---undo header block名稱
********************************************************************************
  Extent Control Header 
/****此sql參考查詢回滾段的區分配情況************/
SQL> select owner,segment_name,segment_type,tablespace_name,extent_id,block_id,blocks from dba_extents where segment_name='_SYSSMU51_1137245832$';
 
OWNER                          SEGMENT_NAME                                                                     SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID   BLOCK_ID     BLOCKS
------------------------------ -------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ---------- ----------
SYS                            _SYSSMU51_1137245832$                                                            TYPE2 UNDO         UNDOTBS1                                0       7104          8
SYS                            _SYSSMU51_1137245832$                                                            TYPE2 UNDO         UNDOTBS1                                1        720          8
SYS                            _SYSSMU51_1137245832$                                                            TYPE2 UNDO         UNDOTBS1                                2     139904        128
  同上sql知回滾段共有3個區,共佔用資料塊個數143,還少一個資料塊
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 3      #blocks: 143  
                  last map  0x00000000  #maps: 0      offset: 4080 
      Highwater::  0x00c01bc2  ext#: 0      blk#: 1      ext size: 7    
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 0    
  mapblk  0x00000000  offset: 0    
                   Unlocked
     Map Header:: next  0x00000000  #extents: 3    obj#: 0      flag: 0x40000000

/**********同上理,查詢回滾段區分配******************/    
SQL> select block_id,to_char(block_id,'xxxxxxxxxxxxxxxxx'),blocks from dba_extents where segment_name='_SYSSMU51_1137245832$';
 
  BLOCK_ID TO_CHAR(BLOCK_ID,'XXXXXXXXXXXX     BLOCKS
---------- ------------------------------ ----------
      7104               1bc0                      8
       720                2d0                      8
    139904              22280                    12    
  Extent Map
  -----------------------------------------------------------------
   0x00c01bc1  length: 7     --和上述sql匹配,每個區的首塊;這個首塊為何加1,因為有個undo block header block佔用了
   0x00c002d0  length: 8    
   0x00c22280  length: 128  
 
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1363958134 --每個區extent commit時間
 Extent Number:1  Commit Time: 1363939587
 Extent Number:2  Commit Time: 1363958134
 
  TRN CTL:: seq: 0x017b chd: 0x0012 ctl: 0x0014 inc: 0x00000000 nfb: 0x0001
            mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00c01bc2.017b.01 scn: 0x0000.009171f4 --uba即具體的undo block,老方法轉算為具體的undo block,這樣undo header block就和undo block聯絡上了
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00c01bc2.017b.01 ext: 0x0  spc: 0x1f84 
    uba: 0x00000000.017a.43 ext: 0x7  spc: 0xaec  
    uba: 0x00000000.0170.08 ext: 0x8  spc: 0x356  
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    
 
 /********這就是大名鼎鼎的事務表*************其實就是一個表格一樣的東東*************/
 
 /***********************插入的查詢sql,查詢測試表的資料塊,經查即與如下事務表的dba 0x1a條目相對應,其state=10,表示未提交,9表示提交**********************************
 /********從事務表可看到對一個資料塊多次修改會形成多條條目*************/
 SQL> select to_number('00c222bd','xxxxxxxxxxxxxxxxxxxxx') from dual;
 
TO_NUMBER('00C222BD','XXXXXXXX
------------------------------
                      12722877
 
SQL> select dbms_utility.data_block_address_file(12722877),dbms_utility.data_block_address_block(12722877) from dual;
 
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                             3                         139965
                            
**************************************************************/                            
  TRN TBL::--縮略詞即transaction table就是事務表
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0087  0x0005  0x0000.0091e754  0x00c1ec6a  0x0000.000.00000000  0x00000053   0x00000000  1363940433
   0x01    9    0x00  0x0089  0x001f  0x0000.009206ad  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363941256
   0x02    9    0x00  0x0088  0x000e  0x0000.00921cd2  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363949100
   0x03    9    0x00  0x0087  0x000d  0x0000.0091a40b  0x00c19d76  0x0000.000.00000000  0x00000053   0x00000000  1363940321
   0x04    9    0x00  0x0088  0x0017  0x0000.009220f9  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363950607
   0x05    9    0x00  0x0087  0x001d  0x0000.0091ea63  0x00c1ec6a  0x0000.000.00000000  0x00000001   0x00000000  1363940437
   0x06    9    0x00  0x0087  0x0000  0x0000.0091d4b7  0x00c1ec18  0x0000.000.00000000  0x00000052   0x00000000  1363940406
   0x07    9    0x00  0x0088  0x000f  0x0000.009229e7  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363953616
   0x08    9    0x00  0x0089  0x0016  0x0000.009218f9  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363947658
   0x09    9    0x00  0x0086  0x0020  0x0000.00920aad  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363942735
   0x0a    9    0x00  0x0086  0x0019  0x0000.009213ba  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363945846
   0x0b    9    0x00  0x0086  0x0014  0x0000.009237eb  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1363958134
   0x0c    9    0x00  0x0088  0x0007  0x0000.0092282c  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363953012
   0x0d    9    0x00  0x0086  0x0006  0x0000.0091bb1d  0x00c1c8c7  0x0000.000.00000000  0x00000052   0x00000000  1363940372
   0x0e    9    0x00  0x0089  0x0004  0x0000.00921ec3  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363949896
   0x0f    9    0x00  0x0085  0x0018  0x0000.00922b35  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363953917
   0x10    9    0x00  0x0086  0x0003  0x0000.00918bfe  0x00c19d24  0x0000.000.00000000  0x00000001   0x00000000  1363940230
   0x11    9    0x00  0x0089  0x0021  0x0000.009224ee  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363951812
   0x12    9    0x00  0x0085  0x0010  0x0000.00917fac  0x00c19d24  0x0000.000.00000000  0x00000052   0x00000000  1363940201
   0x13    9    0x00  0x0081  0x0001  0x0000.009205c2  0x00c222bc  0x0000.000.00000000  0x00000002   0x00000000  1363941035
   0x14    9    0x00  0x0087  0xffff  0x0000.009237ee  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1363958134
   0x15    9    0x00  0x0086  0x001c  0x0000.00920e68  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363944036
   0x16    9    0x00  0x0086  0x0002  0x0000.009219d8  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363947954
   0x17    9    0x00  0x0089  0x0011  0x0000.009222c8  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363951203
   0x18    9    0x00  0x0089  0x000b  0x0000.00922d3a  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363954819
   0x19    9    0x00  0x0088  0x0008  0x0000.009216b6  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363947046
   0x1a   10    0x80  0x0085  0x0002  0x0000.00922f1d  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  0            ---這個就是測試表insert未提交在undo header block插入的事務表條目
   0x1b    9    0x00  0x0086  0x000a  0x0000.0092115f  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363944945
   0x1c    9    0x00  0x0087  0x001b  0x0000.009210a6  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363944647
   0x1d    9    0x00  0x0087  0x001e  0x0000.0091fddc  0x00c222bb  0x0000.000.00000000  0x00000052   0x00000000  1363940461
   0x1e    9    0x00  0x0087  0x0013  0x0000.009205c1  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363941035
   0x1f    9    0x00  0x0086  0x0009  0x0000.0092091b  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363942234
   0x20    9    0x00  0x0087  0x0015  0x0000.00920c42  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363943135
   0x21    9    0x00  0x0086  0x000c  0x0000.009225dd  0x00c222bd  0x0000.000.00000000  0x00000001   0x00000000  1363952111
 
  /********************擴充套件事務表***************/
  EXT TRN CTL::
  usn: 51 --這個好v$transaction中的usn,就是我們測試的事務
  sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000 --sp字首不知是什麼
  sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
  EXT TRN TBL::
  --我理解這個擴充套件事務表即是對事務表的增強,這個還要研究
  index  extflag    extHash    extSpare1   extSpare2
  ---------------------------------------------------
   0x00  0x00000000 0x00000000 0x00000000  0x00000000
   0x01  0x00000000 0x00000000 0x00000000  0x00000000
   0x02  0x00000000 0x00000000 0x00000000  0x00000000
   0x03  0x00000000 0x00000000 0x00000000  0x00000000
   0x04  0x00000000 0x00000000 0x00000000  0x00000000
   0x05  0x00000000 0x00000000 0x00000000  0x00000000
   0x06  0x00000000 0x00000000 0x00000000  0x00000000
   0x07  0x00000000 0x00000000 0x00000000  0x00000000
   0x08  0x00000000 0x00000000 0x00000000  0x00000000
   0x09  0x00000000 0x00000000 0x00000000  0x00000000
   0x0a  0x00000000 0x00000000 0x00000000  0x00000000
   0x0b  0x00000000 0x00000000 0x00000000  0x00000000
   0x0c  0x00000000 0x00000000 0x00000000  0x00000000
   0x0d  0x00000000 0x00000000 0x00000000  0x00000000
   0x0e  0x00000000 0x00000000 0x00000000  0x00000000
   0x0f  0x00000000 0x00000000 0x00000000  0x00000000
   0x10  0x00000000 0x00000000 0x00000000  0x00000000
   0x11  0x00000000 0x00000000 0x00000000  0x00000000
   0x12  0x00000000 0x00000000 0x00000000  0x00000000
   0x13  0x00000000 0x00000000 0x00000000  0x00000000
   0x14  0x00000000 0x00000000 0x00000000  0x00000000
   0x15  0x00000000 0x00000000 0x00000000  0x00000000
   0x16  0x00000000 0x00000000 0x00000000  0x00000000
   0x17  0x00000000 0x00000000 0x00000000  0x00000000
   0x18  0x00000000 0x00000000 0x00000000  0x00000000
   0x19  0x00000000 0x00000000 0x00000000  0x00000000
   0x1a  0x00000000 0x00000000 0x00000000  0x00000000
   0x1b  0x00000000 0x00000000 0x00000000  0x00000000
   0x1c  0x00000000 0x00000000 0x00000000  0x00000000
   0x1d  0x00000000 0x00000000 0x00000000  0x00000000
   0x1e  0x00000000 0x00000000 0x00000000  0x00000000
   0x1f  0x00000000 0x00000000 0x00000000  0x00000000
   0x20  0x00000000 0x00000000 0x00000000  0x00000000
   0x21  0x00000000 0x00000000 0x00000000  0x00000000

/******再看來看redo的情況****************/     
/********找到當前日誌並trace******************/
SQL> select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
         3          1        689   52428800        512          1 NO       INACTIVE               9537783 2013/3/22 1      9539502 2013/3/22 1
         4          1        690  209715200        512          1 NO       INACTIVE               9539502 2013/3/22 1      9544823 2013/3/22 1
         5          1        691  209715200        512          1 NO       INACTIVE               9544823 2013/3/22 1      9551076 2013/3/22 1
         6          1        692  209715200        512          1 NO       INACTIVE               9551076 2013/3/22 1      9557772 2013/3/22 1
         7          1        693  209715200        512          1 NO       INACTIVE               9557772 2013/3/22 1      9564180 2013/3/22 1
         8          1        694  209715200        512          1 NO       INACTIVE               9564180 2013/3/22 1      9577485 2013/3/22 1
         9          1        696  209715200        512          1 NO       CURRENT                9583525 2013/3/22 2 281474976710
        10          1        695   20971520        512          1 NO       INACTIVE               9577485 2013/3/22 1      9583525 2013/3/22 2
 
8 rows selected
 
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         7         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO07.LOG                                       NO
         8         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO08.LOG                                       NO
         3         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO03.LOG                                       NO
         4         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO04.LOG                                       NO
         5         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO05.LOG                                       NO
         6         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO06_1.LOG                                     NO
         9         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO09.LOG                                       NO
        10         ONLINE  D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO10.LOG                                       NO
 
8 rows selected
/*************************dump線上日誌********************/
SQL> alter system dump logfile 'D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO09.LOG';
 
System altered

DUMP OF REDO FROM FILE 'D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO09.LOG'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff --rba唯一標記每個重作日誌條目
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 
 FILE HEADER:
 Compatibility Vsn = 186646528=0xb200000
 Db ID=1331266241=0x4f5982c1, Db Name='ORCL'
 Activation ID=1331289025=0x4f59dbc1
 Control Seq=19901=0x4dbd, File size=409600=0x64000 --日誌檔案大小
 File Number=9, Blksiz=512, File Type=2 LOG --日誌檔案編號
 descrip:"Thread 0001, Seq# 0000000696, SCN 0x000000923ba5-0xffffffffffff"
 thread: 1 nab: 0xffffffff seq: 0x000002b8 hws: 0x1 eot: 1 dis: 0
 resetlogs count: 0x2fe43741 scn: 0x0000.00000001 (1)
 prev resetlogs count: 0x0 scn: 0x0000.00000000
 Low  scn: 0x0000.00923ba5 (9583525) 03/22/2013 21:30:37 --最低的scn
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 ---最高的scn
 Enabled scn: 0x0000.00000001 (1) 12/31/2012 14:25:10
 Thread closed scn: 0x0000.00923ba5 (9583525) 03/22/2013 21:30:37
 Disk cksum: 0x3704 Calc cksum: 0x3704
 Terminal recovery stop scn: 0x0000.00000000
 Terminal recovery  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000
 Largest LWN: 0 blocks
 End-of-redo stream : No
 Unprotected mode
 Miscellaneous flags: 0x800000
 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
 
 --具體的重作條目或叫 重作記錄,日誌檔案有許多條這樣的結構
 REDO RECORD - Thread:1 RBA: 0x0002b8.00000002.0010 LEN: 0x0070 VLD: 0x06 --rba,rba有3個部分,與v$log對應
SCN: 0x0000.00923bf0 SUBSCN:  1 03/22/2013 21:34:13 --也有scn
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
 Block Written - afn: 3 rdba: 0x00c019c8 BFT:(1024,12589512) non-BFT:(3,6600)
                   scn: 0x0000.00923ac9 seq: 0x01 flg:0x04
                  
REDO RECORD - Thread:1 RBA: 0x0002b8.00000004.0010 LEN: 0x00e0 VLD: 0x06
SCN: 0x0000.00923c0d SUBSCN:  1 03/22/2013 21:35:41
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
 Block Written - afn: 2 rdba: 0x00801798 BFT:(1024,8394648) non-BFT:(2,6040) --寫入了資料塊
                   scn: 0x0000.00923af3 seq: 0x01 flg:0x04
 Block Written - afn: 2 rdba: 0x00801799 BFT:(1024,8394649) non-BFT:(2,6041)
                   scn: 0x0000.00923af2 seq: 0x01 flg:0x04
 Block Written - afn: 2 rdba: 0x0080179a BFT:(1024,8394650) non-BFT:(2,6042)
                   scn: 0x0000.00923af2 seq: 0x01 flg:0x04
 Block Written - afn: 2 rdba: 0x0080179b BFT:(1024,8394651) non-BFT:(2,6043)
                   scn: 0x0000.00923af4 seq: 0x02 flg:0x06
 Block Written - afn: 2 rdba: 0x0080179c BFT:(1024,8394652) non-BFT:(2,6044)
                   scn: 0x0000.00923b78 seq: 0x01 flg:0x04
 Block Written - afn: 2 rdba: 0x0080179d BFT:(1024,8394653) non-BFT:(2,6045)
                   scn: 0x0000.00923af2 seq: 0x01 flg:0x04
 Block Written - afn: 2 rdba: 0x0080179e BFT:(1024,8394654) non-BFT:(2,6046)
                   scn: 0x0000.00923af2 seq: 0x01 flg:0x04
 Block Written - afn: 2 rdba: 0x0080179f BFT:(1024,8394655) non-BFT:(2,6047)
                   scn: 0x0000.00923b7d seq: 0x01 flg:0x04
                  
REDO RECORD - Thread:1 RBA: 0x0002b8.00000006.01ac LEN: 0x0044 VLD: 0x02
SCN: 0x0000.00923c0d SUBSCN:  1 03/22/2013 21:35:41
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
 Block Written - afn: 2 rdba: 0x00810954 BFT:(1024,8456532) non-BFT:(2,67924)
                   scn: 0x0000.00923b0c seq: 0x01 flg:0x06
                                      
REDO RECORD - Thread:1 RBA: 0x0002b8.00000862.0050 LEN: 0x0108 VLD: 0x01
SCN: 0x0000.00923d74 SUBSCN: 51 03/22/2013 21:40:37
CHANGE #1 TYP:0 CLS:124 AFN:3 DBA:0x00c01c0f OBJ:4294967295 SCN:0x0000.00923d74 SEQ:50 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 100 spc: 3958 flg: 0x0022 seq: 0x0196 rec: 0x33 --說明日誌檔案對undo也有redo記錄
            xid:  0x0036.00c.00000089 
ktubu redo: slt: 12 rci: 50 opc: 10.22 objn: 6216 objd: 6216 tsn: 1
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
index undo for leaf key operations
KTB Redo
op: 0x02  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: C  uba: 0x00c01c0f.0196.31
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x8010c2 block=0x0080dec0
(kdxlpu): purge leaf row
key :(32):
 06 c5 0e 20 1b 3f 2a 0d 64 64 74 31 70 73 35 78 62 64 6e 33 64 06 c5 26 4d
 03 14 5d 03 c2 02 07
CHANGE #2 TYP:0 CLS:1 AFN:2 DBA:0x0080dec0 OBJ:6216 SCN:0x0000.00923d74 SEQ:25 OP:10.2 ENC:0 RBL:0 --每條重作條目由多個change向量,也叫重作向量組成;即對資料塊一次更改產生一個redo vector
index redo (kdxlin):  insert leaf row
KTB Redo
op: 0x02  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: C  uba: 0x00c01c0f.0196.33
REDO: SINGLE / -- / --
itl: 2, sno: 45, row size 42
insert key: (32):
 06 c5 0e 20 1b 3f 2a 0d 64 64 74 31 70 73 35 78 62 64 6e 33 64 06 c5 26 4d
 03 14 5d 03 c2 02 07
keydata: (6):  00 81 0d f0 00 15 

---在日誌檔案中可看到塊清除記錄
Block cleanout record, scn:  0x0000.00923f2e ver: 0x01 opt: 0x02, entries follow...
  itli: 3  flg: 2  scn: 0x0000.0092039f
  itli: 4  flg: 2  scn: 0x0000.009203a4
  itli: 5  flg: 2  scn: 0x0000.00923f2e                 
4,小結
  1,對redo這裡內部結構還有些暈,把undo和redo未聯絡起來
 
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-756904/,如需轉載,請註明出處,否則將追究法律責任。

相關文章