truncate表恢复

原理

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

实验工具

Fy_Recover_Data恢复工具下载链接

PRM-DUL恢复工具下载链接

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

实验步骤

1. 下载并解压Fy_Recover_Data

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

2.编译Fy_Recover_Data

1sys@DBHAWK>@FY_Recover_Data.pck
2Enter value for files: 
3old  30:   --   1. Temp Restore and Recover tablespace & files             ---
4new  30:   --   1. Temp Restore and Recover tablespace              ---
5
6Package created.
7Package body created.

3.创造实验环境

1scott@DBHAWK>select * from hawk_dept;
2
3    DEPTNO DNAME      LOC
4---------- -------------- -------------
5    10 ACCOUNTING     NEW YORK
6    20 RESEARCH       DALLAS
7    30 SALES          CHICAGO
8    40 OPERATIONS     BOSTON
9
10scott@DBHAWK>select count(1) from hawk_dept;
11
12  COUNT(1)
13----------
14     4
15
16scott@DBHAWK>truncate table hawk_dept;
17
18Table truncated.
19
20scott@DBHAWK>select * from hawk_dept;
21
22no rows selected
23
24scott@DBHAWK>select count(1) from hawk_dept;
25
26  COUNT(1)
27----------
28     0

4.使用Fy_Recover_Data恢复数据

1sys@DBHAWK>exec fy_recover_data.recover_truncated_table('SCOTT','hawk_dept');
2
300:08:03: Use existing Directory Name: FY_DATA_DIR
400:08:03: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
500:08:03: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
600:08:04: Recover Table: SCOTT.HAWK_DEPT$
700:08:04: Restore Table: SCOTT.HAWK_DEPT$$
800:08:13: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
900:08:13: begin to recover table SCOTT.HAWK_DEPT
1000:08:13: Use existing Directory Name: TMP_HF_DIR
1100:08:13: Recovering data in datafile /u01/app/oracle/oradata/dbhawk/users01.dbf
1200:08:13: Use existing Directory Name: TMP_HF_DIR
1300:08:14: 1 truncated data blocks found.
1400:08:14: 4 records recovered in backup table SCOTT.HAWK_DEPT$$
1500:08:14: Total: 1 truncated data blocks found.
1600:08:14: Total: 4 records recovered in backup table SCOTT.HAWK_DEPT$$
1700:08:14: Recovery completed.
1800:08:14: Data has been recovered to SCOTT.HAWK_DEPT$$
19
20PL/SQL procedure successfully completed.
21
22scott@DBHAWK>select * from HAWK_DEPT$$;
23
24    DEPTNO DNAME      LOC
25---------- -------------- -------------
26    10 ACCOUNTING     NEW YORK
27    20 RESEARCH       DALLAS
28    30 SALES          CHICAGO
29    40 OPERATIONS     BOSTON
30
31scott@DBHAWK>insert into hawk_dept select * from HAWK_DEPT$$;
32
334 rows created.
34
35scott@DBHAWK>select * from hawk_dept;
36
37    DEPTNO DNAME      LOC
38---------- -------------- -------------
39    10 ACCOUNTING     NEW YORK
40    20 RESEARCH       DALLAS
41    30 SALES          CHICAGO
42    40 OPERATIONS     BOSTON

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

1sys@DBHAWK>drop tablespace FY_REC_DATA including contents and datafiles;
2
3Tablespace dropped.
4
5sys@DBHAWK>drop tablespace FY_RST_DATA including contents and datafiles;
6
7Tablespace dropped.

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