Oracle truncate异常恢复之plsql修复

2020-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 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 buffer_cache;

select count(1) from hsql.TRUNC_11;

三、恢复过程

******恢复过程

1、将表空间置为read only

alter tablespace hsql read only;

2、oracle 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 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;

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、文章涉及内容，请勿在生产环境模拟。

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

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

分享好友

分享这个小栈给你的朋友们，一起进步吧。

数据库技术笔记

创建时间：2020-03-03 00:45:30

oracle技术分享

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

栈主、嘉宾

查看更多

orastar
栈主

小栈成员

查看更多

xzh1980
飘絮絮絮丶
Leila
gaokeke123

一、 概念介绍

二、 环境准备

栈主、嘉宾

小栈成员

一、概念介绍

二、环境准备