redo logfile operation codes

redo logfile operation codes

By admin - Last updated: Friday, March 21, 2014 - Save & Share - Leave a Comment

关于redo logfile 中的Operation Codes (OP)，其代码编号代表了记录在redo log 中的操作类型，其中操作类型超过了150多种，其中每一个OP包含layer code 和 sub code。例如OP:11.2，其中11代表的row operation，2代表insert single row。

Layers 包含了一下几种：

Layer Description
4 Block Cleanout
5 Transaction Management
10 Index Operations
11 Row Operations
13 Segment Management
14 Extent Management
17 Tablespace Management
18 Block Image(Hot backups)
19 Direct Loader
20 Compatibility Segment
22 Locally Managed Tablespaces
23 Block Writes
24 DDL Statements

下面做几个测试来说明常见的几种Operation Codes。
首先举例说明关于Transaction Management：

SQL > select * from v $ log ;
 
     GROUP #    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
-------- -- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1           1         103     52428800           1 NO   CURRENT                   646900 12 - JUN - 12
         2           1         101     52428800           1 YES INACTIVE                 643203 12 - JUN - 12
         3           1         102     52428800           1 YES INACTIVE                 644692 12 - JUN - 12
 
SQL > select dbms_flashback . get_system_change_number from dual ;
 
GET_SYSTEM_CHANGE_NUMBER
---------------------- --
                  655172
 
SQL > update honcho set id = 100 where id = 1 ;
 
1 row updated .
 
SQL > commit ;
 
Commit complete .
 
SQL > select dbms_flashback . get_system_change_number from dual ;
 
GET_SYSTEM_CHANGE_NUMBER
---------------------- --
                  655187
 
SQL > alter system dump logfile ' /u01/app/oracle/oradata/honcho/redo01.log ' scn min 655172 scn max 655187 ;
 
System altered .
 
SQL >  select d . value
    || ' / '
    || lower ( rtrim ( i . instance , chr ( 0 )))
    || ' _ora_ '
    || p . spid
    || ' .trc '
     as " trace_file_name "
from ( select p . spid from v $ mystat m , v $ session s , v $ process p
     where m . statistic # = 1 and s.sid = m.sid and p.addr = s.paddr) p ,
  ( select t . instance from v $ thread t , v $ parameter v
     where v . name = ' thread '
     and ( v . value = 0 or t . thread # = to_number(v.value))) i,
         ( select value from v $ parameter
         where name = ' user_dump_dest ' ) d ;
 
trace_file_name
-------------------------------------------------------------------------------------------- --
/u01/app/oracle/admin/honcho/udump/honcho_ora_7859.trc

查看dump 文件

REDO RECORD - Thread:1 RBA: 0x000067.0000d05d.0010 LEN: 0x0238 VLD: 0x05
SCN: 0x0000.0009ff4d SUBSCN:  1 06/13/2012 09:18:42
CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x0000.0009fe98 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0000 sqn: 0x000000b5 flg: 0x0012 siz: 156 fbi: 0
            uba: 0x00803331.00a6.0f    pxid:  0x0000.000.00000000

我们知道当我们执行一个DML操作时就是一个事务的开始，此时就会分配undo segment，所以会在undo header 中创建一个OP 5.2去更新transaction table。

CHANGE #2 TYP:0 CLS:30 AFN:2 DBA:0x00803331 OBJ:4294967295 SCN:0x0000.0009fe97 SEQ:  1 OP:5.1
ktudb redo: siz: 156 spc: 5090 flg: 0x0012 seq: 0x00a6 rec: 0x0f
            xid:  0x0007.000.000000b5
ktubl redo: slt: 0 rci: 0 opc: 11.1 objn: 10378 objd: 10378 tsn: 5
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00803331.00a6.0e
prev ctl max cmt scn:  0x0000.0009f4ec  prev tx cmt scn:  0x0000.0009f4f6
txn start scn:  0xffff.ffffffff  logon user: 30  prev brb: 8396295  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0140000f  hdba: 0x0140000b
itli: 3  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 183
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 02

接下来的change 就是记录信息到事务槽中，因此OP 是5.1，5.1 代表 undo slot。一个事务结束的标志是commit或者rollback，我们在dump文件末尾查看是否有相关的OP

REDO RECORD - Thread:1 RBA: 0x000067.0000d05f.0014 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.0009ff4e SUBSCN:  1 06/13/2012 09:18:42
CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x0000.0009ff4d SEQ:  1 OP:5.4
ktucm redo: slt: 0x0000 sqn: 0x000000b5 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00803331.00a6.11 ext: 14 spc: 4776 fbi: 0
END OF REDO DUMP

在dump文件的末尾我们看到了OP：5.4的记录，5.4代表的就是一个事务的结束。

现在我们来说明关于row operation 的OP，关于row operation 的OP 描述如下：

Opcode Description
11.2 Insert Single Row
11.3 Delete Single Row
11.4 Lock Row
11.5 Update Row
11.6 Chained Row
11.9 Cluster key index
11.10 Set cluster key pointers
11.11 Insert Multiple Rows
11.12 Delete Multiple Rows

现在我们来对表honcho做一些常用的操作来验证上面的OP

SQL > alter system switch logfile ;
 
System altered .
 
SQL > select * from v $ log ;
 
     GROUP #    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
-------- -- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1           1         103     52428800           1 YES ACTIVE                   646900 12 - JUN - 12
         2           1         104     52428800           1 NO   CURRENT                   655820 13 - JUN - 12
         3           1         102     52428800           1 YES INACTIVE                 644692 12 - JUN - 12
 
SQL > select dbms_flashback . get_system_change_number from dual ;
 
GET_SYSTEM_CHANGE_NUMBER
---------------------- --
                  655830
 
SQL > insert into honcho values ( 51 , ' honcho ' ) ;
 
1 row created .
 
SQL > delete honcho where id = 51 ;
 
1 row deleted .
 
SQL > update honcho set id = 1 where id = 100 ;
 
1 row updated .
 
SQL > insert into honcho select * from honcho ;
 
100000 rows created .
 
SQL > delete honcho where id like ' 9% ' ;
 
22222 rows deleted .
 
SQL > commit ;
 
Commit complete .
 
SQL > select dbms_flashback . get_system_change_number from dual ;
 
GET_SYSTEM_CHANGE_NUMBER
---------------------- --
                  655891
 
SQL > alter system dump logfile ' /u01/app/oracle/oradata/honcho/redo02.log ' scn min 655830 scn max 655891 ;
 
System altered .
 
SQL > select d . value
    || ' / '
    || lower ( rtrim ( i . instance , chr ( 0 )))
    || ' _ora_ '
    || p . spid
    || ' .trc '
     as " trace_file_name "
from ( select p . spid from v $ mystat m , v $ session s , v $ process p
     where m . statistic # = 1 and s.sid = m.sid and p.addr = s.paddr) p ,
  ( select t . instance from v $ thread t , v $ parameter v
     where v . name = ' thread '
     and ( v . value = 0 or t . thread # = to_number(v.value))) i,
         ( select value from v $ parameter
         where name = ' user_dump_dest ' ) d ;
  2     3     4     5     6     7     8     9     10     11     12     13     14
trace_file_name
-------------------------------------------------------------------------------------------- --
/u01/app/oracle/admin/honcho/udump/honcho_ora_9250.trc

查看dump文件

CHANGE #3 TYP:2 CLS: 1 AFN:5 DBA:0x0140000f OBJ:10378 SCN:0x0000.0009ff4e SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0004.011.000000e8    uba: 0x008032f8.00d0.0a
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0140000f  hdba: 0x0140000b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 50(0x32) size/delt: 13

与上面的insert 单行数据相符合。

CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0140000f OBJ:10378 SCN:0x0000.000a0208 SEQ:  4 OP:11.3
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x008032f9.00d0.27
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0140000f  hdba: 0x0140000b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 55(0x37)

与上面的delete某一行相符合。

CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0140000f OBJ:10378 SCN:0x0000.000a01e2 SEQ:  1 OP:11.5
KTB Redo
op: 0x12  ver: 0x01
op: C  uba: 0x008032f8.00d0.0e
Block cleanout record, scn:  0x0000.000a01e7 ver: 0x01 opt: 0x02, entries follow...
  itli: 3  flg: 2  scn: 0x0000.0009ff4e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0140000f  hdba: 0x0140000b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 183
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 02

与上面的update某一行相吻合。

CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x014023ed OBJ:11396 SCN:0x0000.000a048a SEQ:  2 OP:11.11
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0007.00b.000000b5    uba: 0x00803332.00a6.05
KDO Op code: QMI row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x014023ed  hdba: 0x01400b2b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 lock: 1 nrow: 214

这里与上面的多行插入相匹配。

这里关于OP 11.12并没有找到，关于Delete Multiple Rows的操作可能与我的理解有一定出入，还待进一步了解。

关于index 的OP的描述如下：

Opcode Description
10.2 Insert Leaf Row
10.3 Purge Leaf Row
10.4 Delete Leaf Row
10.5 Restore Leaf Log
10.6 Lock Block
10.7 Clear block opcode on commit
10.8 Initialise header
10.9 Apply XAT do to ITL 1
10.10 Set leaf block next pointer
10.11 Set leaf block previous pointer
10.12 Initialize root block after split
10.13 Make leaf block empty
10.15 Insert branch row
10.16 Purge branch row
10.18 Update key data in row
10.19 Clear split flag
10.21 Undo branch operation
10.22 Undo leaf operation
10.24 Shrink ITL
10.30 Update nonkey value
10.31 Create/Load Index
10.34 Make leaf block empty

关于index 的OP这里就不演练了，如果感兴趣可以自己来验证

最后给大家演练一个hot backup 产生redo 的例子

SQL > select * from v $ log ;
 
     GROUP #    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
-------- -- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1           1         115     52428800           1 YES ACTIVE                   662431 13 - JUN - 12
         2           1         116     52428800           1 NO   CURRENT                   662723 13 - JUN - 12
         3           1         114     52428800           1 YES INACTIVE                 662210 13 - JUN - 12
 
SQL >  select dbms_flashback . get_system_change_number from dual ;
 
GET_SYSTEM_CHANGE_NUMBER
---------------------- --
                  662738
 
SQL > alter tablespace honcho begin backup ;
 
Tablespace altered .
 
SQL > create table backup ( id number , name varchar2 ( 20 )) tablespace honcho ;
 
Table created .
 
SQL > alter tablespace honcho end backup ;
 
Tablespace altered .
 
SQL > select dbms_flashback . get_system_change_number from dual ;
 
GET_SYSTEM_CHANGE_NUMBER
---------------------- --
                  662794
 
SQL > alter system dump logfile ' /u01/app/oracle/oradata/honcho/redo02.log ' scn min 662738 scn max 662794 ;
 
System altered .

REDO RECORD - Thread:1 RBA: 0x000074.0000002f.01dc LEN: 0x2024 VLD: 0x01
SCN: 0x0000.000a1cf2 SUBSCN:  1 06/13/2012 11:09:09
CHANGE #1 TYP:3 CLS:13 AFN:5 DBA:0x01400002 OBJ:4294967295 SCN:0x0000.000a04e7 SEQ:  2 OP:18.1
Log block image redo entry
Dump of memory from 0xAE1A9220 to 0xAE1AB208
AE1A9220 00000005 00000008 00003180 00000009  [.........1......]
AE1A9230 00000001 003FFFFE 00000007 00003180  [......?......1..]
AE1A9240 000004A0 0000019B 0009CB9C 00000000  [................]
AE1A9250 00000000 00000000 00000000 00002489  [.............$..]
AE1A9260 00000080 00000000 00000000 00000000  [................]
AE1A9270 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
AE1AB200 00000000 00000000                    [........]
Dump of memory from 0xAE1AB208 to 0xAE1AB209
AE1AB200                   0000001D                   [....]

此时数据库镜像会写入redo logfile，标记为18.1.当end backup 时tablespace 的更改信息也会写入redo 标记为17.1

REDO RECORD - Thread:1 RBA: 0x000074.00000069.00a4 LEN: 0x0044 VLD: 0x01
SCN: 0x0000.000a1d03 SUBSCN:  1 06/13/2012 11:09:18
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:17.1
End backup marker - file:5 scn:  0x0000.000a1cdc

关于redo Operation Codes 就简单的写了这些，如果感兴趣的话可以深入学习。

« About temporary table redo logfile

Oracle SQL Profiles »