Truncate异常恢复五之PLSQL恢复

Truncate异常恢复一之Truncate前后对比
Truncate异常恢复二之DBMS恢复
Truncate异常恢复三之GDUL恢复
Truncate异常恢复四之BBED恢复
Truncate异常恢复四之BBED恢复

恢复思路

1.通过logmnr找到被truncate表上一次的data_object_id。

2.通过dba_free_space获取所有free block。

3.通过dbms_rowid.rowid_create来创建rowid。

4.利用rowid来抽取数据。

测试

构建测试表

SQL> create table test_trun as select * from dba_objects;

Table created.

查看表的基础信息

SQL> select count(*) from test_trun;

  COUNT(*)
----------
     72938

SQL> select object_id,data_object_id from dba_objects where object_name='TEST_TRUN';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     78931          78931

Truncate表并将表空间置于read only模式

SQL> TRUNCATE TABLE TEST_TRUN;

Table truncated.

SQL> ALTER TABLESPACE DEVIN READ ONLY;

Tablespace altered.

恢复data_object_id

logminer定位data_object_id，此处测试，忽略

SQL> select object_id,data_object_id from dba_objects where object_name='TEST_TRUN';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     78931          78941
     
SQL> update sys.obj$ set dataobj#=78931 where obj#=78931;

1 row updated.

SQL> commit;

Commit complete.

SQL> @flush

System altered.


System altered.

利用plsql来恢复数据

先创建一个与原表结构一样的空表，用来存储恢复出来的数据

SQL> CREATE TABLE TEST_TRUN_BAK AS SELECT * FROM DBA_OBJECTS WHERE 1=2;

Table created.

PLSQL恢复数据

declare
   v_fno number;
   v_s_bno number;
   v_e_bno number;
   v_rowid rowid;
   nrows number;
begin
   for i in 
...
...
        commit;
       end loop;
    end loop;
end;
/

PL/SQL procedure successfully completed.

## PLSQL脚本可以从翔宇老师那里获取

检查数据恢复情况

SQL> select count(*) from test_trun_bak;

  COUNT(*)
----------
     20651

SQL> /

  COUNT(*)
----------
     23928

SQL> /

  COUNT(*)
----------
     30028

SQL> /

  COUNT(*)
----------
     37995

SQL> /

  COUNT(*)
----------
     72938

数据恢复成功。