返回小栈
Oracle truncate异常恢复之plsql修复
orastar2020-03-23 14:23:06

一、    概念介绍



本文使用redo中被删除的Extent Map信息,手工重构被truncate前表的extent结构,结合dbms_rowid.rowid_create函数,绕过truncate初始化的assm 位图,实现truncate后业务数据的恢复


         二、    环境准备



create tablespace hsql datafile '/oradata/epmsn/hsql01.dbf' size100M autoextend off;

create table hsql.trunc_11(c_char1 char(10),c_char2 char(10))tablespace hsql;

 

begin

for i in 1 .. 1000000 loop

insert into hsql.trunc_11 values(i,'orastar');

end loop;

commit;

end;

/

 

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;

 

set linesize 200 pagesize 200

col owner for a10

col segment_name for a10

select owner,segment_name,header_file,header_block,SEGMENT_TYPE fromdba_segments where segment_name='TRUNC_11';

 

OWNER         SEGMENT_NA HEADER_FILE HEADER_BLOCKSEGMENT_TYPE

---------- ---------- ----------- ------------ ------------------

HSQL     TRUNC_11          5         130 TABLE

 

SQL>

set linesize 200 pagesize 200

col object_name for a10

select owner,object_name,OBJECT_ID,DATA_OBJECT_ID from dba_objectswhere object_name='TRUNC_11';

SQL>

 

OWNER         OBJECT_NAM OBJECT_ID DATA_OBJECT_ID

---------- ---------- ---------- --------------

HSQL     TRUNC_11   88060           88060

 

 

******检查使用日志文件,redo01切换为active

set line 200

col MEMBER for a50

col ARCHIVED for a10

col STATUS 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#;

 

 

******truncate操作

 

truncate table hsql.trunc_11;

 

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;

 

    三、恢复过程



 

******恢复过程

1、将表空间置为read only

alter tablespace hsql read only;

 

2oracle dump redo日志

oradebug setmypid

alter system dump logfile '/oradata/epmsn/redo01.log';

oradebug close_trace

oradebug tracefile_name

 

生成输出文件:

/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_115081.trc

 

3、查找obj$(DATAOBJ#)变更:CDOBJ: new object number

 

egrep -i 'CDOBJ'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_115081.trc

[oracle@sourcedb trace]$ egrep -i 'CDOBJ'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_115081.trc

   CDOBJ: new objectnumber:88060

   CDOBJ: new object number:88061

[oracle@sourcedb trace]$

 

4、重构extent信息

egrep -i 'ADD:'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_115081.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 ") select n_dba,n_len from new_ext"}'

 

[oracle@sourcedb ~]$ egrep -i 'ADD:'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_115081.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 select dbms_utility.data_block_address_file(TO_NUMBER(n_dba,\47XXXXXXXX\47)) file_id,\ndbms_utility.data_block_address_block(TO_NUMBER(n_dba, \47XXXXXXXX\47))block_id,\n n_len from new_ext"}'

 

with new_ext as (

select '1400088' as n_dba, '8' as n_len from dual union all

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 '14000a8' as n_dba, '8' as n_len from dual union all

select '14000b0' as n_dba, '8' as n_len from dual union all

select '14000b8' as n_dba, '8' as n_len from dual union all

select '14000c0' as n_dba, '8' as n_len from dual union all

select '14000c8' as n_dba, '8' as n_len from dual union all

select '14000d0' as n_dba, '8' as n_len from dual union all

select '14000d8' as n_dba, '8' as n_len from dual union all

select '14000e0' as n_dba, '8' as n_len from dual union all

select '14000e8' as n_dba, '8' as n_len from dual union all

select '14000f0' as n_dba, '8' as n_len from dual union all

select '14000f8' as n_dba, '8' as n_len from dual union all

select '1400100' as n_dba, '128' as n_len from dual union all

select '1400180' as n_dba, '128' as n_len from dual union all

select '1400200' as n_dba, '128' as n_len from dual union all

select '1400280' as n_dba, '128' as n_len from dual union all

select '1400300' as n_dba, '128' as n_len from dual union all

select '1400380' as n_dba, '128' as n_len from dual union all

select '1400400' as n_dba, '128' as n_len from dual union all

select '1400480' as n_dba, '128' as n_len from dual union all

select '1400500' as n_dba, '128' as n_len from dual union all

select '1400580' as n_dba, '128' as n_len from dual union all

select '1400600' as n_dba, '128' as n_len from dual union all

select '1400680' as n_dba, '128' as n_len from dual union all

select '1400700' as n_dba, '128' as n_len from dual union all

select '1400780' as n_dba, '128' as n_len from dual union all

select '1400800' as n_dba, '128' as n_len from dual union all

select '1400880' as n_dba, '128' as n_len from dual union all

select '1400900' as n_dba, '128' as n_len from dual union all

select '1400980' as n_dba, '128' as n_len from dual union all

select '1400a00' as n_dba, '128' as n_len from dual union all

select '1400a80' as n_dba, '128' as n_len from dual union all

select '1400b00' as n_dba, '128' as n_len from dual union all

select '1400b80' as n_dba, '128' as n_len from dual union all

select '1400c00' as n_dba, '128' as n_len from dual union all

select '1400c80' as n_dba, '128' as n_len from dual union all

select '1400d00' as n_dba, '128' as n_len from dual union all

select '1400d80' as n_dba, '128' as n_len from dual union all

select '1400e00' as n_dba, '128' as n_len from dual union all

select '1400e80' as n_dba, '128' as n_len from dual union all

select '1400f00' as n_dba, '128' as n_len from dual

)

 selectdbms_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

 union all                                                                    

select file_id,block_id,'8' from dba_extents wheresegment_name='TRUNC_11';  --将该行添加到最后,并修改表名,

 

[oracle@sourcedb ~]$

 

5、创建恢复对象到其它表空间

create table sys.trunc_11_new as select * from hsql.trunc_11 where1=0;

 

 

6、修改seg$(dataobj#)并测试dbms_rowid.ROWID_CREATE函数

 

*** 修改前测试

 

SQL> select file_id,block_id,'8' len from dba_extents wheresegment_name='TRUNC_11';

 

   FILE_ID   BLOCK_ID L

---------- ---------- -

    5              128   8       --所以file:5,block: 131为第1个业务数据块                 

 

SQL>

 

select * from hsql.trunc_11 whererowid=dbms_rowid.ROWID_CREATE(1,88060,5, 131 ,0);

SQL> select * from hsql.trunc_11 whererowid=dbms_rowid.ROWID_CREATE(1,88060,5, 131 ,0);

select * from hsql.trunc_11 whererowid=dbms_rowid.ROWID_CREATE(1,88060,5, 131 ,0)

                   *

ERROR at line 1:

ORA-01410: invalid ROWID

 

 

SQL>

 

select obj#,dataobj# from obj$ where obj#=88060;

update obj$ set DATAOBJ#=88060 where obj#=88060;

commit;

 

 

***刷新缓存

alter system flush shared_pool;

alter system flush shared_pool;

alter system flush buffer_cache;

alter system flush buffer_cache;

 

***重新查询

SQL> select * from hsql.trunc_11 whererowid=dbms_rowid.ROWID_CREATE(1,88060,5, 131 ,0);

 

C_CHAR1    C_CHAR2

---------- ----------

270        orastar

SQL>

 

7、恢复操作

 

set serveroutput on

DECLARE

   nrows number;

   rid rowid;

   objd number;

   ROWSPERBLOCK number;

  BEGIN

   ROWSPERBLOCK:=1000;

   nrows:=0;

   for i in (

with new_ext as (

select '1400088' as n_dba, '8' as blocks from dual union all

select '1400090' as n_dba, '8' as blocks from dual union all

select '1400098' as n_dba, '8' as blocks from dual union all

select '14000a0' as n_dba, '8' as blocks from dual union all

select '14000a8' as n_dba, '8' as blocks from dual union all

select '14000b0' as n_dba, '8' as blocks from dual union all

select '14000b8' as n_dba, '8' as blocks from dual union all

select '14000c0' as n_dba, '8' as blocks from dual union all

select '14000c8' as n_dba, '8' as blocks from dual union all

select '14000d0' as n_dba, '8' as blocks from dual union all

select '14000d8' as n_dba, '8' as blocks from dual union all

select '14000e0' as n_dba, '8' as blocks from dual union all

select '14000e8' as n_dba, '8' as blocks from dual union all

select '14000f0' as n_dba, '8' as blocks from dual union all

select '14000f8' as n_dba, '8' as blocks from dual union all

select '1400100' as n_dba, '128' as blocks from dual union all

select '1400180' as n_dba, '128' as blocks from dual union all

select '1400200' as n_dba, '128' as blocks from dual union all

select '1400280' as n_dba, '128' as blocks from dual union all

select '1400300' as n_dba, '128' as blocks from dual union all

select '1400380' as n_dba, '128' as blocks from dual union all

select '1400400' as n_dba, '128' as blocks from dual union all

select '1400480' as n_dba, '128' as blocks from dual union all

select '1400500' as n_dba, '128' as blocks from dual union all

select '1400580' as n_dba, '128' as blocks from dual union all

select '1400600' as n_dba, '128' as blocks from dual union all

select '1400680' as n_dba, '128' as blocks from dual union all

select '1400700' as n_dba, '128' as blocks from dual union all

select '1400780' as n_dba, '128' as blocks from dual union all

select '1400800' as n_dba, '128' as blocks from dual union all

select '1400880' as n_dba, '128' as blocks from dual union all

select '1400900' as n_dba, '128' as blocks from dual union all

select '1400980' as n_dba, '128' as blocks from dual union all

select '1400a00' as n_dba, '128' as blocks from dual union all

select '1400a80' as n_dba, '128' as blocks from dual union all

select '1400b00' as n_dba, '128' as blocks from dual union all

select '1400b80' as n_dba, '128' as blocks from dual union all

select '1400c00' as n_dba, '128' as blocks from dual union all

select '1400c80' as n_dba, '128' as blocks from dual union all

select '1400d00' as n_dba, '128' as blocks from dual union all

select '1400d80' as n_dba, '128' as blocks from dual union all

select '1400e00' as n_dba, '128' as blocks from dual union all

select '1400e80' as n_dba, '128' as blocks from dual union all

select '1400f00' as n_dba, '128' as blocks from dual

)

 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,

 blocks from new_ext

 union all                                                                   

select file_id,block_id,'8' blocks from dba_extents wheresegment_name='TRUNC_11'

order by block_id  

   )

   loop

     for fblkno ini.block_id..i.block_id+i.blocks-1 loop

      for fblkrow in1..ROWSPERBLOCK loop

      begin

        rid :=dbms_rowid.ROWID_CREATE(1,88060,i.file_id, fblkno,fblkrow-1);

        insert intohsql.trunc_11_new

        select *

        from hsql.trunc_11 A

        where rowid = rid;

        if sql%rowcount = 1then nrows:=nrows+1; end if;

        if(mod(nrows,10000)=0) then commit; end if;

      exception when othersthen null;

      end;

      end loop;

    end loop;

   end loop;

   COMMIT;

   dbms_output.put_line('Totalrows: '||to_char(nrows));

  END;

  /

 

Total rows: 1000000

 

PL/SQL procedure successfully completed.

 

SQL>

 

SQL>  select count(1) fromhsql.trunc_11_new;

 

  COUNT(1)

----------

   1000000

 

SQL>

 

 

8、删除并重建原表

select obj#,dataobj# from obj$ where obj#=88060;

update obj$ set DATAOBJ#=88061 where obj#=88060;

commit;

 

drop table hsql.trunc_11;

create table hsql.trunc_11 as select * from hsql.trunc_11_new;

 

四、结果验证



SQL> select count(1) from hsql.trunc_11;

 

  COUNT(1)

----------

   1000000

 

SQL>

 五、    说明



1、以上内容为个人多次测试结果,由于个人原因,如有分析不足之处还请见谅及指正。

2、文章涉及内容,请勿生产环境模拟。



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



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




 


0
0