oracle redo 200mb,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内容提取有误,如果有朋友发现了请海涵,已经修改。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值