一、 概念介绍
当使用truncate语句误删除业务表时，使用备份集恢复，在没有可用备份集时建议及时将表空read only避免数据块被覆盖，然后选择data unload或bbed工具进行恢复。
本文主要使用bbed工具对truncate表进行异常恢复，根据前文描述，bbed进行truncate恢复，需恢复以下信息，
1、数据字典恢复：
obj$(dataobj#)、tab$(dataobj#)
2、段头块恢复：
segment header(High Highwater、Low Highwater、Extent Map、AuxillaryMap、#extents(Map Header)、obj#)
二、 环境准备
create tablespace hsql datafile '/oradata/epmsn/hsql01.dbf'size 100M autoextend off;
create table hsql.trunc_11(c_char1 char(10),c_char2 char(10))tablespace hsql;
begin
for i in1 .. 10000 loop
insertinto hsql.trunc_11 values(i,'orastar');
end loop;
commit;
end;
/
altersystem flush shared_pool;
altersystem flush shared_pool;
altersystem flush buffer_cache;
altersystem flush buffer_cache;
selectcount(1) from hsql.TRUNC_11;
setlinesize 200 pagesize 200
col ownerfor a10
colsegment_name for a10
selectowner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segmentswhere segment_name='TRUNC_11';
OWNER SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
-------------------- ----------- ------------ ------------------
HSQL TRUNC_11 5 130 TABLE
SQL>
setlinesize 200 pagesize 200
colobject_name for a10
selectowner,object_name,OBJECT_ID,DATA_OBJECT_ID from dba_objects whereobject_name='TRUNC_11';
SQL>
OWNER OBJECT_NAM OBJECT_IDDATA_OBJECT_ID
-------------------- ---------- --------------
HSQL TRUNC_11 88057 88057
***dump段头块信息
altersession set tracefile_identifier='orastar_hdrseg_before_trunc_11';
oradebugsetmypid
altersystem dump datafile 5 block 130;
oradebugclose_trace
oradebugtracefile_name
输出结果:
/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113054_orastar_hdrseg_before_trunc_11.trc
******检查使用日志文件,将redo01切换为active
set line200
colMEMBER for a50
colARCHIVED for a10
colSTATUS for a10
selectg.group#,g.thread#,g.SEQUENCE#,g.bytes,g.members,g.ARCHIVED,g.STATUS,memberfrom v$log g,v$logfile f where g.group#=f.group#;
altersystem switch logfile;
******truncate操作
altersession set tracefile_identifier='orastar_10046_trunc_11';
oradebugsetmypid
oradebugevent 10046 trace name context forever,level 12
truncatetable hsql.trunc_11;
oradebugevent 10046 trace name context off
oradebugtracefile_name
生成输出文件：/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113060_orastar_10046_trunc_11.trc
altersystem switch logfile;
altersystem flush shared_pool;
altersystem flush buffer_cache;
******oracledump redo日志
oradebug setmypid
altersystem dump logfile '/oradata/epmsn/redo01.log';
oradebugclose_trace
oradebugtracefile_name
生成输出文件：/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113067.trc
三、变更日志分析
******分析10046日志变更情况
egrep -i -n '^update|^delete|^insert' /u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113060_orastar_10046_trunc_11.trc
[oracle@sourcedb ~]$ egrep -i -n '^update|^delete|^insert'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113060_orastar_10046_trunc_11.trc
11730:update sys.mon_mods$ set inserts = inserts + :ins, updates =updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag),:flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg,timestamp = :time where obj# = :objn
11835:insert into sys.mon_mods$ (obj#, inserts, updates, deletes,timestamp, flags, drop_segments) values (:1, :2, :3, :4, :5, :6, :7)
12041:delete from superobj$ where subobj# = :1
12318:delete from tab_stats$ where obj#=:1
12524:update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35where obj#=:1
12974:update seg$ settype#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13,65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 wherets#=:1 and file#=:2 and block#=:3
13086:update obj$ set obj#=:4,type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13,spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is nulland linkname is null and subname is null
[oracle@sourcedb ~]$
******分析redo日志变更情况
***查找obj$(DATAOBJ#)变更：CDOBJ: new object number
egrep -i 'CDOBJ'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113067.trc
[oracle@sourcedb ~]$ egrep -i 'CDOBJ' /u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113067.trc
CDOBJ: new objectnumber:88057
CDOBJ: new objectnumber:88058
[oracle@sourcedb ~]$
88057 -> 88058
select TO_CHAR('88057', 'XXXXXXXX') ||'-> '|| TO_CHAR('88058','XXXXXXXX') dataobj#_change from dual;
DATAOBJ#_CHANGE
----------------------
157F9-> 157FA
***查找hwm变更信息
egrep -i 'Highwater::|Low HWM|High HWM'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113067.trc
Both the HWMs
Low HWM
Highwater:: 0x014000b0 ext#: 5 blk#: 8 ext size: 8
High HWM
Highwater:: 0x014000b0 ext#: 5 blk#: 8 ext size: 8
Low HWM
Highwater:: 0x01400083 ext#: 0 blk#: 3 ext size: 8
High HWM
Highwater:: 0x01400083 ext#: 0 blk#: 3 ext size: 8
四、判断块是否被覆盖
***Oracle redo dump: epmsn_ora_113067.trc
egrep -i'ADD:' /u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113067.trc|sort-k 5 -t ':' -n|uniq|awk '{print $2 " " $3}'|sed 's/dba:0x//g'|sed's/len://g'|awk 'BEGIN{print "with new_ext as ("}{print "select\47" $1 "\47 as n_dba, \47" $2 "\47 as n_len from dualunion all"}END{print ") \n selectdbms_utility.data_block_address_file(TO_NUMBER(n_dba, \47XXXXXXXX\47))file_id,\n dbms_utility.data_block_address_block(TO_NUMBER(n_dba, \47XXXXXXXX\47))block_id,\n n_len \n from new_ext;"}'
SQL>set serveroutput on
DECLARE
v_fnonumber;
v_fblknonumber;
resnumber := 0;
begin
for i in(
withnew_ext as (
select'1400088' as n_dba, '8' as n_len from dual union all --替换with中内容
select'1400090' as n_dba, '8' as n_len from dual union all
select'1400098' as n_dba, '8' as n_len from dual union all
select'14000a0' as n_dba, '8' as n_len from dual union all
select'00400120' as n_dba, '8' as n_len from dual --后一行去掉union all
)
select dbms_utility.data_block_address_file(TO_NUMBER(n_dba,'XXXXXXXX')) file_id,
dbms_utility.data_block_address_block(TO_NUMBER(n_dba,'XXXXXXXX')) block_id,
n_len
from new_ext
) loop
for j ini.block_id..i.block_id+i.n_len loop
select count(1) into res from dba_extents
wherefile_id=i.file_id and block_id<= i.block_id and (block_id + blocks -1)>=i.block_id;
if(res<> 0) then
dbms_output.put_line('dba'||i.file_id||','||i.block_id||' covered');
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 end if;
endloop;
endloop;
end;
/25 26 27 28
dba1,288 covered
dba1,288 covered
dba1,288 covered
dba1,288 covered
dba1,288 covered
dba1,288 covered
dba1,288 covered
dba1,288 covered
dba1,288 covered
PL/SQLprocedure successfully completed.
SQL>
五、恢复操作
******恢复脚本整理
1、数据字典恢复obj$(dataobj#)、tab$(dataobj#)
2、段头块恢复：segment header(High Highwater、Low Highwater、Extent Map、AuxillaryMap、#extents(Map Header)、obj#、)--以上顺序按offset列出
***1、数据字典恢复
***查询表
SQL> select count(1) from hsql.trunc_11;
COUNT(1)
----------
0
***更新tab$(DATAOBJ#)、obj$(DATAOBJ#)
select obj#,dataobj# from tab$ where obj#=88057;
select obj#,dataobj# from obj$ where obj#=88057;
update tab$ set DATAOBJ#=88057 where obj#=88057;
update obj$ set DATAOBJ#=88057 where obj#=88057;
commit;
***段头块中obj#没有更新，提法对象不存在
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.trunc_11;
SQL> select count(1) from hsql.trunc_11;
select count(1) from hsql.trunc_11
*
ERROR at line 1:
ORA-08103: object no longer exists
SQL>
**ub4 mhobj_ech @272 157F9 -> 157FA
set dba 5,130
d offset 272 count 4
m /x F9 offset 272
SQL> alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.trunc_11;
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
COUNT(1)
----------
0
SQL>
***2、位图信息恢复
***ub4 ext_ech @48 0x00000005
***ub4 blk_ech @52 0x00000008
***ub4 extsize_ech @56 0x00000008
***ub4 hw_ech @60 0x014000b0
***ub4 lhwmext_ech @92 0x00000005
***ub4 lhwmblk_ech @96 0x00000008
***ub4 lhwmextsize_ech @100 0x00000008
***ub4 lhwm_ech @104 0x014000b0
***需恢复高水位信息
Both the HWMs
Low HWM
Highwater:: 0x014000b0 ext#: 5 blk#: 8 ext size: 8
High HWM
Highwater:: 0x014000b0 ext#: 5 blk#: 8 ext size: 8
Low HWM
Highwater:: 0x01400083 ext#: 0 blk#: 3 ext size: 8
High HWM
Highwater:: 0x01400083 ext#: 0 blk#: 3 ext size: 8
set dba 5,130
d offset 48 count 4
d offset 52 count 4
d offset 56 count 4
d offset 60 count 4
d offset 92 count 4
d offset 96 count 4
d offset 100 count 4
d offset 104 count 4
m /x 05 offset 48
m /x 08 offset 52
m /x 08 offset 56
m /x b0 offset 60
m /x 05 offset 92
m /x 08 offset 96
m /x 08 offset 100
m /x b0 offset 104
***需恢复extent个数
***ub4 mhext_echo @264 0x00000006
set dba 5,130
d offset 264 count 4
m /x 06 offset 264
***3、恢复extent map --Oracle redodump file: epmsn_ora_105675.trc
set dba 5,130
BBED> d offset 280 count 400 --检查块内extent map
***以下命令直接生成bbed脚本
egrep -i 'ADD:'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113067.trc|sort -k 5 -t':' -n|uniq|awk '{print $2 " " $5 " " $3}'|awk -F 'len:''{printf("%s %08x\n",$1,$2)}'|sed 's/dba://g'|sed 's/offset://g'|sed's/0x//g'|awk '{printf("%08s %s %s\n",$1,$2,$3)}'|awk '{printsubstr($1,7,2)substr($1,5,2) " " substr($1,3,2)substr($1,1,2) "" $2 " " substr($3,7,2)substr($3,5,2) " " substr($3,3,2)substr($3,3,2)}'|awk'{print "m /x "$1 " offset " 280+8*$3 "\nm /x " $2 " offset "280+8*$3+2 "\nm /x " $4 " offset " 280+8*$3+4 "\nm /x" $5 " offset " 280+8*$3+6}'
m /x 8800 offset 288
m /x 4001 offset 290
m /x 0800 offset 292
m /x 0000 offset 294
m /x 9000 offset 296
m /x 4001 offset 298
m /x 0800 offset 300
m /x 0000 offset 302
m /x 9800 offset 304
m /x 4001 offset 306
m /x 0800 offset 308
m /x 0000 offset 310
m /x a000 offset 312
m /x 4001 offset 314
m /x 0800 offset 316
m /x 0000 offset 318
m /x a800 offset 320
m /x 4001 offset 322
m /x 0800 offset 324
m /x 0000 offset 326
***4、恢复aux map
***以下命令直接生成bbed脚本
BBED> d offset 2736 count 400 --检查块内aux map
egrep -i 'ADDAXT:'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_113067.trc|awk '{print$3 " " $4 " " $2}'|sort -k 4 -t ':' -n|uniq|sed's/fdba:x//g'|sed 's/bdba:0x//g'|sed 's/offset://g'|awk '{printsubstr($1,7,2)substr($1,5,2) " " substr($1,3,2)substr($1,1,2) "" $3 " " substr($2,7,2)substr($2,5,2) " "substr($2,3,2)substr($2,1,2)}'|awk '{print "m /x "$1 " offset" 2736+8*$3 "\nm /x "$2 " offset " 2736+8*$3+2"\nm /x " $4 " offset " 2736+8*$3+4 "\nm /x " $5" offset " 2736+8*$3+6}'
m /x 8000 offset 2744
m /x 4001 offset 2746
m /x 8800 offset 2748
m /x 4001 offset 2750
m /x 9000 offset 2752
m /x 4001 offset 2754
m /x 9100 offset 2756
m /x 4001 offset 2758
m /x 9000 offset 2760
m /x 4001 offset 2762
m /x 9800 offset 2764
m /x 4001 offset 2766
m /x a000 offset 2768
m /x 4001 offset 2770
m /x a100 offset 2772
m /x 4001 offset 2774
m /x a000 offset 2776
m /x 4001 offset 2778
m /x a800 offset 2780
m /x 4001 offset 2782
六、数据库检查恢复状态
SQL> alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.TRUNC_11;
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
COUNT(1)
----------
10000
SQL>
truncate数据恢复完成。
七、 说明
1、以上内容为个人多次测试结果，由于个人原因，如有分析不足之处还请见谅及指正。
2、文章涉及内容，请勿在生产环境模拟。
纸上得来终觉浅，绝知此事要躬行。--陆游
感谢您的阅读，如果您觉得有所收获，也欢迎把文章分享给您的朋友。
