truncate表恢复 - HelloWorld开发者社区

原理

Truncate 不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据（如存储断头和扩展段图）。
也就是说，此时，基本数据表并未破坏，而是被系统回收，等待被重新分配—因此，要恢复被truncate的数据，需要及时备份所在的数据文件。

实验工具

Fy_Recover_Data恢复工具下载链接

PRM-DUL恢复工具下载链接

注：本次实验使用Fy_Recover_Data恢复被TRUNCATE的数据

实验步骤

1. 下载并解压Fy_Recover_Data

[oracle@hawker ~]$ ll
total 380
-rw-r--r--. 1 oracle oinstall  79775 Mar  7  2014 FY_Recover_Data.pck
-rw-r--r--. 1 oracle oinstall  12888 May  4 17:10 FY_Recover_Data.zip
-rw-r-----. 1 oracle oinstall 289692 May  4 11:46 install2018-05-04_11-38-06.log

2.编译Fy_Recover_Data

sys@DBHAWK>@FY_Recover_Data.pck
Enter value for files: 
old  30:   --   1. Temp Restore and Recover tablespace & files             ---
new  30:   --   1. Temp Restore and Recover tablespace              ---

Package created.
Package body created.

3.创造实验环境

scott@DBHAWK>select * from hawk_dept;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

scott@DBHAWK>select count(1) from hawk_dept;

  COUNT(1)
----------
     4

scott@DBHAWK>truncate table hawk_dept;

Table truncated.

scott@DBHAWK>select * from hawk_dept;

no rows selected

scott@DBHAWK>select count(1) from hawk_dept;

  COUNT(1)
----------
     0

4.使用Fy_Recover_Data恢复数据

sys@DBHAWK>exec fy_recover_data.recover_truncated_table('SCOTT','hawk_dept');

00:08:03: Use existing Directory Name: FY_DATA_DIR
00:08:03: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
00:08:03: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
00:08:04: Recover Table: SCOTT.HAWK_DEPT$
00:08:04: Restore Table: SCOTT.HAWK_DEPT$$
00:08:13: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
00:08:13: begin to recover table SCOTT.HAWK_DEPT
00:08:13: Use existing Directory Name: TMP_HF_DIR
00:08:13: Recovering data in datafile /u01/app/oracle/oradata/dbhawk/users01.dbf
00:08:13: Use existing Directory Name: TMP_HF_DIR
00:08:14: 1 truncated data blocks found.
00:08:14: 4 records recovered in backup table SCOTT.HAWK_DEPT$$
00:08:14: Total: 1 truncated data blocks found.
00:08:14: Total: 4 records recovered in backup table SCOTT.HAWK_DEPT$$
00:08:14: Recovery completed.
00:08:14: Data has been recovered to SCOTT.HAWK_DEPT$$

PL/SQL procedure successfully completed.

scott@DBHAWK>select * from HAWK_DEPT$$;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

scott@DBHAWK>insert into hawk_dept select * from HAWK_DEPT$$;

4 rows created.

scott@DBHAWK>select * from hawk_dept;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

5.清理恢复程序生成的表空间

sys@DBHAWK>drop tablespace FY_REC_DATA including contents and datafiles;

Tablespace dropped.

sys@DBHAWK>drop tablespace FY_RST_DATA including contents and datafiles;

Tablespace dropped.

注：生产环境中谨慎操作，如有必要请联系专业人士处理。