返回小栈
Oracle truncate异常恢复之bbed修复
orastar2020-03-23 14:22:47

一、    概念介绍



当使用truncate语句误删除业务表时,首选使用备份集恢复,没有可用备份集时建议及时将表空read only避免数据块被覆盖,然后选择data unloadbbed工具进行恢复。

本文主要使用bbed工具对truncate表进行异常恢复,根据前文描述,bbed进行truncate恢复,需恢复以下信息,

1、数据字典恢复:

obj$(dataobj#)tab$(dataobj#)

2、段头块恢复:

segment header(High HighwaterLow HighwaterExtent MapAuxillaryMap#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 HighwaterLow HighwaterExtent MapAuxillaryMap#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、文章涉及内容,请勿生产环境模拟。



纸上得来终觉浅,绝知此事要躬行。--陆游



感谢您的阅读,如果您觉得有所收获,也欢迎把文章分享给您的朋友。




 


0
0