环境:(不新建表空间,不新建用户都可以,这个不是关键)
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