创建测试用户测试表
SQL> create user wy identified by wy;
User created.
SQL> grant dba,connect to wy;
Grant succeeded.
SQL> conn wy/wy
Connected.
SQL> create table test as select * from dba_objects;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
107025
模拟使用truncate清空表
SQL> truncate table wy.test;
Table truncated.
SQL> select count(*) from test;
COUNT(*)
----------
0
连接到数据库
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 16:33:03 2022
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> select file_name from dba_data_files f, dba_tables t where t.owner='WY' and t.table_name='TEST' and t.tablespace_name = f.tablespace_name;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/app/u01/oracle/oradata/HALODB/datafile/o1_mf_users_kklfmb2g_.dbf
执行脚本并输入文件位置
SQL>@/home/oracle/FY_Recover_Data.pck
Enter value for files: /app/u01/oracle/oradata/HALODB/datafile/o1_mf_users_kklfmb2g_.dbf
old 30: -- 1. Temp Restore and Recover tablespace & files ---
new 30: -- 1. Temp Restore and Recover tablespace /app/u01/oracle/oradata/HALODB/datafile/o1_mf_users_kklfmb2g_.dbf ---
Package created.
Package body created.
开始执行恢复，用户名，表名
SQL> exec fy_recover_data.recover_truncated_table('WY','TEST');
PL/SQL procedure successfully completed.
使用wy用户查看多了TEST$$表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
TEST$ TABLE
TEST$$ TABLE
看看数据
SQL> select count(*) from wy.TEST$ ;
COUNT(*)
----------
0
SQL> select count(*) from wy.TEST$$ ;
COUNT(*)
----------
107025
将wy.TEST$插入到要恢复的表中
insert into wy.TEST$ select * from wy.TEST$$;
107025 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from wy.TEST;
COUNT(*)
----------
107025
对比表空间多了两个
TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_USE MAX_FREE CHUNKS_FREE
-------------------- ---------------- ---------------- ---------- ---------------- ----------------
SYSTEM 1,160 10 99.1002155 9 5
TS_HIS6 74,300 2,421 96.741504 860 4
USERS 2,494 133 94.6766917 133 1
SYSAUX 690 40 94.1394928 40 2
UNDOTBS1 2,225 414 81.4101124 208 15
SQL> /
TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_USE MAX_FREE CHUNKS_FREE
-------------------- ---------------- ---------------- ---------- ---------------- ----------------
FY_REC_DATA 0 0 100 0 0
SYSTEM 1,160 10 99.1056034 9 5
TS_HIS6 74,300 2,421 96.741504 860 4
USERS 2,494 133 94.6766917 133 1
SYSAUX 690 40 94.1394928 40 2
UNDOTBS1 2,225 414 81.4101124 208 15
FY_RST_DATA 20 6 70 6 1
7 rows selected.
注意事项
对于使用工具fy_recover_data进行数据恢复，需要确保：
①truncate之后，需要保证没有新的数据进入表中，否则无法还原；
②存放该表的数据文件块不能被覆盖，否则无法完整还原数据。
在发生故障后，可以迅速使用：
SQL> altertablespace users read only;
SQL> altertablespace users read write;
来关闭/开启表空间的写功能，这样可以保证数据文件不会被覆写。
Fy_Recover_Data包的下载地址：
链接：https://pan.baidu.com/s/1HvRqgIShU9x3xih-IkSVFQ
提取码：0911
摩天轮
https://www.modb.pro/download/909567
