insert redo怎么记录

最新推荐文章于 2021-04-08 14:36:16 发布

cuchenqiu1926

最新推荐文章于 2021-04-08 14:36:16 发布

阅读量120

点赞数

文章标签：数据库

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 150994944 bytes
Fixed Size                  1247804 bytes
Variable Size             117441988 bytes
Database Buffers           29360128 bytes
Redo Buffers                2945024 bytes
数据库装载完毕。
SQL> alter database flashback on;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> select name ,flashback_on from v$database ;

NAME FLASHBACK_ON
--------- ------------------
SMSDR YES

SQL>

grant execute on dbms_flashback to scott;
conn scott/tiger;
select dbms_flashback.get_system_change_number from dual; --scn1
create table redo_internals_tbl(char_column varchar2(5),varchar2_column varchar2(40));
select dbms_flashback.get_system_change_number from dual; --scn2
insert into redo_internals_tbl values ('A2', 'SECOND ROW');
select dbms_flashback.get_system_change_number from dual; --scn3
select group#,status from v$log;
select group#,member from v$logfile;
--- dump scn2到 scn3
alter system dump logfile '/u01/app/oracle/oradata/diri/redo02.log' scn min 466638 scn max 466676;

-- 查看dump 文件

oradebug mypid

oradebug tracefile_name

******************************************************
dump 文件一个redo record 由4个 change vector chang1#
******************************************************

REDO RECORD - Thread:1 RBA: 0x0000a4.0000017b.01b8 LEN: 0x01b0 VLD: 0x01
SCN: 0x0000.002c4353 SUBSCN: 1 03/10/2011 17:07:46
CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x0000.002c4353 SEQ: 1 OP:5.2
ktudh redo: slt: 0x000f sqn: 0x0000029e flg: 0x0012 siz: 128 fbi: 0
uba: 0x00800615.02b5.02 pxid: 0x0000.000.00000000

CHANGE #2 TYP:0 CLS:30 AFN:2 DBA:0x00800615 OBJ:4294967295 SCN:0x0000.002c431f SEQ: 1 OP:5.1
ktudb redo: siz: 128 spc: 8072 flg: 0x0012 seq: 0x02b5 rec: 0x02
            xid: 0x0007.00f.0000029e
ktubl redo: slt: 15 rci: 0 opc: 11.1 objn: 53676 objd: 53676 tsn: 4
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x00800615.02b5.01
prev ctl max cmt scn: 0x0000.002bedc7 prev tx cmt scn: 0x0000.002beddd
txn start scn: 0xffff.ffffffff logon user: 54 prev brb: 8390161 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100575f hdba: 0x0100575b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)

CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x0100575f OBJ:53676 SCN:0x0000.002c4353 SEQ: 2 OP:11.2
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0007.00f.0000029e uba: 0x00800615.02b5.02
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100575f hdba: 0x0100575b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 17
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] 41 32
col 1: [10] 53 45 43 4f 4e 44 20 52 4f 57

CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 159
serial number = 5
transaction name =
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 209Kb in 0.61s => 0.33 Mb/sec
Total physical reads: 4096Kb
Longest record: 1Kb, moves: 0/538 (0%)
Change moves: 249/1038 (23%), moved: 0Mb
Longest LWN: 14Kb, moves: 0/89 (0%), moved: 0Mb
Last redo scn: 0x0000.002c4355 (2900821)

change #1
OP5.2
Update rollback segment header - KTURDH
我的理解是先定位 undo rollback segment

change #2
OP5.1
: Undo block or undo segment header - KTURDB
更新 undo segment header
op: Z
Undo of first (ever) change to the ITL
更新itl 槽
不知道什么意思 DSI309
DO Op code: DRP row dependencies Disabled
KDO（DML操作）：这里KDO是UNDO记录，操作类型是DRP，就是删除相关行
tabn: 0 slot: 0(0x0)
drp 操作需要删除的行

change# 3
OP:11.2
Insert Row Piece
op: F
First change to ITL by this TX. Copy redo to ITL，OP：F
更新itl事务槽
KDO Op code: IRP row dependencies Disabled
--Single Insert Row Piece，行插入操作
redo
slot: 0(0x0) size/delt: 19
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 17
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] 41 32
col 1: [10] 53 45 43 4f 4e 44 20 52 4f 57
oracle 的redo 操作对应insert的数值

CHANGE #4
事务

**********************

得出结论

insert 的时候记录redo的顺序如下

cv1

1：定位回滚段的位置

cv2

1：更新回滚段头

2：更新ITL槽

3：记录undo的redo

cv3

1：更新itl事务槽