一、Oracle 官方对reod内容的解释：
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-the-redo-log.html#GUID-4625A35C-EF8A-4A9E-8D19-829C1A665A34
二、实验过程
1、创建新表和索引
oracle@ray115 admin]$ sqlplus raysuen/"*******"@suenpdb
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 16 20:03:59 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create table t1(tid number,tname varchar2(20));
Table created.
SQL> create index t1_id on t1(tid);
Index created.
2、确定当前的日志
SQL> alter system checkpoint;
System altered.
SQL> set linesize 500
col group# for 999
col mb for 9999
col member for a60
col thread# for 999
col archived for a10
select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP#
group by a.group#,SQL> SQL> SQL> SQL> SQL> SQL> 2 a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#;
GROUP# MB MEMBER THREAD# SEQUENCE# MEMBERS ARCHIVED STATUS FIRST_TIME NEXT_TIME
------ ----- ------------------------------------------------------------ ------- ---------- ---------- ---------- ---------------- ------------------ ------------------
1 200 /data/app/oracle/oradata/raysuen/redo01.log 1 37 1 NO CURRENT 20-DEC-23
2 200 /data/app/oracle/oradata/raysuen/redo02.log 1 35 1 NO INACTIVE 20-DEC-23 20-DEC-23
3 200 /data/app/oracle/oradata/raysuen/redo03.log 1 36 1 NO INACTIVE 20-DEC-23 20-DEC-23
3、切换当前redo到新日志
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
4、插入数据
SQL> insert into t1 values(1,'aa');
1 row created.
SQL> commit;
Commit complete.
5、DUMP日志文件
SQL> set linesize 500
col group# for 999
col mb for 9999
col member for a60
col thread# for 999
col archived for a10
select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP#
group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#;SQL> SQL> SQL> SQL> SQL> SQL> 2
GROUP# MB MEMBER THREAD# SEQUENCE# MEMBERS ARCHIVED STATUS FIRST_TIME NEXT_TIME
------ ----- ------------------------------------------------------------ ------- ---------- ---------- ---------- ---------------- ------------------ ------------------
1 200 /data/app/oracle/oradata/raysuen/redo01.log 1 37 1 NO INACTIVE 20-DEC-23 16-JAN-24
2 200 /data/app/oracle/oradata/raysuen/redo02.log 1 38 1 NO CURRENT 16-JAN-24
3 200 /data/app/oracle/oradata/raysuen/redo03.log 1 36 1 NO INACTIVE 20-DEC-23 20-DEC-23
SQL> alter system dump logfile '/data/app/oracle/oradata/raysuen/redo02.log';
System altered.
SQL> select distinct sid from v$mystat;
SID
----------
66
SQL> select value from v$diag_info where name like 'Default%';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc
6、查看DUMP日志文件内容
#查看设计的对象ID
SQL> col owner for a20
SQL> select owner,object_id,object_name,object_type from dba_objects where owner='RAYSUEN';
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
-------------------- ---------- -------------------- --------------------
RAYSUEN 76318 T1 TABLE
RAYSUEN 76319 T1_ID INDEX
vi /data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc
REDO RECORD - Thread:1 RBA: 0x000026.0000000f.001c LEN: 0x009c VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10
CHANGE #1 CON_ID:3 TYP:1 CLS:4 AFN:11 DBA:0x02c00082 OBJ:76318 SCN:0x000000000013ddab SEQ:1 OP:13.17 ENC:0 RBL:0 FLG:0x0000
ktsphfredo - Format Pagetable Segment Header
StartDBA 0x02c00080 nblks: 8 ForceL3 :0 Tsn: 4 objd: 76318
REDO RECORD - Thread:1 RBA: 0x000026.0000000f.00b8 LEN: 0x0060 VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10
CHANGE #1 CON_ID:3 TYP:1 CLS:9 AFN:11 DBA:0x02c00081 OBJ:76318 SCN:0x000000000013ddab SEQ:1 OP:13.19 ENC:0 RBL:0 FLG:0x0000
ktspsfredo - Format Level2 Bitmap Block
ParentDBA: 0x02c00082 Start DBA: 0x02c00080 Number: 1incn: 0
REDO RECORD - Thread:1 RBA: 0x000026.0000000f.0118 LEN: 0x0098 VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10
CHANGE #1 CON_ID:3 TYP:1 CLS:8 AFN:11 DBA:0x02c00080 OBJ:76318 SCN:0x000000000013ddab SEQ:1 OP:13.18 ENC:0 RBL:0 FLG:0x0000
ktspffredo - Format Level1 Bitmap Block
Start DBA of the range: 0x02c00080 Number of Blocks: 8
nbits: 4 inst: 0 nmrk: 3 ParentDBA: 0x02c00081 Offset: 0
REDO RECORD - Thread:1 RBA: 0x000026.0000000f.01b0 LEN: 0x008c VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10
CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:11 DBA:0x02c00082 OBJ:76318 SCN:0x000000000013ddab SEQ:2 OP:13.28 ENC:0 RBL:0 FLG:0x0000
Both the HWMs
Low HWM
Highwater:: 0x02c00083 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x02c00080
High HWM
Highwater:: 0x02c00083 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x02c00080
REDO RECORD - Thread:1 RBA: 0x000026.00000010.00a4 LEN: 0x01a8 VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10
CHANGE #1 CON_ID:3 TYP:0 CLS:31 AFN:10 DBA:0x018000f0 OBJ:4294967295 SCN:0x000000000013ddab SEQ:2 OP:5.2 ENC:0 RBL:0 FLG:0x0000
ktudh redo: slt: 0x000e sqn: 0x00000290 flg: 0x044a siz: 136 fbi: 112
uba: 0x0180636a.0072.01 pxid: 0x0000.000.00000000 pdbid:2607644639
CHANGE #2 CON_ID:3 TYP:1 CLS:32 AFN:10 DBA:0x0180636a OBJ:4294967295 SCN:0x000000000013ddab SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 136 spc: 0 flg: 0x000a seq: 0x0072 rec: 0x01
xid: 0x0008.00e.00000290
ktubl redo: slt: 14 rci: 0 opc: 11.1 [objn: 14 objd: 8 tsn: 0]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x01806369.0072.01
prev ctl max cmt scn: 0x000000000013cbe0 prev tx cmt scn: 0x000000000013cbea
txn start scn: 0xffffffffffffffff logon user: 107 prev brb: 25191271 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0007.001.000002b0 uba: 0x01807cdc.0085.16
flg: C--- lkc: 0 scn: 0x000000000013dd53
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004083a9 hdba: 0x004000c0
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 33(0x21)
CHANGE #3 CON_ID:3 TYP:0 CLS:1 AFN:8 DBA:0x004083a9 OBJ:8 SCN:0x000000000013ddab SEQ:1 OP:11.2 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0008.00e.00000290 uba: 0x0180636a.0072.01
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004083a9 hdba: 0x004000c0
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 33(0x21) size/delt: 29
fb: K-H-FL-- lb: 0x1 cc: 3
curc: 0 comc: 0 pk: 0x004083a9.21 nk: 0x004083a9.21
null: ---
col 0: [ 2] c1 05
col 1: [ 2] c1 0c
col 2: [ 3] c2 02 1f
REDO RECORD - Thread:1 RBA: 0x000026.0000021a.01cc LEN: 0x0150 VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013e1b0 SUBSCN: 1 01/16/2024 20:08:11
CHANGE #1 CON_ID:3 TYP:0 CLS:32 AFN:10 DBA:0x01806368 OBJ:4294967295 SCN:0x000000000013ddab SEQ:2 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 184 spc: 1390 flg: 0x0022 seq: 0x0072 rec: 0x33
xid: 0x0008.016.00000296
ktubu redo: slt: 22 rci: 50 opc: 11.1 objn: 81 objd: 81 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x01806368.0072.31
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00407f46 hdba: 0x004003f8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 78(0x4e) size/delt: 43
fb: --H-FL-- lb: 0x0 cc: 25
null:
01234567890123456789012345678901234567890123456789012345678901234567890123456789
--NNNNNNNNNN--NNN-NN--NN-
col 0: [ 4] c3 08 40 14
col 1: [ 2] c1 0b
col 2: *NULL*
col 3: *NULL*
col 4: *NULL*
col 5: *NULL*
col 6: *NULL*
col 7: *NULL*
col 8: *NULL*
col 9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 2] c1 03
col 13: [ 3] c2 03 38
col 14: *NULL*
col 15: *NULL*
col 16: *NULL*
col 17: [ 1] 80
col 18: *NULL*
col 19: *NULL*
col 20: [ 1] 80
col 21: [ 1] 80
col 22: *NULL*
col 23: *NULL*
col 24: [ 1] 80
CHANGE #2 CON_ID:3 TYP:0 CLS:1 AFN:8 DBA:0x00407f46 OBJ:81 SCN:0x000000000013ddab SEQ:1 OP:11.3 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x01806368.0072.33
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00407f46 hdba: 0x004003f8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 78(0x4e)
REDO RECORD - Thread:1 RBA: 0x000026.0000021d.0178 LEN: 0x009c VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11
CHANGE #1 CON_ID:3 TYP:1 CLS:4 AFN:11 DBA:0x02c0008a OBJ:76319 SCN:0x000000000013e1b1 SEQ:1 OP:13.17 ENC:0 RBL:0 FLG:0x0000
ktsphfredo - Format Pagetable Segment Header
StartDBA 0x02c00088 nblks: 8 ForceL3 :0 Tsn: 4 objd: 76319
REDO RECORD - Thread:1 RBA: 0x000026.0000021e.0024 LEN: 0x0060 VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11
CHANGE #1 CON_ID:3 TYP:1 CLS:9 AFN:11 DBA:0x02c00089 OBJ:76319 SCN:0x000000000013e1b1 SEQ:1 OP:13.19 ENC:0 RBL:0 FLG:0x0000
ktspsfredo - Format Level2 Bitmap Block
ParentDBA: 0x02c0008a Start DBA: 0x02c00088 Number: 1incn: 0
REDO RECORD - Thread:1 RBA: 0x000026.0000021e.0084 LEN: 0x0098 VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11
CHANGE #1 CON_ID:3 TYP:1 CLS:8 AFN:11 DBA:0x02c00088 OBJ:76319 SCN:0x000000000013e1b1 SEQ:1 OP:13.18 ENC:0 RBL:0 FLG:0x0000
ktspffredo - Format Level1 Bitmap Block
Start DBA of the range: 0x02c00088 Number of Blocks: 8
nbits: 2 inst: 0 nmrk: 3 ParentDBA: 0x02c00089 Offset: 0
REDO RECORD - Thread:1 RBA: 0x000026.0000021e.011c LEN: 0x008c VLD: 0x01 CON_UID: 2607644639
SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11
CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:11 DBA:0x02c0008a OBJ:76319 SCN:0x000000000013e1b1 SEQ:2 OP:13.28 ENC:0 RBL:0 FLG:0x0000
Both the HWMs
Low HWM
Highwater:: 0x02c0008b ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x02c00088
High HWM
Highwater:: 0x02c0008b ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
lfdba: 0x02c00088
7、查看DUMP日志文件内所有的Object ID对应的对象名称
[root@ray115 ~]# egrep "OBJ:" /data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc | awk '{for(i=1;i;i++){if(match($i,"obj:"))>
OBJ:10
OBJ:100
OBJ:2
OBJ:4294967295
OBJ:76318
OBJ:76319
OBJ:8
OBJ:81
OBJ:82
OBJ:9
[root@ray115 ~]# egrep "OBJ:" /data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc | awk '{for(i=1;i;i++){if(match($i,"obj:"))>
10,100,2,4294967295,76318,76319,8,81,82,9,
SQL> select owner,object_id,object_name,object_type from dba_objects where object_id in (10,100,2,4294967295,76318,76319,8,81,82,9);
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
-------------------- ---------- ------------------------------ -----------------------
SYS 2 C_OBJ# CLUSTER
SYS 8 C_FILE#_BLOCK# CLUSTER
SYS 9 I_FILE#_BLOCK# INDEX
SYS 10 C_USER# CLUSTER
SYS 81 DEFERRED_STG$ TABLE
SYS 82 I_DEFERRED_STG1 INDEX
SYS 100 SEQ$ TABLE
RAYSUEN 76318 T1 TABLE
RAYSUEN 76319 T1_ID INDEX
9 rows selected.
总结：
1、redo内记录redo record
2、redo内容包含数据对象，及其涉及的索引。
3、redo内容包含对应底层修改的基表。
