记录下使用 bbed 修改 undo block 的过程，以及修改 undo block 的注意事项
在回滚块损坏的情况下，通过 bbed 修改 undo block 跳过损坏的 undo block，手动模拟事务提交，跳过事务回滚阶段
环境
1
2
3
4
5
6
7
8
select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
1
2
3
4
5
6
create table test_undo (id number, name varchar2(20));
insert into test_undo values (1, 'test1');
insert into test_undo values (2, 'test2');
insert into test_undo values (3, 'test3');
insert into test_undo values (4, 'test4');
commit;
1
update test_undo set name = 'test1-1' where id = 1;
在不修改 undo block 的情况下，直接修改数据文件的 block 状态，使得事务提交，切记不要在 session1 中提交或者执行 ddl 操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
select dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, id, name from test_undo;
FILE_ID | BLOCK_ID | ID | NAME
-------------------------------
5 | 150 | 1 | test1
5 | 150 | 2 | test2
5 | 150 | 3 | test3
5 | 150 | 4 | test4
WXJ@darkdb 9,19922 SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, id, name from test_undo;
FILE_ID BLOCK_ID ID NAME
---------- ---------- ---------- --------------------
5 150 1 test1-1 -- 未提交的数据，block id 没有变化
5 150 2 test2
5 150 3 test3
5 150 4 test4
Elapsed: 00:00:00.00
select dump(id,16), dump(name,16), id, name from test_undo order by id;
DUMP(ID,16) | DUMP(NAME,16) | ID | NAME
------------------------------------------------------------
Typ=2 Len=2: c1,2 | Typ=1 Len=5: 74,65,73,74,31 | 1 | test1
Typ=2 Len=2: c1,3 | Typ=1 Len=5: 74,65,73,74,32 | 2 | test2
Typ=2 Len=2: c1,4 | Typ=1 Len=5: 74,65,73,74,33 | 3 | test3
Typ=2 Len=2: c1,5 | Typ=1 Len=5: 74,65,73,74,34 | 4 | test4
WXJ@darkdb 9,19922 SQL> select dump(id,16) as did, dump(name,16) as dname, id, name from test_undo order by id;
DID DNAME ID NAME
-------------------- ---------------------------------------- ---------- ----------
Typ=2 Len=2: c1,2 Typ=1 Len=7: 74,65,73,74,31,2d,31 1 test1-1
Typ=2 Len=2: c1,3 Typ=1 Len=5: 74,65,73,74,32 2 test2
Typ=2 Len=2: c1,4 Typ=1 Len=5: 74,65,73,74,33 3 test3
Typ=2 Len=2: c1,5 Typ=1 Len=5: 74,65,73,74,34 4 test4
Elapsed: 00:00:00.00
不管是在 session1 还是其他 session 中，查看到的 block id 都是一样的，区别在于 session1 中可以查看到未提交的数据
dump 出 block 150 的内容，切记不要在 session1 中执行，dump 操作是 ddl 操作，会提交事务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
alter system dump datafile 5 block 150;
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
WXJ@darkdb 764,20136 SQL> alter system dump datafile 5 block 150;
System altered.
Elapsed: 00:00:00.00
WXJ@darkdb 764,20136 SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_20136.trc
Elapsed: 00:00:00.02
文件内容不多，直接 cat 显示
大小端翻转的时候 Xid 和 Uba 以及 Scn 的翻转方式不同，根据 dump 文件的小数点分割来进行翻转，不要按照字节来翻转，这样会导致错误
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
┌─[oracle@oracle11g]─[/u01/app/oracle/diag/rdbms/darkdb/darkdb/trace]
└──╼ $ cat /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_20136.trc
Trace file /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_20136.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name: Linux
Node name: oracle11g
Release: 5.4.17-2102.201.3.el7uek.x86_64
Version: #2 SMP Fri Apr 23 09:05:55 PDT 2021
Machine: x86_64
Instance name: darkdb
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 20136, image: oracle@oracle11g (TNS V1-V3)
*** 2025-03-20 14:05:33.113
*** SESSION ID:(764.325) 2025-03-20 14:05:33.113
*** CLIENT ID:() 2025-03-20 14:05:33.113
*** SERVICE NAME:(SYS$USERS) 2025-03-20 14:05:33.113
*** MODULE NAME:(SQL*Plus) 2025-03-20 14:05:33.113
*** ACTION NAME:() 2025-03-20 14:05:33.113
Start dump data blocks tsn: 6 file#:5 minblk 150 maxblk 150 ## 这里显示的 file# 和 block# 正是我们 dump 的 block，确认没有问题在接着往下看，不然白忙活，minblk 和 maxblk 是 dump 的 block 范围
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6 rdba=20971670
BH (0x15afc50c8) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15a9f6000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 96,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x15afca6d0,0x189ebd620] lru: [0x15dff4a38,0x15afc4cd8]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.211a85],[xid: 0x9.3.f07],[uba: 0xc003eb.101.1d],[cls: 0x0.211a85],[sfl: 0x0],[lc: 0x0.211a85]
flags: only_sequential_access
BH (0x15afca618) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15aa82000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 96,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x15afa2d10,0x15afc5180] lru: [0x15afca360,0x15afca0f0]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.211a07],[xid: 0x9.3.f07],[uba: 0xc003eb.101.1d],[cls: 0x0.211a07],[sfl: 0x0],[lc: 0x0.211a07]
flags: only_sequential_access
BH (0x15afa2c58) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15a672000
set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 94,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x15af80288,0x15afca6d0] lru: [0x15afa2e80,0x15afa2c10]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.211a06],[xid: 0x9.3.f07],[uba: 0xc003eb.101.1d],[cls: 0x0.211a06],[sfl: 0x0],[lc: 0x0.211a06]
flags: only_sequential_access
BH (0x15af801d0) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15a2e4000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 93,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x15aff05c0,0x15afa2d10] lru: [0x15af803f8,0x15af80188]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.2119fb],[xid: 0x9.3.f07],[uba: 0xc003eb.101.1d],[cls: 0x0.2119fb],[sfl: 0x0],[lc: 0x0.2119fb]
flags: only_sequential_access
BH (0x15aff0508) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15ae66000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 103,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x15afca598,0x15af80288] lru: [0x15aff0730,0x15aff04c0]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.2119fa],[xid: 0x9.3.f07],[uba: 0xc003eb.101.1d],[cls: 0x0.2119fa],[sfl: 0x0],[lc: 0x0.2119fa]
flags: only_sequential_access
BH (0x15afca4e0) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15aa80000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 96,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x189ebd620,0x15aff05c0] lru: [0x15afca840,0x15dff0868]
ckptq: [NULL] fileq: [NULL] objq: [0x15afcb0f0,0x177f3c1e0] objaq: [0x15afcb100,0x177f3c1d0]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 3
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 6 rdba: 0x01400096 (5/150)
scn: 0x0000.00211a85 seq: 0x01 flg: 0x04 tail: 0x1a850601
frmt: 0x02 chkval: 0xbe10 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F3689BDBA00 to 0x00007F3689BDDA00
7F3689BDBA00 0000A206 01400096 00211A85 04010000 [......@...!.....]
7F3689BDBA10 0000BE10 00000001 00017732 00211A85 [........2w....!.]
7F3689BDBA20 00000000 00320002 01400090 001A0003 [......2...@.....]
7F3689BDBA30 00000F54 00C009CD 001F00DF 00008000 [T...............]
7F3689BDBA40 002118A0 00030009 00000F07 00C003EB [..!.............]
7F3689BDBA50 001D0101 00000001 00000000 00000000 [................]
7F3689BDBA60 00000000 00040100 001AFFFF 1F4C1F5A [............Z.L.]
7F3689BDBA70 00001F4C 1F5A0004 1F741F80 00001F68 [L.....Z...t.h...]
7F3689BDBA80 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
7F3689BDD9B0 00000000 00000000 00000000 022C0000 [..............,.]
7F3689BDD9C0 02C10202 73657407 312D3174 0202002C [.....test1-1,...] ## 这里显示的是修改后的数据
7F3689BDD9D0 740505C1 34747365 0202002C 740504C1 [...test4,......t]
7F3689BDD9E0 33747365 0202002C 740503C1 32747365 [est3,......test2]
7F3689BDD9F0 0202002C 740502C1 31747365 1A850601 [,......test1....] ## 这里显示的是修改前的数据，可以看到修改后的数据和修改前的数据都在 block 中，Oracle 不会主动清理磁盘上的数据，只是在逻辑上标记为删除，然后复用
End dump data blocks tsn: 6 file#: 5 minblk 150 maxblk 150
Block header dump: 0x01400096
Object id on Block? Y
seg/obj: 0x17732 csc: 0x00.211a85 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.01a.00000f54 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
|
V #这里的 C 表示 commit，表示事务已经提交，Lck 是锁的标志，0 表示没有锁，或者确切的说是 0 行被锁。这里的 scn 是 commit 的 scn
前两位 0003 回滚段 undo （v$rollname.name 对应的值就是 undo segment）编号（然后在关联 dba_segment name 获取信息），中间的 01a 是事务表槽号（也可以理解为行号，可以通过后文 dump undo 信息对应的 TRN TBL 信息查看） slot ，后面的 00000f54 事务表序列号，对应的是 wrap# 。序列号每次被覆盖就会加 1 ，用于判断事务是否发生变化（事务槽对应的还是否是同一个事务）
## select name from v$rollname where usn = 3;
## select header_file, header_block, blocks from dba_segments where segment_name = '_SYSSMU3_1723003836$';
## select extent_id, file_id., block_id, blocks from dba_extents where segment_name = '_SYSSMU3_1723003836$';
0x02 0x0009.003.00000f07 0x00c003eb.0101.1d ---- 1 fsc 0x0000.00000000
#这里的----表示没有 commit，我们需要做的就是将这个事务的 commit 标志改为 commit，然后将 scn 改为 commit 的 scn，这样就可以将这个事务置为提交，即便当前会话没有提交，但是其他会话可以看到修改后的数据
## 只要 Itl 存在没有提交的事务，Oracle 每次读取到这个事务槽的时候都会发生回滚 undo，并不需要读取到下面的 block_row_dump 展现的 lb 0x2 没有提交的事务再发生回滚。
## 例如 session2 读取的时候表 test_undo 的时候，session1 还没有提交事务，所以 session2 读取到的数据是 test1，而不是 test1-1，Oracle 就会发生回滚 undo，将 test1-1 回滚为 test1，对于 session2 来说，就是看不到 session1 的修改数据。Oracle 访问和事务无关的三行是，会先读取 CR 块，然后再 CR 块上做 undo，这样就可以保证事务的一致性。是否需要构造 CR 块，是以 Itl 事务槽为准，如果 Itl 事务槽有未提交的事务，就会构造 CR 块，如果没有未提交的事务，就不会构造 CR 块
# 我们需要通过 bbed 的操作，就是将 0x02 对应的 Flag 修改为 C---，Lck 修改为 0 ，然后将 scn 修改为 0x0000.002118a0（比上一个 SCN 大一点就行，不能比当前实例的 SCN 还大），这样就可以将这个事务置为提交，即便当前会话没有提交，但是其他会话可以看到修改后的数据
bdba: 0x01400096
data_block_dump,data header at 0x7f3689bdba64
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7f3689bdba64
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f5a
avsp=0x1f4c
tosp=0x1f4c
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f5a
0x14:pri[1] offs=0x1f80
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f68
block_row_dump:
tab 0, row 0, @0x1f5a
tl: 14 fb: --H-FL-- lb: 0x2 cc: 2 ## lb 是锁的标志，和上文的 Itl 事务槽（0x02)关联，只要 lb 不为 0x0 就表示和其他事务有关联，而且这个事务是未提交的事务，可以把 lb 理解为行锁
col 0: [ 2] c1 02 ## col 0 表示第一列，[ 2] 表示长度为 2，c1 02 表示第一列对应的十六进制的值，select dump(id,16)
col 1: [ 7] 74 65 73 74 31 2d 31 ## col 1 表示第二列，[ 7] 表示长度为 7，74 65 73 74 31 2d 31 表示第二列对应的十六进制的值，select dump(name,16)，这里的值是修改后的值 select dump(test1-1,16)
[
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 74 65 73 74 32
tab 0, row 2, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 5] 74 65 73 74 33
tab 0, row 3, @0x1f68
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [ 5] 74 65 73 74 34
] ## 从这里也可以看出来，目前这个 block 中有 4 行数据，其中第一行的行锁是 0x2，表示和其他事务有关联，其他 3 行的行锁是 0x0，表示没有和其他事务有关联
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 150 maxblk 150
通过上面的理解，我们可以通过 bbed 修改 block 150 的 Itl 事务槽，将未提交的事务置为提交，这样其他 session 就可以看到修改后的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
BBED> set FILENAME '/oradata/darkdb/data01.dbf'
FILENAME /oradata/darkdb/data01.dbf
BBED> set file 5
FILE# 5
BBED> set block 150
BLOCK# 150
BBED> set count 8192
COUNT 8192
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 8192
LOGFILE log.bbd
SPOOL No
BBED>
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 8191 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 3c1d2100 00000104 a9b90000 01000000 32770100 3c1d2100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 09000300 070f0000 eb03c000 01011d00 01000000 00000000 00000000
00000000 00010400 ffff1a00 5a1f4c1f 4c1f0000 04005a1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
## 省略中间的 00000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00002c02
0202c102 07746573 74312d31 2c000202 c1050574 65737434 2c000202 c1040574
65737433 2c000202 c1030574 65737432 2c000202 c1020574 65737431 01063c1d
<32 bytes per line>
因为是修改 Itl 信息就行，所以不需要 dump 所有的 8192 字节，只需要 dump 出 Itl 信息就行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
## Itl Xid Uba Flag Lck Scn/Fsc
## 0x01 0x0003.01a.00000f54[03001a00 540f0000] 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
## 0x02 0x0009.003.00000f07[09000300 070f0000] 0x00c003eb.0101.1d ---- 1 fsc 0x0000.00000000
BBED> set count 512
COUNT 512
BBED>
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 511 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 3c1d2100 00000104 a9b90000 01000000 32770100 3c1d2100
00000000 02003200 90004001 [03001a00 540f0000] cd09c000 df001f00 00800000 ## 注意大小端问题，这里的 540f0000 是 0x00000f54 的大小端，Itl 总是会从 Xid 的位置开始
a0182100 [09000300 070f0000] eb03c000 01011d00 01000000 00000000 00000000 ## 注意大小端问题，这里的 070f0000 是 0x00000f07 的大小端，两个 Itl 事务槽都找到了
00000000 00010400 ffff1a00 5a1f4c1f 4c1f0000 04005a1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
BBED>
## 方便观察，设置下 offset 重新 dump 一下对齐显示
BBED> set offset 0
OFFSET 0
BBED> set count 200
COUNT 200
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 200
LOGFILE log.bbd
SPOOL No
## 这里需要注意有可能存在重复的字符，注意甄别 offset 位置是否正确
BBED> find /x 03001a00
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 44 to 243 Dba:0x01400096
------------------------------------------------------------------------
03001a00 540f0000 cd09c000 df001f00 [00800000] [a0182100] 09000300 070f0000 ## 这里的 00800000 就是 Flag 的 C---，我们需要将 0x02 的 Flag 修改为 C---（00800000），表示事务已经提交。 a0182100 就是 Scn ，后面紧跟的是下一个 Itl 事务槽的 Xid
eb03c000 01011d00 01000000 00000000 00000000 00000000 00010400 ffff1a00
5a1f4c1f 4c1f0000 04005a1f 801f741f 681f0000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED>
## Itl Xid Uba Flag Lck Scn/Fsc
## 0x01 0x0003.01a.00000f54[03001a00 540f0000] 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
## 0x02 0x0009.003.00000f07[09000300 070f0000] 0x00c003eb.0101.1d ---- 1 fsc 0x0000.00000000
## 我们需要修改的是 Flag Lck Scn
## 大小端翻转的时候 Xid 和 Uba 以及 Scn 的翻转方式不同，根据 dump 文件的小数点分割来进行翻转，不要按照字节来翻转，这样会导致错误
## 先找到 0x02 对应的 Flag，通过查找 Uba 的位置，后面挨着的就是 Flag，直接将 C 转换为 16 进制是不行的
BBED> find /x 01011d00
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 80 to 279 Dba:0x01400096
------------------------------------------------------------------------
[01]011d00 [01000000] 00000000 00000000 00000000 00010400 ffff1a00 5a1f4c1f
## 01 表示 offset 80 的位置，两位数字表示一个字节，我们需要修改的 Flag 位置起始点就是 80+4 也就是 [01000000]，修改为 [00800000]，表示事务已经提交
4c1f0000 04005a1f 801f741f 681f0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
## 可以先设置 offset 为 84 ， 然后再 modify ，也可以直接 modify /x 0x000000 offset 84
BBED> set mode edit
MODE Edit
BBED> modify /x 00800000 offset 84
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 84 to 283 Dba:0x01400096
------------------------------------------------------------------------
00800000 00000000 00000000 00000000 00010400 ffff1a00 5a1f4c1f 4c1f0000
04005a1f 801f741f 681f0000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> set offset 80
OFFSET 80
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 80 to 279 Dba:0x01400096
------------------------------------------------------------------------
01011d00 00800000 00000000 00000000 00000000 00010400 ffff1a00 5a1f4c1f
4c1f0000 04005a1f 801f741f 681f0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED>
修改了 Flag 之后，我们需要修改 Scn，Flag 位置后面紧跟的就是 Scn，我们需要将 Scn 修改为比上一个事务的 Scn 大一点的值就行，不要超过当前实例的 Scn
1
2
3
4
5
6
7
8
9
10
11
12
13
## Itl Xid Uba Flag Lck Scn/Fsc
## 0x01 0x0003.01a.00000f54[03001a00 540f0000] 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
## 0x02 0x0009.003.00000f07[09000300 070f0000] 0x00c003eb.0101.1d ---- 1 fsc 0x0000.00000000
## 上一个 Itl 的 Scn 是 0x0000.002118a0，我们需要将 0x02 的 Scn 修改为比 0x0000.002118a0 大一点的值
## a0182100
BBED> show offset
OFFSET 80
## modify /x a0182200 offset 88
BBED> modify /x a0182200 offset 88
BBED-00209: invalid number (a0182200)
## 如果出现以上报错，是因为 Oracle 觉得数字太大了吧，可以拆分为两部分修改，先修改 offset 88 两个字节，然后再修改 offset 90 两个字节
中途有事，测试没有做完，连接断开了，导致之前的 session1 窗口断开，应该自动回滚了，正好接着测试 session2
1
2
3
4
5
6
7
8
9
10
11
12
WXJ@darkdb 9,12627 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1 -- 可以看到这里还是修改前的数据
2 test2
3 test3
4 test4
Elapsed: 00:00:00.00
WXJ@darkdb 9,12627 SQL>
接着查看 对应的 block 信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
set filename '/oradata/darkdb/data01.dbf'
set file 5
set block 150
show all
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> set offset 80
OFFSET 80
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 80 to 591 Dba:0x01400096
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00010400 ffff1a00 4e1f4e1f
4e1f0000 04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
可以看到之前 bbed 修改的内容已经被回滚了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
BBED> set offset 0
OFFSET 0
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 511 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 e3d70000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
## 查看尾部的数据
set count 8192
dump
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00002c00 [0202c102 05746573 74312c02] ## 多出来的部分内容，这里其实有点混淆了，测试用例不太好，应该选择不同的字符作为参考，相同的字符(test1 -> test1-1)太多导致结果无法清晰的查看出来
0202c102 07746573 74312d31 2c000202 c1050574 65737434 2c000202 c1040574
65737433 2c000202 c1030574 65737432 2c000202 c1020574 65737431 01065286
<32 bytes per line>
BBED>
右边窗口为断开前的 data 内容，左边为新的 dump 内容，可以看到也发生了改变
接着修改 Itl 信息测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
BBED> set count 200
COUNT 200
BBED> set offset 0
OFFSET 0
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 200
LOGFILE log.bbd
SPOOL No
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 e3d70000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED>
## 为了保险起见 重新 dump 以下 trace 信息
WXJ@darkdb 9,12627 SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_12627.trc
Elapsed: 00:00:00.03
WXJ@darkdb 9,12627 SQL>
┌─[oracle@oracle11g]─[~]
└──╼ $ vim -R /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_12627.trc
┌─[oracle@oracle11g]─[~] ## 可以看出 Itl 0x01 信息没有变化，但是 0x02 已经全部变为 0 了，test1-1 也还在 block 信息中
15 7FF96AE05A60 00000000 00040100 001AFFFF 1F4E1F4E [............N.N.]
14 7FF96AE05A70 00001F4E 1F4E0004 1F741F80 00001F68 [N.....N...t.h...]
13 7FF96AE05A80 00000000 00000000 00000000 00000000 [................]
12 Repeat 498 times
11 7FF96AE079B0 002C0000 02C10202 73657405 022C3174 [..,......test1,.]
10 7FF96AE079C0 02C10202 73657407 312D3174 0202002C [.....test1-1,...]
9 7FF96AE079D0 740505C1 34747365 0202002C 740504C1 [...test4,......t]
8 7FF96AE079E0 33747365 0202002C 740503C1 32747365 [est3,......test2]
7 7FF96AE079F0 0202002C 740502C1 31747365 86520601 [,......test1..R.]
6 Block header dump: 0x01400096
5 Object id on Block? Y
4 seg/obj: 0x17732 csc: 0x00.2148b6 itc: 2 flg: E typ: 1 - DATA
3 brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0
2 inc: 0 exflg: 0
1 .
101 Itl Xid Uba Flag Lck Scn/Fsc 1 0x01 0x0003.01a.00000f54 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
2 0x02 [0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000] ## 已经不在持有锁
3 bdba: 0x01400096
4 data_block_dump,data header at 0x7ff96ae05a64
5 ===============
6 tsiz: 0x1f98
7 hsiz: 0x1a
8 pbl: 0x7ff96ae05a64
9 76543210
10 flag=--------
11 ntab=1
12 nrow=4
13 frre=-1
14 fsbo=0x1a
15 fseo=0x1f4e
## 修改 Flag
BBED> modify /x 00800000 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 84 to 283 Dba:0x01400096
------------------------------------------------------------------------
00800000 00000000 00000000 00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000
04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> set offset 80
OFFSET 80
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 80 to 279 Dba:0x01400096
------------------------------------------------------------------------
00000000 00800000 00000000 00000000 00000000 00010400 ffff1a00 4e1f4e1f
4e1f0000 04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
## 修改 Scn ，拆分为两部分
# modify /x a018 offset 88
# modify /x 2200 offset 90
BBED> modify /x a018 offset 88
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 88 to 287 Dba:0x01400096
------------------------------------------------------------------------
a0180000 00000000 00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f
801f741f 681f0000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> modify /x 2200 offset 90
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 90 to 289 Dba:0x01400096
------------------------------------------------------------------------
22000000 00000000 00000001 0400ffff 1a004e1f 4e1f4e1f 00000400 4e1f801f
741f681f 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> set offset 88
OFFSET 88
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 88 to 287 Dba:0x01400096
------------------------------------------------------------------------
a0182200 00000000 00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f
801f741f 681f0000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
修改完成记得修改校验和
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
sum apply
## 重新在读取一次 block
set block 150
dump
BBED> sum apply
Check value for File 5, Block 150:
current = 0x4f61, required = 0x4f61
BBED> set block 150
BLOCK# 150
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 614f0000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 [00800000 a0182200] 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
在刷新 cache 和 checkpoint 之前，一定要记得 bbed 重新读取一次 block ，否则 bbed 修改的信息会被覆盖，因为 bbed 修改的是磁盘上的数据，而 cache 中的数据是内存中的数据，刷新 cache 会将内存中的数据写入磁盘，覆盖掉 bbed 修改的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> alter system flush buffer_cache;
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 614f0000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00800000 a0182200 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
SQL> alter system checkpoint;
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 614f0000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00800000 a0182200 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
因为 Itl 0x02 已经释放掉了，所以即便通过 bbed 修改了数据，也无法读取到修改之后的数据了。接下来的测试就直接修改表的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
select * from test_undo;
ID | NAME
----------
1 | test1
2 | test2
3 | test3
4 | test4
## session1 修改数据不提交
WXJ@darkdb 2269,12573 SQL> update test_undo set name = 'AAAA' where id = 2;
1 row updated.
Elapsed: 00:00:00.01
## 因为测试环境的表空间比较小，所以遇到了 ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small 错误
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
## 在 session2 中可以正常查看到表的数据，也不会报错，回滚段也和 System 有关
select * from test_undo;
ID | NAME
----------
1 | test1
2 | test2
3 | test3
4 | test4
可以看到回滚段也会使用 system 表空间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ora undo
INST_ID TABLESPACE_NAME OWNER SEGMENT_NAME STATUS SIZE_M EXTENTS SHRINKS WRAPS OPTSIZE
------- -------------------- -------------------- ------------------------------ ------- ---------- ---------- ---------- ---------- ----------
SYSTEM SYS SYSTEM ONLINE 0 6 0 0
UNDOTBS1 PUBLIC _SYSSMU10_1197734989$ ONLINE 2 4 1 9
UNDOTBS1 PUBLIC _SYSSMU1_3724004606$ ONLINE 2 4 2 6
UNDOTBS1 PUBLIC _SYSSMU2_2996391332$ ONLINE 1 3 3 5
UNDOTBS1 PUBLIC _SYSSMU3_1723003836$ ONLINE 2 4 1 7
UNDOTBS1 PUBLIC _SYSSMU4_1254879796$ ONLINE 1 3 2 14
UNDOTBS1 PUBLIC _SYSSMU5_898567397$ ONLINE 2 4 2 6
UNDOTBS1 PUBLIC _SYSSMU6_1263032392$ ONLINE 1 3 2 6
UNDOTBS1 PUBLIC _SYSSMU7_2070203016$ ONLINE 1 3 2 6
UNDOTBS1 PUBLIC _SYSSMU8_517538920$ ONLINE 2 4 2 9
UNDOTBS1 PUBLIC _SYSSMU9_1650507775$ ONLINE 2 4 1 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
select * from v$rollname;
USN | NAME
---------------------------
0 | SYSTEM
1 | _SYSSMU1_3724004606$
2 | _SYSSMU2_2996391332$
3 | _SYSSMU3_1723003836$
4 | _SYSSMU4_1254879796$
5 | _SYSSMU5_898567397$
6 | _SYSSMU6_1263032392$
7 | _SYSSMU7_2070203016$
8 | _SYSSMU8_517538920$
9 | _SYSSMU9_1650507775$
10 | _SYSSMU10_1197734989$
select * from v$rollstat;
USN | LATCH | EXTENTS | RSSIZE | WRITES | XACTS | GETS | WAITS | OPTSIZE | HWMSIZE | SHRINKS | WRAPS | EXTENDS | AVESHRINK | AVEACTIVE | STATUS | CUREXT | CURBLK
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 | 0 | 6 | 385024 | 5400 | 0 | 419 | 0 | None | 385024 | 0 | 0 | 0 | 0 | 0 | ONLINE | 5 | 3
1 | 1 | 4 | 2220032 | 1298838 | 0 | 2468 | 0 | None | 2220032 | 2 | 6 | 2 | 1048576 | 185372 | ONLINE | 2 | 3
2 | 2 | 3 | 1171456 | 1872382 | 0 | 2526 | 1 | None | 2220032 | 3 | 5 | 2 | 1048576 | 109964 | ONLINE | 2 | 30
3 | 3 | 4 | 2220032 | 2420158 | 0 | 2741 | 0 | None | 3268608 | 1 | 7 | 2 | 2097152 | 244860 | ONLINE | 2 | 9
4 | 4 | 3 | 1171456 | 7979976 | 0 | 4603 | 0 | None | 8511488 | 2 | 14 | 8 | 4194304 | 1254466 | ONLINE | 2 | 76
5 | 0 | 4 | 2220032 | 1826452 | 1 | 2476 | 0 | None | 3268608 | 2 | 6 | 2 | 1572864 | 185372 | ONLINE | 2 | 9
6 | 1 | 3 | 1171456 | 3177522 | 0 | 2899 | 0 | None | 4317184 | 2 | 6 | 3 | 2097152 | 255496 | ONLINE | 1 | 7
7 | 2 | 3 | 1171456 | 2252418 | 0 | 2565 | 1 | None | 3268608 | 2 | 6 | 2 | 1572864 | 173658 | ONLINE | 2 | 27
8 | 3 | 4 | 2220032 | 2260364 | 0 | 2747 | 0 | None | 2220032 | 2 | 9 | 3 | 1048576 | 252795 | ONLINE | 2 | 46
9 | 4 | 4 | 2220032 | 660772 | 0 | 1363 | 0 | None | 2220032 | 1 | 2 | 1 | 1048576 | 18267 | ONLINE | 2 | 63
10 | 0 | 4 | 2220032 | 2146190 | 0 | 2676 | 0 | None | 2220032 | 1 | 9 | 2 | 1048576 | 223628 | ONLINE | 2 | 25
select * from dba_extents where file_id = 3 order by segment_name, extent_id;
OWNER | SEGMENT_NAME | PARTITION_NAME | SEGMENT_TYPE | TABLESPACE_NAME | EXTENT_ID | FILE_ID | BLOCK_ID | BYTES | BLOCKS | RELATIVE_FNO
--------------------------------------------------------------------------------------------------------------------------------------------------
SYS | _SYSSMU10_1197734989$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 272 | 65536 | 8 | 3
SYS | _SYSSMU10_1197734989$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 5896 | 65536 | 8 | 3
SYS | _SYSSMU10_1197734989$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 512 | 1048576 | 128 | 3
SYS | _SYSSMU10_1197734989$ | None | TYPE2 UNDO | UNDOTBS1 | 3 | 3 | 2304 | 1048576 | 128 | 3
SYS | _SYSSMU1_3724004606$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 128 | 65536 | 8 | 3
SYS | _SYSSMU1_3724004606$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 5904 | 65536 | 8 | 3
SYS | _SYSSMU1_3724004606$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 1024 | 1048576 | 128 | 3
SYS | _SYSSMU1_3724004606$ | None | TYPE2 UNDO | UNDOTBS1 | 3 | 3 | 1280 | 1048576 | 128 | 3
SYS | _SYSSMU2_2996391332$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 144 | 65536 | 8 | 3
SYS | _SYSSMU2_2996391332$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 6088 | 65536 | 8 | 3
SYS | _SYSSMU2_2996391332$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 640 | 1048576 | 128 | 3
SYS | _SYSSMU3_1723003836$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 160 | 65536 | 8 | 3
SYS | _SYSSMU3_1723003836$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 6056 | 65536 | 8 | 3
SYS | _SYSSMU3_1723003836$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 768 | 1048576 | 128 | 3
SYS | _SYSSMU3_1723003836$ | None | TYPE2 UNDO | UNDOTBS1 | 3 | 3 | 2176 | 1048576 | 128 | 3
SYS | _SYSSMU4_1254879796$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 176 | 65536 | 8 | 3
SYS | _SYSSMU4_1254879796$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 6064 | 65536 | 8 | 3
SYS | _SYSSMU4_1254879796$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 3712 | 1048576 | 128 | 3
SYS | _SYSSMU5_898567397$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 192 | 65536 | 8 | 3
SYS | _SYSSMU5_898567397$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 200 | 65536 | 8 | 3
SYS | _SYSSMU5_898567397$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 1152 | 1048576 | 128 | 3
SYS | _SYSSMU5_898567397$ | None | TYPE2 UNDO | UNDOTBS1 | 3 | 3 | 1792 | 1048576 | 128 | 3
SYS | _SYSSMU6_1263032392$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 208 | 65536 | 8 | 3
SYS | _SYSSMU6_1263032392$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 216 | 65536 | 8 | 3
SYS | _SYSSMU6_1263032392$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 3968 | 1048576 | 128 | 3
SYS | _SYSSMU7_2070203016$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 224 | 65536 | 8 | 3
SYS | _SYSSMU7_2070203016$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 232 | 65536 | 8 | 3
SYS | _SYSSMU7_2070203016$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 3840 | 1048576 | 128 | 3
SYS | _SYSSMU8_517538920$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 240 | 65536 | 8 | 3
SYS | _SYSSMU8_517538920$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 5888 | 65536 | 8 | 3
SYS | _SYSSMU8_517538920$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 384 | 1048576 | 128 | 3
SYS | _SYSSMU8_517538920$ | None | TYPE2 UNDO | UNDOTBS1 | 3 | 3 | 2560 | 1048576 | 128 | 3
SYS | _SYSSMU9_1650507775$ | None | TYPE2 UNDO | UNDOTBS1 | 0 | 3 | 256 | 65536 | 8 | 3
SYS | _SYSSMU9_1650507775$ | None | TYPE2 UNDO | UNDOTBS1 | 1 | 3 | 264 | 65536 | 8 | 3
SYS | _SYSSMU9_1650507775$ | None | TYPE2 UNDO | UNDOTBS1 | 2 | 3 | 4096 | 1048576 | 128 | 3
SYS | _SYSSMU9_1650507775$ | None | TYPE2 UNDO | UNDOTBS1 | 3 | 3 | 896 | 1048576 | 128 | 3
1
2
3
4
5
6
7
8
9
10
alter database datafile '/oradata/darkdb/system01.dbf' resize 1G;
-- 扩容之后还是无法查询未提交的数据
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
┌─[oracle@oracle11g]─[~]
└──╼ $ ora undo
SP2-0640: Not connected
SP2-0640: Not connected
Enter value for _session:
Elapsed: 00:00:00.00
SESSION_INFO
------------
1523,29106
Elapsed: 00:00:00.01
INST_ID TABLESPACE_NAME OWNER SEGMENT_NAME STATUS SIZE_M EXTENTS SHRINKS WRAPS OPTSIZE
------- -------------------- -------------------- ------------------------------ ------- ---------- ---------- ---------- ---------- ----------
SYSTEM SYS SYSTEM ONLINE 0 6 0 0 ## 这里可以看出 size 还是为 0
UNDOTBS1 PUBLIC _SYSSMU10_1197734989$ ONLINE 2 4 1 9
UNDOTBS1 PUBLIC _SYSSMU1_3724004606$ ONLINE 2 4 2 6
UNDOTBS1 PUBLIC _SYSSMU2_2996391332$ ONLINE 1 3 3 5
UNDOTBS1 PUBLIC _SYSSMU3_1723003836$ ONLINE 2 4 1 7
UNDOTBS1 PUBLIC _SYSSMU4_1254879796$ ONLINE 1 3 2 14
UNDOTBS1 PUBLIC _SYSSMU5_898567397$ ONLINE 2 4 2 6
UNDOTBS1 PUBLIC _SYSSMU6_1263032392$ ONLINE 1 3 2 6
UNDOTBS1 PUBLIC _SYSSMU7_2070203016$ ONLINE 1 3 2 6
UNDOTBS1 PUBLIC _SYSSMU8_517538920$ ONLINE 2 4 2 9
UNDOTBS1 PUBLIC _SYSSMU9_1650507775$ ONLINE 2 4 1 2
Elapsed: 00:00:00.11
后续在做测试验证 undo 和 system 的关系，这次先直接 rollback ，接着测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
WXJ@darkdb 9,12627 SQL> alter database datafile '/oradata/darkdb/system01.dbf' resize 2G;
Database altered.
Elapsed: 00:00:07.74
-- session1 rollback
rollback;
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.01
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
WXJ@darkdb 2269,12573 SQL>
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 test3
4 test4
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> update test_undo set name = 'AAAA' where id = 2;
1 row updated.
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.01
WXJ@darkdb 2269,12573 SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 test3
4 test4
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> update test_undo set name = 'AAAA' where id = 2;
1 row updated.
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.00
WXJ@darkdb 2269,12573 SQL>
尝试继续扩大 system 也不行，rollback 之后退出当前 session1 新开 session 测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
┌─[oracle@oracle11g]─[~]
└──╼ $ swxj
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 21 14:58:25 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Session altered.
Elapsed: 00:00:00.00
SESSION_INFO
------------
2269,29552
Elapsed: 00:00:00.01
WXJ@darkdb 2269,29552 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 test3
4 test4
Elapsed: 00:00:00.00
WXJ@darkdb 2269,29552 SQL> update test_undo set name = 'AAAA' where id = 3;
1 row updated.
Elapsed: 00:00:00.01
WXJ@darkdb 2269,29552 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.01
WXJ@darkdb 2269,29552 SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
WXJ@darkdb 2269,29552 SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.06
WXJ@darkdb 2269,29552 SQL> update test_undo set name = 'AAAA' where id = 3;
1 row updated.
Elapsed: 00:00:00.01
WXJ@darkdb 2269,29552 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Elapsed: 00:00:00.01
WXJ@darkdb 2269,29552 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
WXJ@darkdb 2269,29552 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 AAAA
4 test4
Elapsed: 00:00:00.00
WXJ@darkdb 2269,29552 SQL> update test_undo set name = 'BBBBB' where id = 3;
update test_undo set name = 'BBBBB' where id = 3
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []
出现了 ORA-00600 错误，根据 bad dscn 提示，可以推断出刚才的 Itl Flag 和 Scn 修改有关，尝试回退修改，不过这里也能看出 ORA-01555 需要重新连接才行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
show all
modify /x 00000000 offset 84
modify /x 00000000 offset 88
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 200
LOGFILE log.bbd
SPOOL No
BBED> modify /x 00000000 offset 84
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 84 to 283 Dba:0x01400096
------------------------------------------------------------------------
00000000 a0182200 00000000 00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000
04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> modify /x 00000000 offset 88
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 88 to 287 Dba:0x01400096
------------------------------------------------------------------------
00000000 00000000 00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f
801f741f 681f0000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> set block 150
BLOCK# 150
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 614f0000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED>
Elapsed: 00:00:00.12
WXJ@darkdb 9,12627 SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.05
WXJ@darkdb 9,12627 SQL> alter system checkpoint;
System altered.
Elapsed: 00:00:00.03
WXJ@darkdb 9,12627 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 150)
ORA-01110: data file 5: '/oradata/darkdb/data01.dbf'
WXJ@darkdb 2269,29552 SQL>
WXJ@darkdb 2269,29552 SQL>
WXJ@darkdb 2269,29552 SQL> select * from test_undo;
select * from test_undo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 150)
ORA-01110: data file 5: '/oradata/darkdb/data01.dbf'
Elapsed: 00:00:00.48
WXJ@darkdb 2269,29552 SQL>
## 忘记修改校验和🤣
sum apply
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 614f0000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 150:
current = 0xd7e3, required = 0xd7e3
BBED> set offset 80
OFFSET 80
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 80 to 279 Dba:0x01400096
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00010400 ffff1a00 4e1f4e1f
4e1f0000 04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> set offset 0
OFFSET 0
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 e3d70000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
WXJ@darkdb 9,12627 SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.03
WXJ@darkdb 9,12627 SQL> alter system checkpoint;
System altered.
Elapsed: 00:00:00.03
WXJ@darkdb 9,12627 SQL>
## 修改之后可以接着做测试了。做个测试一波三折😂
WXJ@darkdb 2269,29552 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 test3
4 test4
Elapsed: 00:00:00.00
WXJ@darkdb 2269,29552 SQL>
1
2
3
4
5
6
7
8
9
10
11
update test_undo set name = 'BBBBB' where id = 3;
WXJ@darkdb 2269,29552 SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, id, name from test_undo;
FILE_ID BLOCK_ID ID NAME
---------- ---------- ---------- --------------------
5 150 1 test1
5 150 2 test2
5 150 3 BBBBB
5 150 4 test4
接着 block 150
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 200
LOGFILE log.bbd
SPOOL No
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 e3d70000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
## 查看尾部数据
select dump(id,16), dump(name,16), id, name from test_undo where id = 3;
DUMP(ID,16) | DUMP(NAME,16) | ID | NAME
------------------------------------------------------------
Typ=2 Len=2: c1,4 | Typ=1 Len=5: 74,65,73,74,33 | 3 | test3
select dump(3,16),dump('BBBBB',16) from dual;
DUMP(3,16) | DUMP('BBBBB',16)
------------------------------------------------
Typ=2 Len=2: c1,4 | Typ=96 Len=5: 42,42,42,42,42
BBED> set offset 8000
OFFSET 8000
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 8000 to 8191 Dba:0x01400096
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00002c00 0202c102 05746573 74312c02
0202c102 07746573 74312d31 2c000202 c1050574 65737434 2c000202 c1040574
65737433 2c000202 c1030574 65737432 2c000202 c1020574 65737431 01065286
<32 bytes per line>
1
2
3
4
5
6
7
8
9
10
11
12
13
WXJ@darkdb 9,31450 SQL> alter system dump datafile 5 block 150;
System altered.
Elapsed: 00:00:00.01
WXJ@darkdb 9,31450 SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_31450.trc
Elapsed: 00:00:00.03
WXJ@darkdb 9,31450 SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
buffer tsn: 6 rdba: 0x01400096 (5/150)
scn: 0x0000.0021c1b3 seq: 0x01 flg: 0x04 tail: 0xc1b30601
frmt: 0x02 chkval: 0x2e1e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6ACC81AA00 to 0x00007F6ACC81CA00 ## 从这里的描述信息 dump of memory 可以看出这是内存中的数据，所以上面 bbed 查看磁盘上的 block 的信息没有修改后的数据
7F6ACC81AA00 0000A206 01400096 0021C1B3 04010000 [......@...!.....]
7F6ACC81AA10 00002E1E 00000001 00017732 0021C1B3 [........2w....!.]
7F6ACC81AA20 00000000 00320002 01400090 001A0003 [......2...@.....]
7F6ACC81AA30 00000F54 00C009CD 001F00DF 00008000 [T...............]
7F6ACC81AA40 002118A0 00140007 00000BDC 00C00F2F [..!........./...]
7F6ACC81AA50 001300DB 00000001 00000000 00000000 [................]
7F6ACC81AA60 00000000 00040100 001AFFFF 1F4E1F4E [............N.N.]
7F6ACC81AA70 00001F4E 1F4E0004 1F741F80 00001F68 [N.....N...t.h...]
7F6ACC81AA80 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
7F6ACC81C9B0 002C0000 02C10202 73657405 022C3174 [..,......test1,.]
7F6ACC81C9C0 02C10202 73657407 312D3174 0202002C [.....test1-1,...] ## 上一次修改未提交被回滚的数据
7F6ACC81C9D0 740505C1 34747365 0202022C 420504C1 [...test4,......B]
7F6ACC81C9E0 42424242 0202002C 740503C1 32747365 [BBBB,......test2] ## 本次修改还未提交的数据
7F6ACC81C9F0 0202002C 740502C1 31747365 C1B30601 [,......test1....]
Block header dump: 0x01400096
Object id on Block? Y
seg/obj: 0x17732 csc: 0x00.21c1b3 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0
inc: 0 exflg: 0
.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.01a.00000f54 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
0x02 0x0007.014.00000bdc 0x00c00f2f.00db.13 ---- 1 fsc 0x0000.00000000 ## 修改 Flag Scn
bdba: 0x01400096
data_block_dump,data header at 0x7f6acc81aa64
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7f6acc81aa64
76543210
flag=--------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
set offset 0
2f0fc000db0013
BBED> set offset 0
OFFSET 0
BBED> find /x 2f0fc000db0013
BBED-00209: invalid number (2f0fc000db0013)
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 e3d70000 01000000 32770100 b6482100
00000000 02003200 90004001 [03001a00 540f0000 cd09c000 df001f00 00800000 # 一个 Itl 占用 4*6+2 = 26 个字节
a0182100 0000][0000 00000000 00000000 00000000 00000000 00000000 00000000] # 这里不知道什么 Itl 0x02 没有找到，可能和我之前的一些操作有关，直接尝试构造整个 Itl 试试
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
BBED> set count 300
COUNT 300
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 299 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 52862100 00000104 e3d70000 01000000 32770100 b6482100
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
<32 bytes per line>
BBED>
BBED> find /x db0013
BBED-00212: search string not found
BBED> find /x db00
BBED-00212: search string not found
BBED>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# Itl Xid Uba Flag Lck Scn/Fsc
# 0x01 0x0003.01a.00000f54 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
# 0x02 0x0007.014.00000bdc 0x00c00f2f.00db.13 ---- 1 fsc 0x0000.00000000 ## 修改 Flag Scn
# 根据 Itl 0x01 定位 0x02
BBED> find /x 03001a00
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 44 to 343 Dba:0x01400096
------------------------------------------------------------------------
[03001a00 540f0000 cd09c000 df001f00 00800000 a0182100 0000][0000 00000000
00000000 00000000 00000000 00000000 00000000] 00000000 00010400 ffff1a00
4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
<32 bytes per line>
select dump(3,16),dump('BBBBB',16) from dual;
DUMP(3,16) | DUMP('BBBBB',16)
------------------------------------------------
Typ=2 Len=2: c1,4 | Typ=96 Len=5: 42,42,42,42,42
set offset 0
BBED> find /x 4242
BBED-00212: search string not found
发现 block 150 中没有 Itl 0x02 和 对应的数据，尝试 rollback 重启实例之后在进行测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 原始数据
select * from test_undo;
ID | NAME
----------
1 | test1
2 | test2
3 | BBBBB
4 | test4
-- session1 修改数据
update test_undo set name = 'CCCCC' where id = 4;
WXJ@darkdb 2269,3403 SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, id, name from test_undo;
FILE_ID BLOCK_ID ID NAME
---------- ---------- ---------- --------------------
5 150 1 test1
5 150 2 test2
5 150 3 BBBBB
5 150 4 CCCCC
Elapsed: 00:00:00.02
WXJ@darkdb 2269,3403 SQL>
-- 其他 session dump block 150
alter system dump datafile 5 block 150;
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
┌─[oracle@oracle11g]─[~]
└──╼ $ vim -R /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_3634.trc
┌─[oracle@oracle11g]─[~]
└──╼ $
┌─[oracle@oracle11g]─[~]
└──╼ $
┌─[oracle@oracle11g]─[~]
└──╼ $ cat /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_3634.trc
Trace file /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_3634.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name: Linux
Node name: oracle11g
Release: 5.4.17-2102.201.3.el7uek.x86_64
Version: #2 SMP Fri Apr 23 09:05:55 PDT 2021
Machine: x86_64
Instance name: darkdb
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 3634, image: oracle@oracle11g (TNS V1-V3)
*** 2025-03-23 09:00:50.301
*** SESSION ID:(764.11) 2025-03-23 09:00:50.301
*** CLIENT ID:() 2025-03-23 09:00:50.301
*** SERVICE NAME:(SYS$USERS) 2025-03-23 09:00:50.301
*** MODULE NAME:(SQL*Plus) 2025-03-23 09:00:50.301
*** ACTION NAME:() 2025-03-23 09:00:50.301
Start dump data blocks tsn: 6 file#:5 minblk 150 maxblk 150
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6 rdba=20971670
BH (0x15efac5e8) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15e76e000
set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 117,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x160ff8f78,0x189ebd620] lru: [0x15efac810,0x15efac5a0]
obj-flags: object_ckpt_list
ckptq: [0x131ff7798,0x1873443e0] fileq: [0x187344480,0x187344480] objq: [0x177f27a78,0x177f27a78] objaq: [0x160ff9120,0x177f27a58]
st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 3
flags: buffer_dirty redo_since_read
LRBA: [0x6.77fd4.0] LSCN: [0x0.21f81c] HSCN: [0x0.21f81c] HSUB: [1]
BH (0x160ff8ec0) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x160f48000
set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 117,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x189ebd620,0x15efac6a0] lru: [0x130f89770,0x131ff1e58]
lru-flags: moved_to_tail
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2
cr: [scn: 0x0.21f7e7],[xid: 0xfffe.ffff.0],[uba: 0x0.0.0],[cls: 0x0.21f7e7],[sfl: 0x1],[lc: 0x0.21f1c8]
flags: only_sequential_access
Block dump from disk:
buffer tsn: 6 rdba: 0x01400096 (5/150)
scn: 0x0000.0021f1c8 seq: 0x01 flg: 0x06 tail: 0xf1c80601
frmt: 0x02 chkval: 0xd47f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F8EEBF41A00 to 0x00007F8EEBF43A00
7F8EEBF41A00 0000A206 01400096 0021F1C8 06010000 [......@...!.....]
7F8EEBF41A10 0000D47F 00000001 00017732 0021E83B [........2w..;.!.]
7F8EEBF41A20 00000000 00320002 01400090 001A0003 [......2...@.....]
7F8EEBF41A30 00000F54 00C009CD 001F00DF 00008000 [T...............]
7F8EEBF41A40 002118A0 00140007 00000BDC 00C00F2F [..!........./...]
7F8EEBF41A50 001300DB 00002001 0021F1C8 00000000 [..... ....!.....]
7F8EEBF41A60 00000000 00040100 001AFFFF 1F4E1F4E [............N.N.]
7F8EEBF41A70 00001F4E 1F4E0004 1F741F80 00001F68 [N.....N...t.h...]
7F8EEBF41A80 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
7F8EEBF439B0 002C0000 02C10202 73657405 022C3174 [..,......test1,.]
7F8EEBF439C0 02C10202 73657407 312D3174 0202002C [.....test1-1,...]
7F8EEBF439D0 740505C1 34747365 0202022C 420504C1 [...test4,......B]
7F8EEBF439E0 42424242 0202002C 740503C1 32747365 [BBBB,......test2]
7F8EEBF439F0 0202002C 740502C1 31747365 F1C80601 [,......test1....] ## 这里还看不到第四行修改的 CCCCC
Block header dump: 0x01400096
Object id on Block? Y
seg/obj: 0x17732 csc: 0x00.21e83b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.01a.00000f54 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
0x02 0x0007.014.00000bdc 0x00c00f2f.00db.13 --U- 1 fsc 0x0000.0021f1c8 ## 这次 Flag 的标志不是 ----
bdba: 0x01400096
data_block_dump,data header at 0x7f8eebf41a64
===============
## E:/BaiduSyncdisk/MyselfBase/blog_file/oracle%E6%95%B0%E6%8D%AE%E5%9D%97%E4%B8%AD%E6%95%B0%E6%8D%AE%E5%AD%98%E5%82%A8%EF%BC%88%E6%91%98%E5%BD%95%EF%BC%89_ITPUB%E5%8D%9A%E5%AE%A2.html
tsiz: 0x1f98 ## 十进制 8088 ## Total data area size： 8k 的 block: 8192-20(block head)-24(Transaction Header)-24*2（一个事务条）-4(block tail)=8096(0x1fa0) --事物列表可能大于 2
hsiz: 0x1a ## --data head size
pbl: 0x7f8eebf41a64 ## Pointer to buffer holding the block
76543210
flag=--------
ntab=1 ## 表示存放一张表的数据，当存放 cluster 时，可能出现 ntab > 1
nrow=4 ## 表示现在该 block 内有 4 行数据
frre=-1
fsbo=0x1a ## 表示可以放数据的空间的起始位置（发现该值和 hsiz 保持一至），只是表示从这个位置之后的空间都可以存放业务数据
fseo=0x1f4e ## 表示可以存放数据的 end 位置：8014 ， Oracle 是从尾部开始存储数据的，所以 fseo 位置其实是业务数据的开始位置
avsp=0x1f4e
tosp=0x1f4e
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f4e ## 0x1f4e = 8014 != 0x1f80 + tl : 12 = 8064 + 12 = 8076 = 0x1f8c ## 这里第一行的数据在尾部 fseo 的位置，是因为之前第一行的数据发生了修改，提交之后数据保存在了 block 的尾部
0x14:pri[1] offs=0x1f80 ## 0x1f74 + tl : 12 = 8052 + 12 = 8064 = 0x1f80
0x16:pri[2] offs=0x1f74 ## 0x1f68 + tl : 12 = 8040 + 12 = 8052 = 0x1f74
0x18:pri[3] offs=0x1f68 ## fseo: 0x1f4e + tl: 12 = 8026 = 8014 + 12 = 0x1f5a != 0x1f68 这里数据对不上，有可能是因为本次测试修改的数据就是第四行数据，本来是在 block 尾部的，但是因为修改所以位置变了，而且此时还未提交数据，等试验结束提交之后再次检查
block_row_dump:
tab 0, row 0, @0x1f4e
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 74 65 73 74 31
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 74 65 73 74 32
tab 0, row 2, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 5] 42 42 42 42 42
tab 0, row 3, @0x1f68
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [ 5] 74 65 73 74 34
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 150 maxblk 150
1
2
3
4
5
6
WXJ@darkdb 1519,3395 SQL> select dump('CCCCC',16) from dual;
DUMP('CCCCC',16)
----------------------------
Typ=96 Len=5: 43,43,43,43,43
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 8192
LOGFILE log.bbd
SPOOL No
BBED> dump
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00002c00 0202c102 05746573 74312c02
0202c102 07746573 74312d31 2c010202 c10505[43 43434343] 2c020202 c1040542 ## 这里也已经可以看见修改之后的数据了/脏块
42424242 2c000202 c1030574 65737432 2c000202 c1020574 65737431 01061cf8
<32 bytes per line>
再次通过 bbed 修改 Itl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
BBED> set filename '/oradata/darkdb/data01.dbf'
FILENAME /oradata/darkdb/data01.dbf
BBED> set file 5
FILE# 5
BBED> set block 150
BLOCK# 150
BBED> set count 300
COUNT 300
BBED> show all
FILE# 5
BLOCK# 150
OFFSET 0
DBA 0x01400096 (20971670 5,150)
FILENAME /oradata/darkdb/data01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.bbed
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 300
LOGFILE log.bbd
SPOOL No
BBED>
# Itl Xid Uba Flag Lck Scn/Fsc
# 0x01 0x0003.01a.00000f54 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
# 0x02 0x0007.014.00000bdc 0x00c00f2f.00db.13 --U- 1 fsc 0x0000.0021f1c8 ## 这次 Flag 的标志不是 ----
find /x db0013
BBED> find /x db0013
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 80 to 379 Dba:0x01400096
------------------------------------------------------------------------
[db001300] 0120[0000 c8f12100] 00000000 00000000 00010400 ffff1a00 4e1f4e1f ## Uba 和 Scn 的位置，中间部分就是 Flag，这次 Scn 已经比之前的大，所以不需要修改
4e1f0000 04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
## 查询当前实例的 scn
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
2229042
select dump(2229042,16) from dual;
DUMP(2229042,16)
--------------------------
Typ=2 Len=5: c4,3,17,5b,2b ## Itl Scn 信息已经比当前实例的 Scn 小
## 修改 Itl 信息
set mode edit
modify /x 0800 offset 84
BBED> set mode edit
MODE Edit
BBED> modify /x 0800 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 84 to 383 Dba:0x01400096
------------------------------------------------------------------------
08000000 c8f12100 00000000 00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000
04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
<32 bytes per line>
BBED> set offset 0
OFFSET 0
BBED> set block 150
BLOCK# 150
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 299 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 1cf82100 00000104 38320000 01000000 32770100 3be82100
00000000 02003200 90004001 04001300 880c0000 2406c000 00012d00 01000000
00000000 07001400 dc0b0000 2f0fc000 db001300 [08000000] c8f12100 00000000 ## 修改成功
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
<32 bytes per line>
## 校验和
BBED> sum
Check value for File 5, Block 150:
current = 0x3238, required = 0x1231
BBED> sum apply
Check value for File 5, Block 150:
current = 0x1231, required = 0x1231
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 299 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 1cf82100 00000104 31120000 01000000 32770100 3be82100
00000000 02003200 90004001 04001300 880c0000 2406c000 00012d00 01000000
00000000 07001400 dc0b0000 2f0fc000 db001300 08000000 c8f12100 00000000
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
<32 bytes per line>
刷新 buffer cache
1
2
3
4
5
6
7
8
9
10
11
12
13
14
alter system flush buffer_cache;
alter system checkpoint;
## session2 还是不能查看到修改之后的数据
WXJ@darkdb 1519,3395 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 BBBBB
4 test4
Elapsed: 00:00:00.00
session1 不提交事务，重启实例
1
session2> shutdown immediate;
重启之后还是无法查看修改之后的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
WXJ@darkdb 10,8567 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 BBBBB
4 test4
Elapsed: 00:00:00.02
-- 检查 bbed dump 内容对应的 Flag 可以看到也发生了改变
BBED> set filename '/oradata/darkdb/data01.dbf'
FILENAME /oradata/darkdb/data01.dbf
BBED> set file 5
FILE# 5
BBED> set block 150
BLOCK# 150
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 511 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 50052200 00000104 46490000 01000000 32770100 0f052200
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 07001400 dc0b0000 2f0fc000 db001300 [00a00000] c7012200 00000000 -- 这里 Flag 已经发生了改变
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
-- 查看尾部数据，发现对应的脏块也已经没有了
set count 8192
BBED> dump ## 已经没有 43434343
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00002c00 0202c102 05746573 74312c02
0202c102 07746573 74312d31 2c000202 c1050574 65737434 2c000202 c1040542
42424242 2c000202 c1030574 65737432 2c000202 c1020574 65737431 01065005
<32 bytes per line>
看来只能修改 undo 块了，修改几次 block 没有效果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select * from test_undo;
ID | NAME
----------
1 | test1
2 | test2
3 | BBBBB
4 | test4
update test_undo set name = 'DDDDD' where id = 4;
WXJ@darkdb 2269,4450 SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, id, name from test_undo;
FILE_ID BLOCK_ID ID NAME
---------- ---------- ---------- --------------------
5 150 1 test1
5 150 2 test2
5 150 3 BBBBB
5 150 4 DDDDD ## session1
select dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, id, name from test_undo;
FILE_ID | BLOCK_ID | ID | NAME
-------------------------------
5 | 150 | 1 | test1
5 | 150 | 2 | test2
5 | 150 | 3 | BBBBB
5 | 150 | 4 | test4 ## session2
alter system dump datafile 5 block 150;
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
/u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_4685.trc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
┌─[oracle@oracle11g]─[~]
└──╼ $ cat /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_4685.trc
Trace file /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_4685.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name: Linux
Node name: oracle11g
Release: 5.4.17-2102.201.3.el7uek.x86_64
Version: #2 SMP Fri Apr 23 09:05:55 PDT 2021
Machine: x86_64
Instance name: darkdb
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 4685, image: oracle@oracle11g (TNS V1-V3)
*** 2025-03-26 10:30:34.706
*** SESSION ID:(2278.39) 2025-03-26 10:30:34.706
*** CLIENT ID:() 2025-03-26 10:30:34.706
*** SERVICE NAME:(SYS$USERS) 2025-03-26 10:30:34.706
*** MODULE NAME:(SQL*Plus) 2025-03-26 10:30:34.706
*** ACTION NAME:() 2025-03-26 10:30:34.706
Start dump data blocks tsn: 6 file#:5 minblk 150 maxblk 150
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6 rdba=20971670
BH (0x15efdf408) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15eca6000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 80,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x15ef6ea38,0x189ebd620] lru: [0x15efdf630,0x15efdf3c0]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.2223bb],[xid: 0x8.19.c42],[uba: 0xc00740.de.1f],[cls: 0x0.2223bb],[sfl: 0x40],[lc: 0x0.220550]
flags: only_sequential_access
BH (0x15ef6e980) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15e118000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 84,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x15ef6eb70,0x15efdf4c0] lru: [0x131fb8630,0x15ef6e938]
obj-flags: object_ckpt_list
ckptq: [0x15ef6e368,0x187556f48] fileq: [0x187556fe8,0x187556fe8] objq: [0x177eb8db8,0x177eb8db8] objaq: [0x131fb8668,0x177eb8d98]
st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 3
flags: buffer_dirty redo_since_read
LRBA: [0x6.84dcd.0] LSCN: [0x0.2223d6] HSCN: [0x0.2223d6] HSUB: [1]
BH (0x15ef6eab8) file#: 5 rdba: 0x01400096 (5/150) class: 1 ba: 0x15e11a000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 84,28
dbwrid: 0 obj: 96050 objn: 96050 tsn: 6 afn: 5 hint: f
hash: [0x189ebd620,0x15ef6ea38] lru: [0x15ef71c68,0x15ef7cd20]
lru-flags: moved_to_tail
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2
cr: [scn: 0x0.2223b5],[xid: 0xfffe.ffff.0],[uba: 0x0.0.0],[cls: 0x0.2223b5],[sfl: 0x1],[lc: 0x0.220550]
flags: only_sequential_access
Block dump from disk:
buffer tsn: 6 rdba: 0x01400096 (5/150)
scn: 0x0000.00220550 seq: 0x01 flg: 0x04 tail: 0x05500601
frmt: 0x02 chkval: 0x4946 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F387EEE3A00 to 0x00007F387EEE5A00
7F387EEE3A00 0000A206 01400096 00220550 04010000 [......@.P.".....]
7F387EEE3A10 00004946 00000001 00017732 0022050F [FI......2w....".]
7F387EEE3A20 00000000 00320002 01400090 001A0003 [......2...@.....]
7F387EEE3A30 00000F54 00C009CD 001F00DF 00008000 [T...............]
7F387EEE3A40 002118A0 00140007 00000BDC 00C00F2F [..!........./...]
7F387EEE3A50 001300DB 0000A000 002201C7 00000000 [..........".....]
7F387EEE3A60 00000000 00040100 001AFFFF 1F4E1F4E [............N.N.]
7F387EEE3A70 00001F4E 1F4E0004 1F741F80 00001F68 [N.....N...t.h...]
7F387EEE3A80 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
7F387EEE59B0 002C0000 02C10202 73657405 022C3174 [..,......test1,.]
7F387EEE59C0 02C10202 73657407 312D3174 0202002C [.....test1-1,...]
7F387EEE59D0 740505C1 34747365 0202002C 420504C1 [...test4,......B]
7F387EEE59E0 42424242 0202002C 740503C1 32747365 [BBBB,......test2]
7F387EEE59F0 0202002C 740502C1 31747365 05500601 [,......test1..P.]
Block header dump: 0x01400096
Object id on Block? Y
seg/obj: 0x17732 csc: 0x00.22050f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.01a.00000f54 0x00c009cd.00df.1f C--- 0 scn 0x0000.002118a0
0x02 0x0007.014.00000bdc 0x00c00f2f.00db.13 C-U- 0 scn 0x0000.002201c7 ## 这里的 Flag 又变成了 C-U- ，什么鬼，但是在 session2 中还是没有看到修改之后的数据，难道这里是因为我使用的 session3 dump trace 的时候触发了自动提交？
bdba: 0x01400096
data_block_dump,data header at 0x7f387eee3a64
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7f387eee3a64
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f4e
avsp=0x1f4e
tosp=0x1f4e
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f4e
0x14:pri[1] offs=0x1f80
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f68
block_row_dump:
tab 0, row 0, @0x1f4e
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 74 65 73 74 31
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 74 65 73 74 32
tab 0, row 2, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 5] 42 42 42 42 42
tab 0, row 3, @0x1f68
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [ 5] 74 65 73 74 34
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 150 maxblk 150
在 dump trace 的时候发现 Flag 和之前的不一样，重新通过 bbed 查看 block 150 ，发现可以查看到 Flag 和修改之后的数据，这又是什么鬼🤣，重新尝试 bbed 修改 block 试试，也有可能是因为这张表已经测试了好几次
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
WXJ@darkdb 1521,4443 SQL> select dump('DDDDD',16) from dual;
DUMP('DDDDD',16)
----------------------------
Typ=96 Len=5: 44,44,44,44,44
set filename '/oradata/darkdb/data01.dbf'
set file 5
set block 150
set count 8192
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 511 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 f9252200 00000104 b38a0000 01000000 32770100 f9252200
00000000 02003200 90004001 08001900 420c0000 4007c000 de001f00 01000000
00000000 07001400 dc0b0000 2f0fc000 [db001300] 00a00000 c7012200 00000000 ## Uba 的位置
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00002c00 0202c102 05746573 74312c02
0202c102 07746573 74312d31 2c010202 c105[05][44 44444444] 2c000202 c1040542 ## 05 长度，44 对应的 D 十六进制
42424242 2c000202 c1030574 65737432 2c000202 c1020574 65737431 0106f925
<32 bytes per line>
set offset 0
set count 200
set mode edit
find /x db0013
BBED> find /x db0013
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 80 to 279 Dba:0x01400096
------------------------------------------------------------------------
db001300 00a00000 c7012200 00000000 00000000 00010400 ffff1a00 4e1f4e1f
4e1f0000 04004e1f 801f741f 681f0000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
<32 bytes per line>
modify /x 0800 offset 84
sum apply
set block 150
set offset 0
dump
刷新 buffer cache
1
2
3
4
5
6
7
8
9
10
11
12
-- 尝试多次 bbed 修改，不管是新的会话还是原有的会话都不能查看到修改之后的数据
select * from test_undo;
ID | NAME
----------
1 | test1
2 | test2
3 | BBBBB
4 | test4
-- 不管是先 checkpoint 还是 flush buffer cache 都不能查看到修改之后的数据
alter system checkpoint; -- 此时重新 bbed 查看 block 150 可以看到修改的值又被还原了
alter system flush buffer_cache; -- 此时重新 bbed 查看 block 150 发现修改的值被还原，经过测试发现 flush buffer 并不会覆盖 bbed 修改的值，只有 checkpoint 才会。checkpoint 会刷脏，写入磁盘，flush buffer 只是将内存中的数据清理掉
继续修改 undo 块
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 0x02 0x0007.014.00000bdc 0x00c00f2f.00db.13 C-U- 0 scn 0x0000.002201c7
select * from v$rollname;
USN | NAME
---------------------------
0 | SYSTEM
1 | _SYSSMU1_3724004606$
2 | _SYSSMU2_2996391332$
3 | _SYSSMU3_1723003836$
4 | _SYSSMU4_1254879796$
5 | _SYSSMU5_898567397$
6 | _SYSSMU6_1263032392$
7 | _SYSSMU7_2070203016$ -- 0x0007 对应的 undo segment 编号
8 | _SYSSMU8_517538920$
9 | _SYSSMU9_1650507775$
10 | _SYSSMU10_1197734989$
select header_file, header_block, blocks from dba_segments where segment_name = (select name from v$rollname where usn = 7);
HEADER_FILE | HEADER_BLOCK | BLOCKS
-----------------------------------
3 | 224 | 400
select extent_id, file_id, block_id, blocks from dba_extents where segment_name = (select name from v$rollname where usn = 7);
EXTENT_ID | FILE_ID | BLOCK_ID | BLOCKS
---------------------------------------
0 | 3 | 224 | 8
1 | 3 | 232 | 8
2 | 3 | 3328 | 128
3 | 3 | 512 | 128
4 | 3 | 3840 | 128
-- dump trace undo segment
alter system dump datafile 3 block 224;
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
/u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_4443.trc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
┌─[oracle@oracle11g]─[~]
└──╼ $ cat /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_4443.trc
Trace file /u01/app/oracle/diag/rdbms/darkdb/darkdb/trace/darkdb_ora_4443.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name: Linux
Node name: oracle11g
Release: 5.4.17-2102.201.3.el7uek.x86_64
Version: #2 SMP Fri Apr 23 09:05:55 PDT 2021
Machine: x86_64
Instance name: darkdb
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 4443, image: oracle@oracle11g (TNS V1-V3)
*** 2025-03-26 15:41:06.810
*** SESSION ID:(1521.11) 2025-03-26 15:41:06.810
*** CLIENT ID:() 2025-03-26 15:41:06.810
*** SERVICE NAME:(SYS$USERS) 2025-03-26 15:41:06.810
*** MODULE NAME:(SQL*Plus) 2025-03-26 15:41:06.810
*** ACTION NAME:() 2025-03-26 15:41:06.810
Start dump data blocks tsn: 2 file#:3 minblk 224 maxblk 224
Block dump from cache:
Dump of buffer cache at level 4 for tsn=2 rdba=12583136
BH (0x15dfcb248) file#: 3 rdba: 0x00c000e0 (3/224) class: 29 ba: 0x15da96000
set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 208,28
dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
hash: [0x15ef81608,0x1898c4020] lru: [0x15efa2730,0x15dfbc6c8]
obj-flags: object_ckpt_list
ckptq: [0x157f6f6e8,0x15bf85240] fileq: [0x187344440,0x15bf7ac90] objq: [0x15bf7ad98,0x15bf7a2a0] objaq: [0x15efa2768,0x15dfbc700]
st: XCURRENT md: NULL fpin: 'ktuwh41: ktucloUsMinScn' tch: 92
flags: buffer_dirty block_written_once redo_since_read
LRBA: [0x6.8f8a7.0] LSCN: [0x0.225916] HSCN: [0x0.22592d] HSUB: [1]
BH (0x15ef81550) file#: 3 rdba: 0x00c000e0 (3/224) class: 29 ba: 0x15e304000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 204,28
dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
hash: [0x15efa3e20,0x15dfcb300] lru: [0x15dfa67c8,0x15ef6a9d8]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktuwh05: ktugct' tch: 0 lfb: 33
flags:
BH (0x15efa3d68) file#: 3 rdba: 0x00c000e0 (3/224) class: 29 ba: 0x15e68e000
set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 208,28
dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
hash: [0x131fd2458,0x15ef81608] lru: [0x15dfcfec8,0x15dfbc590]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktuwh41: ktucloUsMinScn' tch: 0 lfb: 33
flags:
BH (0x131fd23a0) file#: 3 rdba: 0x00c000e0 (3/224) class: 29 ba: 0x131b50000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 204,28
dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
hash: [0x1898c4020,0x15efa3e20] lru: [0x15df8f410,0x131fd25c8]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktuwh72: ktugus:ktuswr1' tch: 0 lfb: 33
flags:
Block dump from disk:
buffer tsn: 2 rdba: 0x00c000e0 (3/224)
scn: 0x0000.0022410b seq: 0x01 flg: 0x04 tail: 0x410b2601
frmt: 0x02 chkval: 0x0de8 type: 0x26=KTU SMU HEADER BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F190A795A00 to 0x00007F190A797A00
7F190A795A00 0000A226 00C000E0 0022410B 04010000 [&........A".....]
7F190A795A10 00000DE8 00000000 00000000 00000000 [................]
7F190A795A20 00000000 00000005 0000018F 00000FF0 [................]
7F190A795A30 00000002 0000002B 00000080 00C00D2B [....+.......+...]
7F190A795A40 00000000 00000002 00000000 00000000 [................]
7F190A795A50 00000000 00000000 00000000 00000005 [................]
7F190A795A60 00000000 00000000 40000000 00C000E1 [...........@....]
7F190A795A70 00000007 00C000E8 00000008 00C00D00 [................]
7F190A795A80 00000080 00C00200 00000080 00C00F00 [................]
7F190A795A90 00000080 00000000 00000000 00000000 [................]
7F190A795AA0 00000000 00000000 00000000 00000000 [................]
Repeat 245 times
7F190A796A00 00000000 67E37BC2 67E37BC2 67E37BC2 [.....{.g.{.g.{.g]
7F190A796A10 67E36777 67E37BC2 67CAE719 67CAE719 [wg.g.{.g...g...g]
7F190A796A20 67CAE719 67CAE719 67CAE719 67CAE719 [...g...g...g...g]
Repeat 20 times
7F190A796B70 67CAE719 67321E8F 67321E8F 67321E8F [...g..2g..2g..2g]
7F190A796B80 67321E8F 67321E8F 67321E8F 67321E8F [..2g..2g..2g..2g]
7F190A796B90 67321E8F 67321E8F 67321E8F 00000000 [..2g..2g..2g....]
7F190A796BA0 00000000 00000000 00000000 00000000 [................]
Repeat 98 times
7F190A7971D0 002234E0 00000000 00C00D2A 001700E2 [.4".....*.......]
7F190A7971E0 00E20001 00000002 00000000 00040005 [................]
7F190A7971F0 0001B000 00000068 7FFFFFFE 00C00D2A [....h.......*...]
7F190A797200 001700E2 13E40002 00C00D2B 002800E2 [........+.....(.]
7F190A797210 0D3A0002 00000000 000700E2 098C0002 [..:.............]
7F190A797220 00000000 000100DB 1F840002 00000000 [................]
7F190A797230 00000000 00000000 00000C23 00C00D2A [........#...*...]
7F190A797240 00224105 00000000 00040009 00000000 [.A".............]
7F190A797250 00000000 00000000 00000001 67E3AAAC [...............g]
7F190A797260 00000B9B 00C00D24 00223967 00000000 [....$...g9".....]
7F190A797270 00100009 00000000 00000000 00000000 [................]
7F190A797280 00000002 67E39806 00000C2B 00C00D20 [.......g+... ...]
7F190A797290 00223A1B 00000000 000E0009 00000000 [.:".............]
7F190A7972A0 00000000 00000000 00000001 67E39931 [............1..g]
7F190A7972B0 00000C31 00C00D2A 00223F17 00000000 [1...*....?".....]
7F190A7972C0 00120009 00000000 00000000 00000000 [................]
7F190A7972D0 00000003 67E3A5FC 00000C30 00C00D2A [.......g0...*...]
7F190A7972E0 0022410B 00000000 FFFF0009 00000000 [.A".............]
7F190A7972F0 00000000 00000000 00000001 67E3AAAC [...............g]
7F190A797300 00000BCA 00C00D23 002234F0 00000000 [....#....4".....]
7F190A797310 001A0009 00000000 00000000 00000000 [................]
7F190A797320 00000001 67E38C50 00000BF0 00C00D2A [....P..g....*...]
7F190A797330 00223F95 00000000 00170009 00000000 [.?".............]
7F190A797340 00000000 00000000 00000001 67E3A728 [............(..g]
7F190A797350 00000C31 00C00D20 00223EE3 00000000 [1... ....>".....]
7F190A797360 00200009 00000000 00000000 00000000 [.. .............]
7F190A797370 00000001 67E3A5FB 00000BF3 00C00D2A [.......g....*...]
7F190A797380 002240EB 00000000 001C0009 00000000 [.@".............]
7F190A797390 00000000 00000000 00000001 67E3AAAC [...............g]
7F190A7973A0 00000C2F 00C00D21 0022369E 00000000 [/...!....6".....]
7F190A7973B0 000B0009 00000000 00000000 00000000 [................]
7F190A7973C0 00000001 67E39100 00000BF0 00C00D2A [.......g....*...]
7F190A7973D0 002240E3 00000000 00080009 00000000 [.@".............]
7F190A7973E0 00000000 00000000 00000001 67E3AAAC [...............g]
7F190A7973F0 00000C0A 00C00D21 0022381E 00000000 [....!....8".....]
7F190A797400 000C0009 00000000 00000000 00000000 [................]
7F190A797410 00000001 67E3953C 00000C11 00C00D23 [....<..g....#...]
7F190A797420 002238BF 00000000 00210009 00000000 [.8".......!.....]
7F190A797430 00000000 00000000 00000001 67E396DC [...............g]
7F190A797440 00000C30 00C00D28 00223F16 00000000 [0...(....?".....]
7F190A797450 00030009 00000000 00000000 00000000 [................]
7F190A797460 00000003 67E3A5FC 00000B92 00C00D20 [.......g.... ...]
7F190A797470 00223A27 00000000 001F0009 00000000 [':".............]
7F190A797480 00000000 00000000 00000001 67E39931 [............1..g]
7F190A797490 00000C1F 00C00D20 00223CFC 00000000 [.... ....<".....]
7F190A7974A0 00070009 00000000 00000000 00000000 [................]
7F190A7974B0 00000001 67E3A112 00000BD8 00C00D24 [.......g....$...]
7F190A7974C0 00223975 00000000 00160009 00000000 [u9".............]
7F190A7974D0 00000000 00000000 00000001 67E39806 [...............g]
7F190A7974E0 00000BAE 00C00D23 002235C0 00000000 [....#....5".....]
7F190A7974F0 00090009 00000000 00000000 00000000 [................]
7F190A797500 00000001 67E38EA8 00000C2C 00C00D2B [.......g,...+...]
7F190A797510 00223F18 00000000 001E0009 00000000 [.?".............]
7F190A797520 00000000 00000000 00000003 67E3A5FC [...............g]
7F190A797530 00000C0D 00C00D20 00223A18 00000000 [.... ....:".....]
7F190A797540 00020009 00000000 00000000 00000000 [................]
7F190A797550 00000001 67E39931 00000BEC 00C00D24 [....1..g....$...]
7F190A797560 002239FB 00000000 001B0009 00000000 [.9".............]
7F190A797570 00000000 00000000 00000001 67E39931 [............1..g]
7F190A797580 00000B93 00C00D20 00223C23 00000000 [.... ...#<".....]
7F190A797590 000F0009 00000000 00000000 00000000 [................]
7F190A7975A0 00000001 67E39EB6 00000C30 00C00D24 [.......g0...$...]
7F190A7975B0 002239E8 00000000 00140009 00000000 [.9".............]
7F190A7975C0 00000000 00000000 00000001 67E39931 [............1..g]
7F190A7975D0 00000C2F 00C00D2A 00223FF2 00000000 [/...*....?".....]
7F190A7975E0 001D0009 00000000 00000000 00000000 [................]
7F190A7975F0 00000001 67E3A81E 00000BB3 00C00D26 [.......g....&...]
7F190A797600 00223F15 00000000 000D0009 00000000 [.?".............]
7F190A797610 00000000 00000000 00000001 67E3A5FC [...............g]
7F190A797620 00000B90 00C00D2A 002240FF 00000000 [....*....@".....]
7F190A797630 00000009 00000000 00000000 00000000 [................]
7F190A797640 00000001 67E3AAAC 00000BA6 00C00D21 [.......g....!...]
7F190A797650 002235BF 00000000 00110009 00000000 [.5".............]
7F190A797660 00000000 00000000 00000001 67E38EA8 [...............g]
7F190A797670 00000BCC 00C00D24 00223A09 00000000 [....$....:".....]
7F190A797680 00130009 00000000 00000000 00000000 [................]
7F190A797690 00000001 67E39931 00000C30 00C00D2A [....1..g0...*...]
7F190A7976A0 002240F3 00000000 00190009 00000000 [.@".............]
7F190A7976B0 00000000 00000000 00000001 67E3AAAC [...............g]
7F190A7976C0 00000B8F 00C00D2A 002240D8 00000000 [....*....@".....]
7F190A7976D0 000A0009 00000000 00000000 00000000 [................]
7F190A7976E0 00000001 67E3AAAC 00000C2F 00C00D2A [.......g/...*...]
7F190A7976F0 00223F26 00000000 00060009 00000000 [&?".............]
7F190A797700 00000000 00000000 00000001 67E3A5FC [...............g]
7F190A797710 00000B99 00C00D20 00223BD2 00000000 [.... ....;".....]
7F190A797720 00150009 00000000 00000000 00000000 [................]
7F190A797730 00000001 67E39DE1 00000C13 00C00D20 [.......g.... ...]
7F190A797740 00223EFB 00000000 00180009 00000000 [.>".............]
7F190A797750 00000000 00000000 00000001 67E3A5FB [...............g]
7F190A797760 00000C0F 00C00D23 0022392C 00000000 [....#...,9".....]
7F190A797770 00010009 00000000 00000000 00000000 [................]
7F190A797780 00000001 67E39805 00000007 00000000 [.......g........]
7F190A797790 00000000 00000000 00000000 00000000 [................]
Repeat 36 times
7F190A7979E0 00000000 00000100 00C70040 00400000 [........@.....@.]
7F190A7979F0 010000C7 02C10280 2911C203 410B2601 [...........).&.A]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 5 #blocks: 399
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c00d2b ext#: 2 blk#: 43 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 5 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c000e1 length: 7
0x00c000e8 length: 8
0x00c00d00 length: 128
0x00c00200 length: 128
0x00c00f00 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1742961602
Extent Number:1 Commit Time: 1742961602
Extent Number:2 Commit Time: 1742961602
Extent Number:3 Commit Time: 1742956407
Extent Number:4 Commit Time: 1742961602
TRN CTL:: seq: 0x00e2 chd: 0x0005 ctl: 0x0004 inc: 0x00000000 nfb: 0x0002
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c00d2a.00e2.17 scn: 0x0000.002234e0
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c00d2a.00e2.17 ext: 0x2 spc: 0x13e4
uba: 0x00c00d2b.00e2.28 ext: 0x2 spc: 0xd3a
uba: 0x00000000.00e2.07 ext: 0x2 spc: 0x98c
uba: 0x00000000.00db.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL:: ## 事务表
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt（cmt 的值不能为 0 ）
## 9 对应的是提交标志 ##（对于未提交的事务，表示事务开始时的 scn，需要修改为提交时的 scn）
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0c23 0x0004 0x0000.00224105 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742973612
0x01 9 0x00 0x0b9b 0x0010 0x0000.00223967 0x00c00d24 0x0000.000.00000000 0x00000002 0x00000000 1742968838
0x02 9 0x00 0x0c2b 0x000e 0x0000.00223a1b 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742969137
0x03 9 0x00 0x0c31 0x0012 0x0000.00223f17 0x00c00d2a 0x0000.000.00000000 0x00000003 0x00000000 1742972412
0x04 9 0x00 0x0c30 0xffff 0x0000.0022410b 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742973612
0x05 9 0x00 0x0bca 0x001a 0x0000.002234f0 0x00c00d23 0x0000.000.00000000 0x00000001 0x00000000 1742965840
0x06 9 0x00 0x0bf0 0x0017 0x0000.00223f95 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742972712
0x07 9 0x00 0x0c31 0x0020 0x0000.00223ee3 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742972411
0x08 9 0x00 0x0bf3 0x001c 0x0000.002240eb 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742973612
0x09 9 0x00 0x0c2f 0x000b 0x0000.0022369e 0x00c00d21 0x0000.000.00000000 0x00000001 0x00000000 1742967040
0x0a 9 0x00 0x0bf0 0x0008 0x0000.002240e3 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742973612
0x0b 9 0x00 0x0c0a 0x000c 0x0000.0022381e 0x00c00d21 0x0000.000.00000000 0x00000001 0x00000000 1742968124
0x0c 9 0x00 0x0c11 0x0021 0x0000.002238bf 0x00c00d23 0x0000.000.00000000 0x00000001 0x00000000 1742968540
0x0d 9 0x00 0x0c30 0x0003 0x0000.00223f16 0x00c00d28 0x0000.000.00000000 0x00000003 0x00000000 1742972412
0x0e 9 0x00 0x0b92 0x001f 0x0000.00223a27 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742969137
0x0f 9 0x00 0x0c1f 0x0007 0x0000.00223cfc 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742971154
0x10 9 0x00 0x0bd8 0x0016 0x0000.00223975 0x00c00d24 0x0000.000.00000000 0x00000001 0x00000000 1742968838
0x11 9 0x00 0x0bae 0x0009 0x0000.002235c0 0x00c00d23 0x0000.000.00000000 0x00000001 0x00000000 1742966440
0x12 9 0x00 0x0c2c 0x001e 0x0000.00223f18 0x00c00d2b 0x0000.000.00000000 0x00000003 0x00000000 1742972412
0x13 9 0x00 0x0c0d 0x0002 0x0000.00223a18 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742969137
0x14 9 0x00 0x0bec 0x001b 0x0000.002239fb 0x00c00d24 0x0000.000.00000000 0x00000001 0x00000000 1742969137 ## Itl 对应的 xid 中的事务表槽号
0x15 9 0x00 0x0b93 0x000f 0x0000.00223c23 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742970550
0x16 9 0x00 0x0c30 0x0014 0x0000.002239e8 0x00c00d24 0x0000.000.00000000 0x00000001 0x00000000 1742969137
0x17 9 0x00 0x0c2f 0x001d 0x0000.00223ff2 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742972958
0x18 9 0x00 0x0bb3 0x000d 0x0000.00223f15 0x00c00d26 0x0000.000.00000000 0x00000001 0x00000000 1742972412
0x19 9 0x00 0x0b90 0x0000 0x0000.002240ff 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742973612
0x1a 9 0x00 0x0ba6 0x0011 0x0000.002235bf 0x00c00d21 0x0000.000.00000000 0x00000001 0x00000000 1742966440
0x1b 9 0x00 0x0bcc 0x0013 0x0000.00223a09 0x00c00d24 0x0000.000.00000000 0x00000001 0x00000000 1742969137
0x1c 9 0x00 0x0c30 0x0019 0x0000.002240f3 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742973612
0x1d 9 0x00 0x0b8f 0x000a 0x0000.002240d8 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742973612
0x1e 9 0x00 0x0c2f 0x0006 0x0000.00223f26 0x00c00d2a 0x0000.000.00000000 0x00000001 0x00000000 1742972412
0x1f 9 0x00 0x0b99 0x0015 0x0000.00223bd2 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742970337
0x20 9 0x00 0x0c13 0x0018 0x0000.00223efb 0x00c00d20 0x0000.000.00000000 0x00000001 0x00000000 1742972411
0x21 9 0x00 0x0c0f 0x0001 0x0000.0022392c 0x00c00d23 0x0000.000.00000000 0x00000001 0x00000000 1742968837 ## 这里的 0x21 表示事务表中最多可以存放的事务数量，可以理解为这个回滚段活动事务（没有提交的事务）最多同时存在的数量，十进制就是 33
EXT TRN CTL::
usn: 7
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x7f1900000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000
0x0b 0x00000000 0x00000000 0x00000000 0x00000000
0x0c 0x00000000 0x00000000 0x00000000 0x00000000
0x0d 0x00000000 0x00000000 0x00000000 0x00000000
0x0e 0x00000000 0x00000000 0x00000000 0x00000000
0x0f 0x00000000 0x00000000 0x00000000 0x00000000
0x10 0x00000000 0x00000000 0x00000000 0x00000000
0x11 0x00000000 0x00000000 0x00000000 0x00000000
0x12 0x00000000 0x00000000 0x00000000 0x00000000
0x13 0x00000000 0x00000000 0x00000000 0x00000000
0x14 0x00000000 0x00000000 0x00000000 0x00000000
0x15 0x00000000 0x00000000 0x00000000 0x00000000
0x16 0x00000000 0x00000000 0x00000000 0x00000000
0x17 0x00000000 0x00000000 0x00000000 0x00000000
0x18 0x00000000 0x00000000 0x00000000 0x00000000
0x19 0x00000000 0x00000000 0x00000000 0x00000000
0x1a 0x00000000 0x00000000 0x00000000 0x00000000
0x1b 0x00000000 0x00000000 0x00000000 0x00000000
0x1c 0x00000000 0x00000000 0x00000000 0x00000000
0x1d 0x00000000 0x00000000 0x00000000 0x00000000
0x1e 0x00000000 0x00000000 0x00000000 0x00000000
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000
End dump data blocks tsn: 2 file#: 3 minblk 224 maxblk 224
bbed 修改 undo block
1
2
3
4
5
6
7
8
9
10
11
12
13
14
set filename '/oradata/darkdb/undotbs01.dbf'
set file 3
set block 224
set count 8192
BBED> find /x bf3922
BBED-00212: search string not found
BBED> find /x 183a22
BBED-00212: search string not found
BBED>
这里还是无法正常寻找到对应的信息，可能是 block 被多次修改，尝试重新创建表测试，先停止数据库然后尝试修改 block 试试
1
2
SQL> shutdown immediate
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
set filename '/oradata/darkdb/data01.dbf'
set file 5
set block 150
set count 8192
dump
---
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 8191 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 84812200 00000104 fb100000 01000000 32770100 84812200
00000000 02003200 90004001 08001900 420c0000 4007c000 de001f00 01000000
00000000 07001400 dc0b0000 2f0fc000 db001300 [00a00000] [f23f2200] 00000000 ## 这里对比上文的信息，发现此处 scn 的信息发生了变化，难道是因为之前没有修改 scn 导致无法通过 bbed 修改事务状态，这里停库之后，又发现 scn 不同，先尝试不修改 scn 启动实例测试看看
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00002c00 0202c102 05746573 74312c02
0202c102 07746573 74312d31 2c010202 c10505[44 44444444] 2c000202 c1040542 ## 这里可以看到 block 中已经存在修改之后的数据
42424242 2c000202 c1030574 65737432 2c000202 c1020574 65737431 01068481
<32 bytes per line>
## 按照上文的步骤重新修改 block
set count 200
set mode edit
modify /x 0800 offset 84
sum apply
set block 150
set offset 0
dump
## 此时启动实例
SQL> startup
WXJ@darkdb 2269,2970 SQL>
WXJ@darkdb 2269,2970 SQL> select * from test_undo;
ID NAME
---------- --------------------
1 test1
2 test2
3 BBBBB
4 test4 ## 发现还是没有变化
BBED> dump
File: /oradata/darkdb/data01.dbf (5)
Block: 150 Offsets: 0 to 199 Dba:0x01400096
------------------------------------------------------------------------
06a20000 96004001 ac8b2200 00000104 f0530000 01000000 32770100 84812200
00000000 02003200 90004001 03001a00 540f0000 cd09c000 df001f00 00800000
a0182100 07001400 dc0b0000 2f0fc000 db001300 [08000000] f23f2200 00000000 ## 这里的值没有发生变化
00000000 00010400 ffff1a00 4e1f4e1f 4e1f0000 04004e1f 801f741f 681f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00002c00 0202c102 05746573 74312c02
0202c102 07746573 74312d31 2c000202 c10505[74 65737434] 2c000202 c1040542 ## 这里的数据发生了变化，应该是从 redo
42424242 2c000202 c1030574 65737432 2c000202 c1020574 65737431 0106ac8b
<32 bytes per line>
## 从启动日志中可以看到通过 redo 恢复了 377 data blocks
Successful mount of redo thread 1, with mount id 833686687
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Mar 27 06:20:19 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Started redo scan
Completed redo scan
read 1098 KB redo, 377 data blocks need recovery
Started redo application at
Thread 1: logseq 6, block 640193
Recovery of Online Redo Log: Thread 1 Group 6 Seq 6 Reading mem 0
Mem# 0: /oradata/darkdb/redo06.log
Completed redo application of 0.97MB
Completed crash recovery at
Thread 1: logseq 6, block 642389, scn 2283996
377 data blocks read, 377 data blocks written, 1098 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
虽然本次实验没有成功修改 undo block ，但是观察了 block 的变化和修改，下次直接创建一张新的测试表测试，这次实验因为中间跨度较大，可能细节存在问题