使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述
在生产中，truncate是使用的多的命令，在使用不当的情况下，往往会造成表的数据全部丢失，恢复较为困难。对于truncate恢复，常见的有以下几种方法可以进行恢复：
- 使用数据泵导入。该方法操作简单，前提是必须要有备份可用，并且会有数据的丢失；
- 使用RMAN进行不完全恢复。可将将数据库恢复到任何时刻，但是恢复时间较长；
- 使用odu、prm-dul、GDUL等收费软件进行恢复；
- 使用fy_recover_data包；
使用RMAN进行异机恢复已在之前测试过，详见：https://www.cnblogs.com/lijiaman/p/11577001.html 。
（二）FY_Recover_Data介绍
FY_Recover_Data是国内Oracle ACE大佬黄玮（个人网站：http://www.hellodba.com）开发的一个package，该脚本专门用于对truncate的表进行恢复。
根据作者所述，其原理：如果我们已经有一套元数据及数据块，然后将被TRUNCATE的用户数据块的内容取代其用户数据块的内容，是否可以“骗”过Oracle，让它读出这些数据呢？
回顾一下表扫描的过程，这个方法应该是可行的。我们只要想办法构造出一个结构相同、且具有完整元数据信息和格式化了的用户数据块的傀儡表对象，然后将被TRUNCATE的用户数据块找出，再将其数据内容部分嫁接到傀儡对象的用户数据块，使Oracle以外这是傀儡对象的数据，就能让Oracle扫描并读出数据内容。其原理用图示描述如下：
+-------------------------+
| Copy Of Dummy Data File |
| (With Formmated Blocks)|
+-------------------------+
||
\/
(Blcok Header, Block Tail)
||
\/
+-------------------+ +----------------+ Table Scan +---------------+
| Source Data File | => (Data Block Content) => | Dummy Table | ============> | Restore Table |
|(Without Meta Data)| |(With Meta Data)| +---------------+
+-------------------+ +----------------+
FY_Recover_Data对于表恢复的支持性如下：
| 压缩表 | 支持 |
| 索引组织表 | 支持 |
| 分区表 | 支持 |
| 行链接/行迁移 | 不支持 |
| 标准SQL类型 | 支持 |
| BLOB/CLOB | 支持Store in Row |
| 离线恢复 | 支持 |
| 操作系统平台 | 全部 |
| 数据库版本 | 9i以上 |
本文使用FY_Recover_Data对truncate的几种情况进行恢复测试，以验证fy_recover_data的恢复能力。
(三)操作过程记录
（3.1）使用fy_recover_data包执行truncate恢复，truncate后未有新数据进入表
STEP1：创建测试表，并执行truncate
SQL> create table test01 as select * from dba_objects; SQL> select count(*) from test01; COUNT(*) ---------- 86968 SQL> SQL> truncate table test01; Table truncated SQL> select count(*) from test01; COUNT(*) ---------- 0
STEP2：导入FY_Recover_Data.pck包
[oracle@source-node ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 10:50:17 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @/home/oracle/FY_Recover_Data.pck -- 第一次执行发现地30行存在“&”符号，删除该符号 Enter value for files: old 30: -- 1. Temp Restore and Recover tablespace & files --- new 30: -- 1. Temp Restore and Recover tablespace --- Package created. Warning: Package Body created with compilation errors. SQL> @/home/oracle/FY_Recover_Data.pck -- 删除“&”符号后导入成功 Package created. Package body created.
STEP3：开始执行恢复，只需要两个参数：schema和table_name
[oracle@source-node ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 11:11:20 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set time on 11:11:43 SQL> set serveroutput on 11:11:54 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01'); 11:12:01: Use existing Directory Name: FY_DATA_DIR 11:12:02: Recover Table: LIJIAMAN.TEST01$ 11:12:02: Restore Table: LIJIAMAN.TEST01$$ 11:12:09: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1 11:12:09: begin to recover table LIJIAMAN.TEST01 11:12:09: Use existing Directory Name: TMP_HF_DIR 11:12:09: Recovering data in datafile /u01/app/oracle/oradata/testdb1/users01.dbf 11:12:09: Use existing Directory Name: TMP_HF_DIR 11:12:39: 1242 truncated data blocks found. 11:12:39: 86968 records recovered in backup table LIJIAMAN.TEST01$$ 11:12:39: Total: 1242 truncated data blocks found. 11:12:39: Total: 86968 records recovered in backup table LIJIAMAN.TEST01$$ 11:12:39: Recovery completed. 11:12:39: Data has been recovered to LIJIAMAN.TEST01$$ PL/SQL procedure successfully completed.
STEP4：根据恢复日志，会创建临时中转表test01$和test01$$，恢复的数据保存在test01$$中
SQL> show user User is "LIJIAMAN" SQL> select count(*) from test01$$; COUNT(*) ---------- 86968 --将数据还原到test01表中 SQL> insert into test01 select * from test01$$; --确认数据已经还原回来 SQL> select count(*) from test01; COUNT(*) ---------- 86968
经过测试，如果表被truncate后，未执行其它操作，数据可以使用fy_recover_data恢复回来。
#######################################################################################
（3.2）使用fy_recover_data包执行truncate恢复，truncate后有新数据进入表（新插入的数据比truncate之前多）
STEP1：创建测试表、序列、存储过程
SQL> create table test01 2 ( 3 col1 number, 4 col2 number, 5 col3 date, 6 col4 varchar2(30), 7 col5 varchar2(100) 8 ); Table created SQL> --创建自增序列 SQL> CREATE SEQUENCE seq01 2 START WITH 1 3 MAXVALUE 99999999 4 MINVALUE 0 5 CYCLE 6 CACHE 10 7 ORDER; Sequence created SQL> --创建随机数据插入存储过程,其中col1列单调递增 create or replace procedure p_insert_test01 IS v_col1 NUMBER; BEGIN FOR i IN 1..10000 LOOP select seq01.nextval INTO v_col1 from dual; insert into test01(col1,col2,col3,col4,col5) values (v_col1, (select round(dbms_random.value(10000, 100000000)) from dual), sysdate, (select dbms_random.string('a', 25) from dual), (select dbms_random.string('a', 85) from dual)); END LOOP; commit; end p_insert_test01;
STEP2：测试表插入10000条数据，col1列的值从1到10000
SQL> exec p_insert_test01; PL/SQL procedure successfully completed SQL> select count(*) from test01; COUNT(*) ---------- 10000 SQL> SELECT MIN(col1),MAX(col1) FROM test01; MIN(COL1) MAX(COL1) ---------- ---------- 1 10000
STEP3： 执行truncate操作
SQL> truncate table test01; Table truncated
STEP4： 接着往表里插入20000条数据
SQL> exec p_insert_test01; PL/SQL procedure successfully completed SQL> exec p_insert_test01; PL/SQL procedure successfully completed SQL> select count(*) from test01; COUNT(*) ---------- 20000 SQL> SELECT MIN(col1),MAX(col1) FROM test01; MIN(COL1) MAX(COL1) ---------- ---------- 10001 30000
STEP5：执行恢复操作
[oracle@source-node ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 14:00:57 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set serveroutput on SQL> set time on 14:01:06 SQL> 14:01:09 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01'); 14:01:13: Use existing Directory Name: FY_DATA_DIR 14:01:13: Recover Table: LIJIAMAN.TEST01$ 14:01:14: Restore Table: LIJIAMAN.TEST01$$ 14:01:18: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1 14:01:18: begin to recover table LIJIAMAN.TEST01 14:01:18: Use existing Directory Name: TMP_HF_DIR 14:01:18: Recovering data in datafile /u01/app/oracle/oradata/testdb1/users01.dbf 14:01:18: Use existing Directory Name: TMP_HF_DIR 14:01:32: 402 truncated data blocks found. 14:01:32: 20000 records recovered in backup table LIJIAMAN.TEST01$$ 14:01:32: Total: 402 truncated data blocks found. 14:01:32: Total: 20000 records recovered in backup table LIJIAMAN.TEST01$$ 14:01:32: Recovery completed. 14:01:32: Data has been recovered to LIJIAMAN.TEST01$$ PL/SQL procedure successfully completed.
STEP6： 通过对test01$$表进行确认，发现返回的数据是truncate之后插入的数据，不符合要求
SQL> select count(*) from test01; COUNT(*) ---------- 20000 SQL> select count(*) from test01$$; COUNT(*) ---------- 20000 SQL> SELECT MIN(col1),MAX(col1) FROM test01; MIN(COL1) MAX(COL1) ---------- ---------- 10001 30000 SQL> SELECT MIN(col1),MAX(col1) FROM test01$$; MIN(COL1) MAX(COL1) ---------- ---------- 10001 30000
#######################################################################################
（3.3）使用fy_recover_data包执行truncate恢复，truncate后有新数据进入表（新插入的数据比truncate之前少）
STEP1：创建测试表、序列、存储过程
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as lijiaman@192.168.10.11/testdb1 SQL> DROP TABLE test01 PURGE; Table dropped SQL> create table test01 2 ( 3 col1 number, 4 col2 number, 5 col3 date, 6 col4 varchar2(30), 7 col5 varchar2(100) 8 ); Table created SQL> DROP SEQUENCE seq01; Sequence dropped SQL> --创建自增序列 SQL> CREATE SEQUENCE seq01 2 START WITH 1 3 MAXVALUE 99999999 4 MINVALUE 0 5 CYCLE 6 CACHE 10 7 ORDER; Sequence created SQL> --创建随机数据插入存储过程,其中col1列单调递增 SQL> create or replace procedure p_insert_test01 IS 2 v_col1 NUMBER; 3 BEGIN 4 FOR i IN 1..10000 LOOP 5 select seq01.nextval INTO v_col1 from dual; 6 insert into test01(col1,col2,col3,col4,col5) 7 values 8 (v_col1, 9 (select round(dbms_random.value(10000, 100000000)) from dual), 10 sysdate, 11 (select dbms_random.string('a', 25) from dual), 12 (select dbms_random.string('a', 85) from dual)); 13 END LOOP; 14 commit; 15 end p_insert_test01; 16 / Procedure created
STEP2：测试表插入10000条数据，col1列的值从1到10000
SQL> exec p_insert_test01; PL/SQL procedure successfully completed SQL> select count(*) from test01; COUNT(*) ---------- 10000 SQL> SELECT MIN(col1),MAX(col1) FROM test01; MIN(COL1) MAX(COL1) ---------- ---------- 1 10000
STEP3：执行truncate操作
SQL> truncate table test01; Table truncated
STEP4：修改存储过程，酶促插入100条数据
SQL> --创建随机数据插入存储过程,其中col1列单调递增 SQL> create or replace procedure p_insert_test01 IS 2 v_col1 NUMBER; 3 BEGIN 4 FOR i IN 1..100 LOOP 5 select seq01.nextval INTO v_col1 from dual; 6 insert into test01(col1,col2,col3,col4,col5) 7 values 8 (v_col1, 9 (select round(dbms_random.value(10000, 100000000)) from dual), 10 sysdate, 11 (select dbms_random.string('a', 25) from dual), 12 (select dbms_random.string('a', 85) from dual)); 13 END LOOP; 14 commit; 15 end p_insert_test01; 16 / Procedure created -- 测试表插入100条数据 SQL> exec p_insert_test01; PL/SQL procedure successfully completed
STEP5：执行恢复操作
[oracle@source-node ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 14:22:34 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set time on 14:22:39 SQL> set serveroutput on 14:22:44 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01'); 14:22:52: Use existing Directory Name: FY_DATA_DIR 14:22:52: Recover Table: LIJIAMAN.TEST01$ 14:22:52: Restore Table: LIJIAMAN.TEST01$$ 14:22:57: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1 14:22:57: begin to recover table LIJIAMAN.TEST01 14:22:57: Use existing Directory Name: TMP_HF_DIR 14:22:57: Recovering data in datafile /u01/app/oracle/oradata/testdb1/users01.dbf 14:22:57: Use existing Directory Name: TMP_HF_DIR 14:23:06: 5 truncated data blocks found. 14:23:06: 100 records recovered in backup table LIJIAMAN.TEST01$$ 14:23:06: Total: 5 truncated data blocks found. 14:23:06: Total: 100 records recovered in backup table LIJIAMAN.TEST01$$ 14:23:06: Recovery completed. 14:23:06: Data has been recovered to LIJIAMAN.TEST01$$ PL/SQL procedure successfully completed.
STEP6： 通过对test01$$表进行确认，发现返回的数据是truncate之后插入的数据，不符合要求
SQL> select count(*) from test01; COUNT(*) ---------- 100 SQL> SELECT MIN(col1),MAX(col1) FROM test01; MIN(COL1) MAX(COL1) ---------- ---------- 10001 10100 SQL> SQL> select count(*) from test01$$; COUNT(*) ---------- 100 SQL> SELECT MIN(col1),MAX(col1) FROM test01$$; MIN(COL1) MAX(COL1) ---------- ---------- 10001 10100
#######################################################################################
（3.4）测试数据文件被覆盖是否影响恢复
STEP1：创建测试表
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as lijiaman@192.168.10.11/testdb1 SQL> create table test01 2 ( 3 col1 number, 4 col2 number, 5 col3 date, 6 col4 varchar2(30), 7 col5 varchar2(100) 8 ) TABLESPACE USERS; Table created
STEP2： 初始时候，表空间总共20MB，剩余15.94MB
SQL> SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName, 2 round(SUM(a.bytes/1024/1024),2) AS "Totle_size(MB)", 3 round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)", 4 round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Used_space(MB)", 5 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%", 6 round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_size(MB)", 7 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%" 8 FROM dba_data_files a, 9 (SELECT SUM(NVL(bytes,0)) free_space1, 10 file_id 11 FROM dba_free_space 12 GROUP BY file_id 13 ) b 14 WHERE a.file_id = b.file_id(+) 15 AND a.TABLESPACE_NAME = 'USERS' 16 GROUP BY a.TABLESPACE_NAME; TABLESPACENAME Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent% -------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------ USERS 20 15.94 4.06 20.31 20 20.31
STEP3：test01表插入大量数据
SQL> exec p_insert_test01; PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / PL/SQL procedure successfully completed SQL> / begin p_insert_test01; end; ORA-01653: unable to extend table LIJIAMAN.TEST01 by 128 in tablespace USERS ORA-06512: at "LIJIAMAN.P_INSERT_TEST01", line 6 ORA-06512: at line 1
STEP4：此时，表空间总共20MB，剩余0.94MB
SQL> SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName, 2 round(SUM(a.bytes/1024/1024),2) AS "Totle_size(MB)", 3 round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)", 4 round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Used_space(MB)", 5 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%", 6 round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_size(MB)", 7 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%" 8 FROM dba_data_files a, 9 (SELECT SUM(NVL(bytes,0)) free_space1, 10 file_id 11 FROM dba_free_space 12 GROUP BY file_id 13 ) b 14 WHERE a.file_id = b.file_id(+) 15 AND a.TABLESPACE_NAME = 'USERS' 16 GROUP BY a.TABLESPACE_NAME; TABLESPACENAME Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent% -------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------ USERS 20 0.94 19.06 95.31 20 95.31
STEP5：此时test01表有90000行数据
SQL> select count(*) from test01; COUNT(*) ---------- 90000 SQL> SELECT MIN(col1),MAX(col1) FROM test01; MIN(COL1) MAX(COL1) ---------- ---------- 109751 199750
STEP6：对test01执行truncate
SQL> truncate table test01; Table truncated
STEP7：执行truncate后，空间已经释放
SQL> SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName, 2 round(SUM(a.bytes/1024/1024),2) AS "Totle_size(MB)", 3 round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)", 4 round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Used_space(MB)", 5 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%", 6 round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_size(MB)", 7 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%" 8 FROM dba_data_files a, 9 (SELECT SUM(NVL(bytes,0)) free_space1, 10 file_id 11 FROM dba_free_space 12 GROUP BY file_id 13 ) b 14 WHERE a.file_id = b.file_id(+) 15 AND a.TABLESPACE_NAME = 'USERS' 16 GROUP BY a.TABLESPACE_NAME; TABLESPACENAME Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent% -------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------ USERS 20 15.88 4.12 20.63 20 20.63
STEP8：创建表test02，用来覆盖test01释放的空间
SQL> create table test02 as select * from dba_objects; Table created
STEP9：test02表创建之后，剩余空间为5.88MB，可以说明：test02表的数据占用了test01表释放出来的空间，即test01表的部分数据已经被覆盖
SQL> SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName, 2 round(SUM(a.bytes/1024/1024),2) AS "Totle_size(MB)", 3 round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)", 4 round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Used_space(MB)", 5 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%", 6 round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_size(MB)", 7 ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%" 8 FROM dba_data_files a, 9 (SELECT SUM(NVL(bytes,0)) free_space1, 10 file_id 11 FROM dba_free_space 12 GROUP BY file_id 13 ) b 14 WHERE a.file_id = b.file_id(+) 15 AND a.TABLESPACE_NAME = 'USERS' 16 GROUP BY a.TABLESPACE_NAME; TABLESPACENAME Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent% -------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------ USERS 20 5.88 14.12 70.63 20 70.63
STEP10：执行恢复操作
[oracle@source-node ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 15:09:58 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set time on 15:10:05 SQL> set serveroutput on 15:10:10 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01'); 15:10:17: Use existing Directory Name: FY_DATA_DIR 15:10:17: Recover Table: LIJIAMAN.TEST01$ 15:10:17: Restore Table: LIJIAMAN.TEST01$$ 15:10:22: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1 15:10:22: begin to recover table LIJIAMAN.TEST01 15:10:22: Use existing Directory Name: TMP_HF_DIR 15:10:22: Recovering data in datafile /u01/app/oracle/oradata/testdb1/users01.dbf 15:10:22: Use existing Directory Name: TMP_HF_DIR 15:10:31: 645 truncated data blocks found. 15:10:31: 24439 records recovered in backup table LIJIAMAN.TEST01$$ 15:10:31: Total: 645 truncated data blocks found. 15:10:31: Total: 24439 records recovered in backup table LIJIAMAN.TEST01$$ 15:10:31: Recovery completed. 15:10:31: Data has been recovered to LIJIAMAN.TEST01$$ PL/SQL procedure successfully completed.
STEP11： 发现只恢复了部分数据，不符合要求
-- truncate之前test01表有90000行数据，恢复了24339行数据 SQL> select count(*) from test01$$; COUNT(*) ---------- 24439 SQL> SELECT MIN(col1),MAX(col1) FROM test01; MIN(COL1) MAX(COL1) ---------- ---------- SQL> SQL> SELECT MIN(col1),MAX(col1) FROM test01$$; MIN(COL1) MAX(COL1) ---------- ---------- 109751 199750
(四)总结
对于使用工具fy_recover_data进行数据恢复，需要确保：
①truncate之后，需要保证没有新的数据进入表中，否则无法还原；
②存放该表的数据文件块不能被覆盖，否则无法完整还原数据。
在发生故障后，可以迅速使用：
SQL> alter tablespace users read only; SQL> alter tablespace users read write;
来关闭/开启表空间的写功能，这样可以保证数据文件不会被覆写。
【完】
Copyright © 2020 ruiliair. All Rights Reserved.
版权声明：本文为lijiaman原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。