oralce redo

环境：（不新建表空间，不新建用户都可以，这个不是关键）

CREATE TABLESPACE ORCLDBF DATAFILE 'D:/ORA/ORCL/ORCLDBF.DBF' SIZE 16M AUTOEXTEND ON;
CREATE USER ORCLDBF IDENTIFIED BY 1 DEFAULT TABLESPACE ORCLDBF;
GRANT DBA TO ORCLDBF;
CONN ORCLDBF/1
CREATE TABLE T_ORCLDBF
(
  ITEMID      NUMBER(9) NOT NULL,
  ELEMENTCODE VARCHAR2(30) NOT NULL,
  CODE        VARCHAR2(20) NOT NULL,
  NAME        VARCHAR2(200) NOT NULL,
  STATUS      NUMBER(9) NOT NULL,
  STARTDATE   DATE,
  ENDDATE     DATE,
  REMARK      VARCHAR2(200)
);

insert into T_ORCLDBF (ITEMID, ELEMENTCODE, CODE, NAME, STATUS, STARTDATE, ENDDATE, REMARK)
values (210439, 'SUMROWTYPE', '1', '模板编码', 2, to_date('14-12-2010 20:40:32', 'dd-mm-yyyy hh24:mi:ss'), to_date('29-04-1999', 'dd-mm-yyyy'), '原始');

更新

UPDATE ORCLDBF.T_ORCLDBF SET REMARK='新值' WHERE ITEMID=210439;

COMMIT;

--Specify CHECKPOINT to explicitly
--force Oracle Database to perform
--a checkpoint, ensuring that all
--changes made by committed transactions
--are written to datafiles on disk.
ALTER SYSTEM CHECKPOINT;
--The FLUSH BUFFER_CACHE clause lets
--you clear all data from the buffer
--cache in the system global area (SGA).
ALTER SYSTEM FLUSH BUFFER_CACHE;

dump出redo

确定当前log,因为默认有3个redo.log

SELECT MEMBER
  FROM V$LOGFILE
 WHERE GROUP# = (select GROUP# from v$log where STATUS = 'CURRENT')

dump trace

SQL> ORADEBUG SETMYPID
已处理的语句
SQL> ORADEBUG TRACEFILE_NAME
D:\APP\QAZ\diag\rdbms\opt\opt\trace\opt_ora_6952.trc
SQL> ALTER SYSTEM DUMP LOGFILE 'D:\APP\QAZ\ORADATA\OPT\REDO01.LOG'

查看trace 720行

SQL> SELECT DUMP('新值',16),DUMP('原始',16) FROM DUAL;

DUMP('新值',16)           DUMP('原始',16)
------------------------- -------------------------
Typ=96 Len=4: d0,c2,d6,b5 Typ=96 Len=4: d4,ad,ca,bc

搜索d0 c2 d6 b5或者d4 ad ca bc

REDO RECORD - Thread:1 RBA: 0x000040.00000014.0010 LEN: 0x025c VLD: 0x0d
SCN: 0x0000.001859e3 SUBSCN:  1 04/05/2017 11:13:58
(LWN RBA: 0x000040.00000014.0010 LEN: 0002 NST: 0001 SCN: 0x0000.001859e2)
CHANGE #1 TYP:2 CLS:1 AFN:6 DBA:0x01800085 OBJ:89830 SCN:0x0000.0018598a SEQ:2 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: F  xid:  0x000a.00e.00000661    uba: 0x00c00502.00e6.33
Block cleanout record, scn:  0x0000.001859e1 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.0018598a
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 13
ncol: 8 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01800085  hdba: 0x01800082
itli: 2  ispac: 0  maxfr: 4858
vect = 60
col  7: [ 4]  d0 c2 d6 b5
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001859cc SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000e sqn: 0x00000661 flg: 0x0012 siz: 188 fbi: 0
            uba: 0x00c00502.00e6.33    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.001859e3 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000e sqn: 0x00000661 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00502.00e6.33 ext: 2 spc: 1356 fbi: 0
CHANGE #4 TYP:0 CLS:36 AFN:3 DBA:0x00c00502 OBJ:4294967295 SCN:0x0000.001859cc SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 188 spc: 1546 flg: 0x0012 seq: 0x00e6 rec: 0x33
            xid:  0x000a.00e.00000661 
ktubl redo: slt: 14 rci: 0 opc: 11.1 [objn: 89830 objd: 89830 tsn: 7]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00502.00e6.31
prev ctl max cmt scn:  0x0000.001855ee  prev tx cmt scn:  0x0000.001855f9
txn start scn:  0x0000.00000000  logon user: 0  prev brb: 12584192  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: L  itl: xid:  0x0006.002.00000679 uba: 0x00c016c4.0144.14
                      flg: C---    lkc:  0     scn: 0x0000.00185831
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 13
ncol: 8 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01800085  hdba: 0x01800082
itli: 2  ispac: 0  maxfr: 4858
vect = 60
col  7: [ 4]  d4 ad ca bc

obj89830：select * from dba_objects where object_id=89830;

这个是表T_ORCLDBF

SET SERVEROUTPUT ON;
DECLARE
  REMARK_ORI VARCHAR2(200);
  REMARK_NOW VARCHAR2(200);
BEGIN
  DBMS_STATS.CONVERT_RAW_VALUE(REPLACE('d4 ad ca bc',' '),REMARK_ORI);
  DBMS_STATS.CONVERT_RAW_VALUE(REPLACE('d0 c2 d6 b5',' '),REMARK_NOW);
  DBMS_OUTPUT.PUT_LINE('REMARK_NOW='||REMARK_ORI);
  DBMS_OUTPUT.PUT_LINE('REMARK_NOW='||REMARK_NOW);
END;

SQL> /
REMARK_NOW=原始
REMARK_NOW=新值

可以看到redolog里记录了remark更新前和更新后的值

完整trace文件

http://download.csdn.net/detail/bjchangxiao/9803972