Analysis of redo log content format generated by DML statements in non-IMU mode

Experiment content: The Redo Log Content format of the DML statement produced in the non-IMU mode is interpreted, the database version: 11.2.0.4

The most detailed explanation is update.

Experimental environment Preparation

The IMU feature is turned on by default in 11G, and this feature needs to be turned off for this experiment.

Alter system set "_in_memory_undo" =FALSE;

Alter system set "_in_memory_undo" =true; --Use this statement to change back to use the IMU feature after the experiment is completed.

After the modification parameters are complete, restart the database:

Shutdown immediate;

Startup

Prepare the experimental table----bys.dept table.

sys@ Bys3>select object_id from dba_objects where object_name= ' DEPT ';

object_id

----------

22327

sys@ Bys3>select * from Bys.dept;

DEPTNO dname LOC

---------- -------------- -------------

Ten ACCOUNTING NEW YORK

DALLAS

OPERATIONS BOSTON

One Chedan BJ

Test sh

bys@ bys3>col file_name for A35

sys@ Bys3>col name for A15

sys@ bys3>select a.file_id,b.ts#,b.name,a.file_name from dba_data_files a,v$tablespace b where a.TABLESPACE_NAME= B.name;

file_id ts# NAME file_name

---------- ---------- --------------- -----------------------------------

1 0 system/u01/oradata/bys3/system01.dbf

2 1 sysaux/u01/oradata/bys3/sysaux01.dbf

3 2 undotbs1/u01/oradata/bys3/undotbs01.dbf

4 4 USERS/U01/ORADATA/BYS3/USER01.DBF

###################################################

start the experiment in three steps, respectively, insert, UPDATE, delete the operation of the statement. The most detailed explanation is update.

Experiment One: Redo log interpretation of insert operation

Session 1:--sys user to switch logs and find the current log name:

sys@ bys3>alter system switch logfile;

System altered.

Col Member for A30

sys@ bys3>select a.group#,a.sequence#,a.archived,a.status,b.type,b.member from V$log a,v$logfile b where a.group#= b.group#;

group# sequence# ARC STATUS TYPE Member

---------- ---------- --- ---------------- ------- ------------------------------

1 310 NO Current Online/u01/oradata/bys3/redo01.log

2 308 YES INACTIVE Online/u01/oradata/bys3/redo02.log

3 309 YES ACTIVE Online/u01/oradata/bys3/redo03.log

Ordinary users do INSERT statements:

sys@ Bys3>conn Bys/bys

SELECT * FROM dept;

Connected.

bys@ bys3>

DEPTNO dname LOC

---------- -------------- -------------

Ten ACCOUNTING NEW YORK

DALLAS

OPERATIONS BOSTON

One Chedan BJ

Test sh

bys@ Bys3>set time on

20:34:07 bys@ bys3>insert into dept values (at, ' Imutest ', ' Hz ');

1 row created.

20:34:12 bys@ bys3>commit;

Commit complete.

Session 2:dump Current Redo log:

sys@ bys3>alter system dump logfile '/u01/oradata/bys3/redo01.log ';

System altered.

sys@ bys3>select value from V$diag_info where name like ' de% ';

VALUE

----------------------------------------------------------------------------------------------------

/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29063.trc

######################

To view this trace file: Vi/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29063.trc then search for the modified object's object_id--22327

Detailed reference to the next update of the redo explanation.

REDO record-thread:1 rba:0x000136.0000000d.0010 len:0x01a0 vld:0x05

scn:0x0000.00702f08 Subscn:1 01/06/2014 20:34:13

(LWN rba:0x000136.0000000d.0010 len:0002 nst:0001 scn:0x0000.00702f08)

Change #1 typ:0 cls:21 afn:3 dba:0x00c000a0 obj:4294967295 scn:0x0000.00702eda seq:1 op:5.2 enc:0 rbl:0

Ktudh redo:slt:0x0003 SQN:0X00000EAA flg:0x0012 siz:112 fbi:0

uba:0x00c01ac7.0250.25 pxid:0x0000.000.00000000

Change #2 typ:0 cls:22 afn:3 dba:0x00c01ac7 obj:4294967295 scn:0x0000.00702ed9 seq:3op:5.1 rbl:0

Ktudb redo:siz:112 spc:3494 flg:0x0012 seq:0x0250 rec:0x25

Xid:0x0003.003.00000eaa

Ktubl redo:slt:3 rci:0 opc:11.1 [objn:22327 objd:22327 Tsn:4]-------Tsn:4, this is the OB of the object that is inserted in the table space number 4--obj:22327-- ject_id

Undo Type:regular Undo Begin Trans Last buffer Split:no

Temp Object:no

Tablespace Undo:no

0x00000000 Prev CTL uba:0x00c01ac7.0250.22

Prev CTL Max CMT Scn:0x0000.007029ac prev TX CMT Scn:0x0000.007029d6

TXN start scn:0xffff.ffffffff logon user:32 prev brb:12589763 prev bcl:0 buext idx:0 flg2:0

KDO Undo Record:

KTB Redo

op:0x03 ver:0x01

Compat Bit:4 (post-11) padding:1

Op:z

KDO Op code:drp Row Dependencies Disabled

Xtype:xa flags:0x00000000 bdba:0x010000ff HDBA:0X010000FA

Itli:1 ispac:0 maxfr:4858

tabn:0 slot:0 (0x0)

Change #3 typ:0 Cls:1 afn:4 dba:0x010000ff obj:22327 scn:0x0000.003eec75 seq:1 op:11.2 enc:0 rbl:0--op:11.2 This should be an insert operation.

KTB Redo-----afn:4, this is inserted in the 4th data file--obj:22327--is the inserted object object_id

OP:0X01 ver:0x01

Compat Bit:4 (post-11) padding:1

Op:f XID:0X0003.003.00000EAA uba:0x00c01ac7.0250.25

KDO Op code:irp Row Dependencies Disabled

Xtype:xa flags:0x00000000 bdba:0x010000ff HDBA:0X010000FA

Itli:1 ispac:0 maxfr:4858

tabn:0 slot:0 (0x0) size/delt:17

FB:--h-fl--lb:0x1 Cc:3

NULL:---

Col 0: [2] C1---INSERT statement is: INSERT INTO dept values (, ' imutest ', ' Hz '); The corresponding is: Select Dump (33,16) from dual; --be aware that numbers do not add single quotes to the dump

Col 1: [7] 6d 75 74 65 73 74-corresponds to select dump (' Imutest ') from dual; [7]--in parentheses is the number of bytes, col 1: The corresponding is the second column,

Col 2: [2] 7a---Corresponds to select dump (' Hz ',) from dual; --The number of bytes in brackets Col 2: Corresponds to the third column

Select Chr (substr (replace (' 6d to_number ', '), rownum*2-1,2), ' xxxxxxxxxxxxxxxx ') from V$BH where rownum& lt;11;

CHR (

----

I

M

U

T

E

S

T

REDO record-thread:1 rba:0x000136.0000000d.01b0 len:0x0060 vld:0x01

scn:0x0000.00702f09 subscn:1 01/06/2014 20:34:13----op:5.4 , Submit action

Change #1 typ:0 cls:21 afn:3 dba:0x00c000a0 obj:4294967295 scn:0x0000.00702f08 seq:1 op:5.4 enc:0 rbl:0

KTUCM redo:slt:0x0003 sqn:0x00000eaa srt:0 sta:9 flg:0x2 KTUCF redo:uba:0x00c01ac7.0250.25ext:12 spc:3380 fbi:0

############################

Experiment two: Redo log interpretation of update operation

Session 3:--sys user switch log:

sys@ bys3>alter system switch logfile;

System altered.

sys@ Bys3>col member for A30

sys@ bys3>select a.group#,a.sequence#,a.archived,a.status,b.type,b.member from V$log a,v$logfile b where a.group#= b.group#;

group# sequence# ARC STATUS TYPE Member

---------- ---------- --- ---------------- ------- ------------------------------

1 310 YES ACTIVE Online/u01/oradata/bys3/redo01.log

2 311 NO Current Online/u01/oradata/bys3/redo02.log

3 309 YES INACTIVE Online/u01/oradata/bys3/redo03.log

Switch to normal user to do UPDATE statements:

sys@ Bys3>conn Bys/bys

Connected.

bys@ Bys3>select * FROM dept;

DEPTNO dname LOC

---------- -------------- -------------

Ten ACCOUNTING NEW YORK

DALLAS

OPERATIONS BOSTON

One Chedan BJ

Test sh

Imutest Hz

6 rows selected.

bys@ Bys3>set time on

20:39:23 bys@ bys3>update Dept Set dname= ' database ' where deptno=11;

1 row updated.

20:39:43 bys@ bys3>commit;

Commit complete.

20:39:46 bys@ bys3>

Session 4:dump Current Redo log:

sys@ bys3>alter system dump logfile '/u01/oradata/bys3/redo02.log ';

System altered.

sys@ bys3>select value from V$diag_info where name like ' de% ';

VALUE

----------------------------------------------------------------------------------------------------