关于ORACLE redo里是否会记录before image的讨论

aquarius

在进行dml操作的时候，会将事务相关的undo segment header,undo block,buffer itl等信息在PGA里生成change vector,进而以redo entry的形式生成相关redo信息，用以之后的instance recovery!那被修改的数据的before image会保存到redo里么？个人的观点如：
在生成修改undo block的redo的时候  应该是将修改信息写到redo了！向量应该是针对修改事务块的，而记录修改undo block的redo应该是包含了old value的 ！

请各位大虾拍砖！

orafans

个人觉得redo是会记录undo中相关block的修改情况，redo记录的是change vector（Each Change Vector contains a header. Header of Change Vector contains change number, change type, operation code. Change vector also consists with physical file location, Segment id, Datablock address
(DBA) and respective ROWID. It also contains block version number.），但不是undo 中的before image。

引用otn：
When you execute update statement as result generate redo information but is not contain before image of your updated data blocks and also undo blocks.Yes this redo will contain both blocks information but its not before image of these blocks.This is indicate "what happen there and how blocks was changed" and this is called change vector(redo).This change will appear in log buffer initially and immediately will write to online logs by LGWR.After instance crash and next startup automatically will happen instance recovery and in this case oracle do not use log buffer its read from online redo log groups and according blocks undo segments then all change will apply,uncommitted transaction will rollback.

另外,Oracle redo中存在有以下几项信息：Redo record header ，Thread - Thread number ，RBA - Redo Byte Address ，LEN - Length of Change Number ，SCN - System Change Number, date and Time 。

请勿大砖头。

lghwf

但有一事不明，如果REDO 里 没有before image的话，LOG MINER 生成的UNDO SQL 中的数据从何而来呢？ 是从UNDO BLOCK里来的么？

aquarius

假如redo里不包含before image的话，那为什么delete产生的redo会比insert多很多呢？

orafans

I think redo nerver contains undo sql statements.

Maclean Liu(刘相兵

做一个简单的实验说明该问题,ODM TEST:

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system checkpoint;  

System altered.

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         71   52428800          2 NO  CURRENT               15989215 30-MAR-12
         2          1         70   52428800          2 YES INACTIVE              15988140 30-MAR-12
         3          1         69   52428800          2 YES INACTIVE              15981490 30-MAR-12

SQL> create table undo_log (t1 timestamp);

Table created.

SQL> insert into undo_log values(systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(t1,16) from undo_log;

DUMP(T1,16)
---------------------------------------------------------------
Typ=180 Len=11: 78,70,3,1e,9,1c,2e,7,46,59,b8   ==>  before image


SQL> update undo_log set t1=systimestamp;

1 row updated.

SQL> commit;

Commit complete.

SQL> select dump(t1,16) from undo_log;

DUMP(T1,16)
-----------------------------------------------------------
Typ=180 Len=11: 78,70,3,1e,9,1e,1,f,d1,f8,f8==> after image


SQL> ALTER SYSTEM DUMP LOGFILE '/s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log';

System altered.

SQL> ORADEBUG SETMYPID;
Statement processed.
SQL> ORADEBUG TRACEFILE_NAME
/s01/admin/G10R21/udump/g10r21_ora_3738.trc


可以在TRACE中找到 相关的 REDO RECORD，一条RECORD中可能包含多个change， 每个change包含不同的OP operation，例如undo 、INSERT、UPDATE、DELETE、BLOCK CLEANOUT等。


REDO RECORD - Thread:1 RBA: 0x000047.00000038.0010 LEN: 0x0218 VLD: 0x05
SCN: 0x0000.00f3fa1a SUBSCN:  1 03/30/2012 08:29:03
CHANGE #1 TYP:0 CLS:31 AFN:8 DBA:0x02000059 OBJ:4294967295 SCN:0x0000.00f3f9f8 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x00000ddf flg: 0x0012 siz: 164 fbi: 0
            uba: 0x02000b28.0398.05    pxid:  0x0000.000.00000000
            
            
            
            
            
            
CHANGE #2 TYP:0 CLS:32 AFN:8 DBA:0x02000b28 OBJ:4294967295 SCN:0x0000.00f3f9f2 SEQ:  2 OP:5.1
ktudb redo: siz: 164 spc: 7634 flg: 0x0012 seq: 0x0398 rec: 0x05
            xid:  0x0008.014.00000ddf
ktubl redo: slt: 20 rci: 0 opc: 11.1 objn: 56418 objd: 56418 tsn: 0
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x02000b28.0398.03
prev ctl max cmt scn:  0x0000.00f3f73a  prev tx cmt scn:  0x0000.00f3f7dd
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 33557284  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: 0x0043df12  hdba: 0x0043df11
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col  0: [11]  78 70 03 1e 09 1c 2e 07 46 59 b8



CHANGE #2 的 OP 5.1 undo

xid:  0x0008.014.00000ddf
Undo type:  Regular undo  ==>常规redo


col  0: [11]  78 70 03 1e 09 1c 2e 07 46 59 b8 ==> before image

OBJ:4294967295
DBA:0x02000b28  





CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0043df12 OBJ:56418 SCN:0x0000.00f3fa01 SEQ:  1 OP:11.5
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0008.014.00000ddf    uba: 0x02000b28.0398.05
Block cleanout record, scn:  0x0000.00f3fa1a ver: 0x01 opt: 0x02, entries follow...
itli: 1  flg: 2  scn: 0x0000.00f3fa01
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0043df12  hdba: 0x0043df11
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 1 nnew: 1 size: 0
col  0: [11]  78 70 03 1e 09 1e 01 0f d1 f8 f8


OP:11.5 UPR update row

同时发生了Block cleanout


col  0: [11]  78 70 03 1e 09 1e 01 0f d1 f8 f8==》AFTER IMAGE


CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:5.20
session number   = 153
serial  number   = 52
transaction name =

aquarius

感谢 LIU ！
还有一点不是很明白，dump(t1,16) 获取的是什么数据呢？为什么是16啊？怎么确定16这个值？

orafans

Regular undo  就是常规redo ？那redo的改变向量跟undo的before image有关系吗？redo不会以change vector形式记录undo中的before image被更该的数据吗？

Maclean Liu(刘相兵

dump 是SQL 转储函数，它可以将 数据转换为Oracle存储的形式，例如：

SQL> select dump(1) from dual;

DUMP(1)
------------------------------------
Typ=2 Len=2: 193,2


==》  dump默认使用10进制 ， 以上相当于

SQL> select dump(1,10) from dual;

DUMP(1,10)
------------------------------------
Typ=2 Len=2: 193,2



但是实际Oracle以16进制store data，所以  number 1 对应的 16进制 储值是：

SQL> select dump(1,16) from dual;

DUMP(1,16)
----------------------------------
Typ=2 Len=2: c1,2

也可以对char或其他类型 转储

SQL> select dump('a',16) from dual;

DUMP('A',16)
--------------------------------
Typ=96 Len=1: 61

aquarius

好的   明白了  谢谢！

Maclean Liu(刘相兵

DUMP

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value         DUMP(<value> [,<return_format>[,<start_position>[,<length>]]])
8         Octal
10         Decimal
16         Hexidecimal
17         Single Characters
1008         octal notation with the character set name
1010         decimal notation with the character set name
1016         hexadecimal notation with the character set name
1017         single characters with the character set name
set linesize 121
col dmp format a50

SELECT table_name, DUMP(table_name) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16, 7, 4) DMP FROM user_tables;

aquarius

9i的环境，发生了较严重的latch free等待，通过v$session_wait查的p2关联v$latch查看到是cache buffer chains, 但是p1列显示值：1.3835E+19，如下：
SID       SEQ#    EVENT                                                            P1TEXT                                                                   P1 P1RAW            P2TEXT                                                                   P2 P2RAW            P3TEXT                                                                   P3 P3RAW             WAIT_TIME SECONDS_IN_WAIT      STATE
------ ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------- ---------------  -------------------
   183        438 latch free                                                       address                                                          1.3835E+19 C0000000771DA3B0 number                                                                   98 0000000000000062 tries                                                                     0 00                        1               2   WAITED KNOWN TIME

怎么将1.3835E+19 转换成正常的latch address呢? 那样才可以找到对于哪个对象的block发生了cache buffer chains等待！