上篇bbed的方法也许较为复杂，如果对块结构和bbed不熟悉的朋友，可以尝试使用本文的方法来修复。
结合truncate系列一的原理，再加上truncate table还有一个特点，就是所释放的空间会变成free space，这就给没有备份的情况下恢复表数据提供了另外一种思路。
恢复思路如下：
- 通过logminer或者redodump找到dataobj#的变化(用于update obj$里的dataobj#)
- 使用dbms_rowid.rowid_create抽取该表空间匹配truncate前dataobj#的block里的数据
抽取的范围也有两种思路：
- truncate bbed修复中介绍的redodump找出的extent map里的block(本文的PL/SQL不采用此方式)
- 该表的第一个extent、该表空间的free space、该表空间所有segment的最后一个extent(因为truncate的空间可能已经被其他segment使用，可以抽取LHWM-HHWM之间还未格式化的block)
环境构造：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> create table rescureora.rescureora_table as select * from dba_objects; Table created. SQL> select count(*) from rescureora.rescureora_table; COUNT(*) ---------- 86877 SQL> truncate table rescureora.rescureora_table; Table truncated. |
1.通过logminer或者redodump找到dataobj#的变化
因为表可能会truncate过多次，原dataobj#不一定就等于obj#，所以需要通过redo来确认，如果最小补充日志没有打开，logminer可能会有遗漏。如果遗漏则使用redodump来寻找。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select obj#,dataobj# from obj$ where name='RESCUREORA_TABLE'; OBJ# DATAOBJ# ---------- ---------- 87903 87904 SQL> select SQL_REDO from V$LOGMNR_CONTENTS where table_name='OBJ$' and SQL_REDO LIKE '%87903%'; SQL_REDO -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- update "SYS"."OBJ$" set "OBJ#" = '87903', "DATAOBJ#" = '87904', "TYPE#" = '2', "CTIME" = TO_DATE('01-DEC-19', 'DD-MON-RR'), "MTIME" = TO_DATE('01-DEC-19', 'DD-MON-RR'), "STIME" = TO_DATE('01-DEC-19', 'DD-MON-RR'), "STATUS" = '1', "FLAGS" = '0', "OID$" = NULL, "SPARE1" = '6', "SPARE2" = '1' where "OBJ#" = '87903' and "DATAOBJ#" = '87903' and "TYPE#" = '2' and "CTIME" = TO_DATE('01-DEC-19', 'DD-MON- RR') and "MTIME" = TO_DATE('01-DEC-19', 'DD-MON-RR') and "STIME" = TO_DATE('01-DEC-19', 'DD-MON-RR') and "STATUS" = '1' and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and R OWID = 'AAAAASAABAAAVKtAAE'; |
dataobj#从87903变成了87904.
2.遍历表所在tablespace的free block和该表的第一个extent，以及该表空间所有segment的最后一个extent，使用dbms_rowid.rowid_create抽取数据
dbms_rowid.rowid_create参数解释如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
由于需要根据dataobj#去匹配，所以需要修改obj$，修改完成之后需要flush shared_pool 注意：如果有lob，truncate不会更改lob index的dataobj#,只需要修改lob的dataobj#即可 SQL> update obj$ set dataobj#=87903 where obj#=87903; 1 row updated. SQL> commit; Commit complete. SQL> alter system flush shared_pool; System altered. 设置表空间为read only，避免数据被覆盖 SQL> alter tablespace users read only; Tablespace altered. 具体抽取脚本如下： 注意：恢复的表需要在重新指定一个表空间，避免覆盖数据。本脚本示例将表恢复到了system表空间。 declare v_fno number; v_s_bno number; v_e_bno number; v_rowid rowid; nrows number; v_owner varchar2(100):='RESCUREORA'; v_table varchar2(100):='RESCUREORA_TABLE'; v_o_owner varchar2(100):='SYS'; v_o_table varchar2(100):='RESCUREORA_TABLE'; v_dataobj number; v_sql varchar2(4000); v_tablespace varchar2(100); begin select data_object_id into v_dataobj from dba_objects where owner=v_owner and object_name=v_table; select tablespace_name into v_tablespace from dba_tables where owner=v_owner and table_name=v_table; for i in (select relative_fno,block_id,blocks from dba_extents where owner=v_owner and segment_name=v_table and extent_id=0 union all select relative_fno,block_id,blocks from dba_free_space where tablespace_name=v_tablespace union all select relative_fno,block_id,blocks from ( select relative_fno,block_id,blocks,row_number()over(partition by owner,segment_name,partition_name order by extent_id desc) rn from dba_extents where tablespace_name=v_tablespace and extent_id>0) where rn=1) loop v_fno:=i.relative_fno; v_s_bno:=i.block_id; v_e_bno:=i.block_id+i.blocks-1; for j in v_s_bno .. v_e_bno loop begin for x in 0 .. 999 loop v_rowid:=dbms_rowid.rowid_create(1,v_dataobj,v_fno,j,x); v_sql:='insert into '||v_o_owner||'.'||v_o_table||' select * from '||v_owner||'.'||v_table||' where rowid=:1'; execute immediate v_sql using v_rowid; end loop; exception when others then null; end; commit; end loop; end loop; end; / SQL> select count(*) from sys.rescureora_table; COUNT(*) ---------- 86877 SQL> update obj$ set dataobj#=87904 where obj#=87903; 1 row updated. SQL> commit; Commit complete. SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; |
至此，该方法在无数据覆盖的情况下实现了完全恢复。