1.bbed的配置(省略)
2.介绍
修改的部分
segment header(dataobj#、LHWM、HHWM、extent map、aux map以及extents个数)
tabKaTeX parse error: Expected 'EOF', got '#' at position 9: (dataobj#̲) obj(dataobj#)
可以不改
L1,L2
3.修改dataobj
select obj#,dataobj#,OWNER#,NAME from obj$ where OWNER# in (select user# from user$ where name=‘DEF’) and name=‘TESTB’;
也可以根据logdump看到obj的变化
more /tmp/soft/app/oracle/diag/rdbms/wlh/wlh/trace/wlh_ora_3878.trc
可以看到0x02400082的dataobj#从87378改成了87380
查询块头
select HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME=‘TESTB’ and owner=‘DEF’;
修改Map Header的obj#
set file 9 block 130 offset 272
需要从87380改回87377
修改语句是:
modify /x 51 offset 272
sum apply
verify
手动更改obj$ (值都是老的值) 并刷新buffer_cache和shared_pool
update obj$ set dataobj#=87376 where obj#=87376;
update tab$ set dataobj#=87376 where obj#=87376;
alter system flush buffer_cache;
alter system flush shared_pool;
此时dataobj#更改完毕(下图配错了,意思是对的)
4.恢复段头块HWM(HWM信息可以从redo dump中获取)
根据logdump看到obj的变化
more /tmp/soft/app/oracle/diag/rdbms/wlh/wlh/trace/wlh_ora_3878.trc
high和low相同,所以只列出2个high HWM即可。
Highwater:: 0x0240056c ext#: 24 blk#: 108 ext size: 128
Highwater:: 0x02400083 ext#: 0 blk#: 3 ext size: 8
改回老的
m /x 18 offset 48
m /x 18 offset 92
m /x 6c offset 52
m /x 6c offset 96
m /x 80 offset 56
m /x 80 offset 10
m /x 6c05 offset 60
m /x 4002 offset 62
m /x 6c05 offset 104
m /x 4002 offset 106
sum apply
verify
alter system flush buffer_cache;
alter system flush shared_pool;
找回数据
5.恢复段头块extent map,Auxillary Map以及extent个数(有时候不做也行)
extent map(offset最高到24,总共24+1=25个extent,24extent的block_id是0x2400500,extent size是128个块。)
grep -i "ADD: dba" /tmp/soft/app/oracle/diag/rdbms/wlh/wlh/trace/wlh_ora_3878.trc
ADD: dba:0x2400500 len:128 at offset:24
ADD: dba:0x2400480 len:128 at offset:23
ADD: dba:0x2400400 len:128 at offset:22
ADD: dba:0x2400380 len:128 at offset:21
ADD: dba:0x2400300 len:128 at offset:20
ADD: dba:0x2400280 len:128 at offset:19
ADD: dba:0x2400200 len:128 at offset:18
ADD: dba:0x2400180 len:128 at offset:17
ADD: dba:0x2400100 len:128 at offset:16
ADD: dba:0x24000f8 len:8 at offset:15
ADD: dba:0x24000f0 len:8 at offset:14
ADD: dba:0x24000e8 len:8 at offset:13
ADD: dba:0x24000e0 len:8 at offset:12
ADD: dba:0x24000d8 len:8 at offset:11
ADD: dba:0x24000d0 len:8 at offset:10
ADD: dba:0x24000c8 len:8 at offset:9
ADD: dba:0x24000c0 len:8 at offset:8
ADD: dba:0x24000b8 len:8 at offset:7
ADD: dba:0x24000b0 len:8 at offset:6
ADD: dba:0x24000a8 len:8 at offset:5
ADD: dba:0x24000a0 len:8 at offset:4
ADD: dba:0x2400098 len:8 at offset:3
ADD: dba:0x2400090 len:8 at offset:2
ADD: dba:0x2400088 len:8 at offset:1
——————————————————
建表ext_map,插入上面grep的数据
create table ext_map(a varchar2(2000));
insert into ext_map values('ADD: dba:0x2400500 len:128 at offset:24');
insert into ext_map values('ADD: dba:0x2400480 len:128 at offset:23');
insert into ext_map values('ADD: dba:0x2400400 len:128 at offset:22');
insert into ext_map values('ADD: dba:0x2400380 len:128 at offset:21');
insert into ext_map values('ADD: dba:0x2400300 len:128 at offset:20');
insert into ext_map values('ADD: dba:0x2400280 len:128 at offset:19');
insert into ext_map values('ADD: dba:0x2400200 len:128 at offset:18');
insert into ext_map values('ADD: dba:0x2400180 len:128 at offset:17');
insert into ext_map values('ADD: dba:0x2400100 len:128 at offset:16');
insert into ext_map values('ADD: dba:0x24000f8 len:8 at offset:15');
insert into ext_map values('ADD: dba:0x24000f0 len:8 at offset:14');
insert into ext_map values('ADD: dba:0x24000e8 len:8 at offset:13');
insert into ext_map values('ADD: dba:0x24000e0 len:8 at offset:12');
insert into ext_map values('ADD: dba:0x24000d8 len:8 at offset:11');
insert into ext_map values('ADD: dba:0x24000d0 len:8 at offset:10');
insert into ext_map values('ADD: dba:0x24000c8 len:8 at offset:9');
insert into ext_map values('ADD: dba:0x24000c0 len:8 at offset:8');
insert into ext_map values('ADD: dba:0x24000b8 len:8 at offset:7');
insert into ext_map values('ADD: dba:0x24000b0 len:8 at offset:6');
insert into ext_map values('ADD: dba:0x24000a8 len:8 at offset:5');
insert into ext_map values('ADD: dba:0x24000a0 len:8 at offset:4');
insert into ext_map values('ADD: dba:0x2400098 len:8 at offset:3');
insert into ext_map values('ADD: dba:0x2400090 len:8 at offset:2');
insert into ext_map values('ADD: dba:0x2400088 len:8 at offset:1');
aux map(ext#24的aux map的L1 dba是x02400500,data dba是0x02400502)
grep -i "ADDAXT:" /tmp/soft/app/oracle/diag/rdbms/wlh/wlh/trace/wlh_ora_3878.trc
ADDAXT: offset:24 fdba:x02400500 bdba:0x02400502
ADDAXT: offset:23 fdba:x02400480 bdba:0x02400482
ADDAXT: offset:22 fdba:x02400400 bdba:0x02400402
ADDAXT: offset:21 fdba:x02400380 bdba:0x02400382
ADDAXT: offset:20 fdba:x02400300 bdba:0x02400302
ADDAXT: offset:19 fdba:x02400280 bdba:0x02400282
ADDAXT: offset:18 fdba:x02400200 bdba:0x02400202
ADDAXT: offset:17 fdba:x02400180 bdba:0x02400182
ADDAXT: offset:16 fdba:x02400100 bdba:0x02400102
ADDAXT: offset:15 fdba:x024000f0 bdba:0x024000f8
ADDAXT: offset:14 fdba:x024000f0 bdba:0x024000f1
ADDAXT: offset:13 fdba:x024000e0 bdba:0x024000e8
ADDAXT: offset:12 fdba:x024000e0 bdba:0x024000e1
ADDAXT: offset:11 fdba:x024000d0 bdba:0x024000d8
ADDAXT: offset:10 fdba:x024000d0 bdba:0x024000d1
ADDAXT: offset:9 fdba:x024000c0 bdba:0x024000c8
ADDAXT: offset:8 fdba:x024000c0 bdba:0x024000c1
ADDAXT: offset:7 fdba:x024000b0 bdba:0x024000b8
ADDAXT: offset:6 fdba:x024000b0 bdba:0x024000b1
ADDAXT: offset:5 fdba:x024000a0 bdba:0x024000a8
ADDAXT: offset:4 fdba:x024000a0 bdba:0x024000a1
ADDAXT: offset:3 fdba:x02400090 bdba:0x02400098
ADDAXT: offset:2 fdba:x02400090 bdba:0x02400091
ADDAXT: offset:1 fdba:x02400080 bdba:0x02400088
————————————————————————
建表aux_map,插入上面grep的数据
create table aux_map(a varchar2(2000));
insert into aux_map values('ADDAXT: offset:24 fdba:x02400500 bdba:0x02400502');
insert into aux_map values('ADDAXT: offset:23 fdba:x02400480 bdba:0x02400482');
insert into aux_map values('ADDAXT: offset:22 fdba:x02400400 bdba:0x02400402');
insert into aux_map values('ADDAXT: offset:21 fdba:x02400380 bdba:0x02400382');
insert into aux_map values('ADDAXT: offset:20 fdba:x02400300 bdba:0x02400302');
insert into aux_map values('ADDAXT: offset:19 fdba:x02400280 bdba:0x02400282');
insert into aux_map values('ADDAXT: offset:18 fdba:x02400200 bdba:0x02400202');
insert into aux_map values('ADDAXT: offset:17 fdba:x02400180 bdba:0x02400182');
insert into aux_map values('ADDAXT: offset:16 fdba:x02400100 bdba:0x02400102');
insert into aux_map values('ADDAXT: offset:15 fdba:x024000f0 bdba:0x024000f8');
insert into aux_map values('ADDAXT: offset:14 fdba:x024000f0 bdba:0x024000f1');
insert into aux_map values('ADDAXT: offset:13 fdba:x024000e0 bdba:0x024000e8');
insert into aux_map values('ADDAXT: offset:12 fdba:x024000e0 bdba:0x024000e1');
insert into aux_map values('ADDAXT: offset:11 fdba:x024000d0 bdba:0x024000d8');
insert into aux_map values('ADDAXT: offset:10 fdba:x024000d0 bdba:0x024000d1');
insert into aux_map values('ADDAXT: offset:9 fdba:x024000c0 bdba:0x024000c8');
insert into aux_map values('ADDAXT: offset:8 fdba:x024000c0 bdba:0x024000c1');
insert into aux_map values('ADDAXT: offset:7 fdba:x024000b0 bdba:0x024000b8');
insert into aux_map values('ADDAXT: offset:6 fdba:x024000b0 bdba:0x024000b1');
insert into aux_map values('ADDAXT: offset:5 fdba:x024000a0 bdba:0x024000a8');
insert into aux_map values('ADDAXT: offset:4 fdba:x024000a0 bdba:0x024000a1');
insert into aux_map values('ADDAXT: offset:3 fdba:x02400090 bdba:0x02400098');
insert into aux_map values('ADDAXT: offset:2 fdba:x02400090 bdba:0x02400091');
insert into aux_map values('ADDAXT: offset:1 fdba:x02400080 bdba:0x02400088');
——————————————————————————————————————
19是16进制=25(10进制)(grep出来数+1)
m /x 19 offset 36---------------total extents
m /x 19 offset 264-------------Map Header的extents
上面的ext_map和aux_map插入完后,生成bbed语句
aux_map:
with aa as (
select replace(regexp_substr(a,'+',1,3),'fdba','') ext#,
(replace(regexp_substr(a,'+',1,3),'fdba','')-1)*8+2744 offset,
trim(replace(regexp_substr(a,'+',1,4),'bdba','')) l1,
trim(regexp_substr(a,'+',1,5)) data
from aux_map)
select 'm /x '||a||' offset '||offset||chr(10)||'m /x '||b||' offset '||to_char(offset+2)||chr(10)||'m /x '||c||' offset '||to_char(offset+4)||chr(10)||'m /x '||d||' offset '||to_char(offset+6) from
(select ext#,to_number(offset) offset,substr(l1,-2,2)||substr(l1,-4,2) a,substr(l1,-6,2)||substr(l1,-8,2) b,substr(data,-2,2)||substr(data,-4,2) c,substr(data,-6,2)||substr(data,-8,2) d
from aa);
ext_map:
with aa as (
select trim(replace(replace(regexp_substr(a,'+',1,3),'len',''),'x','x0')) block_id,
(regexp_substr(a,'+',1,5)-1)*8+288 offset,
lpad(trim((to_char((replace(regexp_substr(a,'+',1,4),'at offset','')),'xxxx'))),2,0) blocks,
regexp_substr(a,'+',1,5) ext#
from ext_map)
select 'm /x '||a||' offset '||offset||chr(10)||'m /x '||b||' offset '||to_char(offset+2)||chr(10)||'m /x '||blocks||' offset '||to_char(offset+4) from
(select ext#,to_number(offset) offset,substr(block_id,-2,2)||substr(block_id,-4,2) a,substr(block_id,-6,2)||substr(block_id,-8,2) b,blocks
from aa);
6.重建表或者导出数据
重启下数据库:或者刷新下buffer_cache和shared_pool(建议刷一下,别重启了)
alter system flush buffer_cache;
alter system flush shared_pool;
查询正常,但是插入会报错:
可以重建表或者导出数据
create table newkktestk as select * from kktestk;
结束。
部分理论内容可移步本人其他文章学习
oracle assm(自动段管理)为什么可以提高插入并发(散记)
oracle assm(自动段管理)与mssm(手动段管理)的区别
参考:
李翔宇