Oracle redo日誌內容探索(一)

    昨天，和群裡的小夥伴討論redo內容是什麼，很多人認為redo內就是存放的sql語句，甚至是解析後的sql。

    例如，一條udate對錶A修改了20萬條記錄。redo內相當於形成了20萬條單一修改的sql。

    原本，我個人狹隘的理解為，redo內應該是資料的變化或者是資料塊的變化。

    今天我查了一下官方文件，證明了我也是錯誤的，狹隘的。

    下面為官方文件：

11.1.2 Redo Log Contents

Redo log files are filled with redo records.

A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Redo records are buffered in a circular fashion in the redo log buffer of the SGA (see "How Oracle Database Writes to the Redo Log") and are written to one of the redo log files by the Log Writer (LGWR) database background process. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.

       

        從文件中獲取 redo record ，記錄的是 redo 條目。 redo 條目，是由一組改變向量組成。那改變向量是資料庫中單個資料塊的改變的描述。

        文章中繼續介紹，一個 redo record 包含對錶的資料段， undo 資料段和撤銷段的事務表的改變。

        所以，上面我和其他人對 redo 內容的理解都是不正確的。

        下面我dump了一個redo log

SQL> conn scott/tiger
Connected.
SQL> update t1 set t_name='aaaa' where t_id=113;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system dump logfile '/u01/oracle/oradata/orcl/redo01.log';
System altered.
SQL>  select distinct sid from v$mystat;
       SID
----------
        25
SQL> select value from v$diag_info where name like 'Default%';
VALUE
------------------------------------------------------------------------
/u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_53340.trc

REDO RECORD - Thread:1 RBA: 0x000265.000077a5.0010 LEN: 0x0250 VLD: 0x05
SCN: 0x0000.00411b7a SUBSCN:  1 06/04/2019 17:43:08
(LWN RBA: 0x000265.000077a5.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00411b7a)
CHANGE #1 TYP:0 CLS:181 AFN:3 DBA:0x00c00e50 OBJ:4294967295 SCN:0x0000.0041186c SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x001a sqn: 0x00000039 flg: 0x0012 siz: 200 fbi: 0
            uba: 0x00c005e6.0044.10    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:182 AFN:3 DBA:0x00c005e6 OBJ:4294967295 SCN:0x0000.0041186b SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 200 spc: 586  4 flg: 0x0012 seq: 0x0044 rec: 0x10
            xid:  0x0053.01a.00000039  
ktubl redo: slt: 26 rci: 0 opc: 11.1 [objn: 87351 objd: 87661 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c005e6.0044.0f 
prev ctl max cmt scn:  0x0000.0040c5aa  prev tx cmt scn:  0x0000.0040c76b 
txn start scn:  0xffff.ffffffff  logon user: 83  prev brb: 12584373  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0112.00f.00000027 uba: 0x00c02682.0036.09
                      flg: C---    lkc:  0     scn: 0x0000.003084cb
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x010000ab  hdba: 0x010000aa
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
col  1: [ 4]  61 61 61 61
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010000ab OBJ:87661 SCN:0x0000.00411b4c SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo 
op: 0x11  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0053.01a.00000039    uba: 0x00c005e6.0044.10
Block cleanout record, scn:  0x0000.00411b7a ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.003084cb
  itli: 2  flg: 2  scn: 0x0000.00411b4c
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x010000ab  hdba: 0x010000aa
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0
ncol: 2 nnew: 1 size: 0
col  1: [ 4]  61 61 61 61
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number   = 25
serial  number   = 1781
transaction name = 
version 1866  47552
audit sessionid 720613
Client Id = 
login   username = SCOTT
 
REDO RECORD - Thread:1 RBA: 0x000265.000077a6.0070 LEN: 0x00a4 VLD: 0x01
SCN: 0x0000.00411b7b SUBSCN:  1 06/04/2019 17:43:08
CHANGE #1 TYP:0 CLS:181 AFN:3 DBA:0x00c00e50 OBJ:4294967295 SCN:0x0000.00411b7a SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x001a sqn: 0x00000039 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c005e6.0044.10 ext: 2 spc: 5662 fbi: 0 
CHANGE #2 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0
 
REDO RECORD - Thread:1 RBA: 0x000265.000077a7.0010 LEN: 0x0084 VLD: 0x05
SCN: 0x0000.00411b7e SUBSCN:  1 06/04/2019 17:43:12
(LWN RBA: 0x000265.000077a7.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00411b7d)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 15315Kb in 2.91s => 5.14 Mb/sec
Total redo bytes: 15359Kb Longest record: 13Kb, moves: 14/26657 moved: 0Mb (0%)
Longest LWN: 845Kb, reads: 1449 
Last redo scn: 0x0000.00411b7e (4266878)
Change vector header moves = 2687/466  41 (5%)
----------------------------------------------

    從上面dumpfile的內容也可以看出，確實記錄的是改變向量的內容。

    

    注：文章寫得倉促，後期可能會有一些補充。

    修改內容：

            之前對dumpfile內容提取有誤，如果有朋友發現了請海涵，已經修改。