昨天,和群里的小伙伴讨论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内容提取有误,如果有朋友发现了请海涵,已经修改。