在数据库运维中，相信大家都遇到过truncate表后，又需要找回数据的情况。但技术上因truncate表后不会产生日志记录和未生成回滚段，因此不能使用常规在线方式恢复，当然也不能用闪回恢复。
常用的补救方法有：
1、有备份的情况下可以用rman恢复，但是在生产业务库中，一般不能轻易停库，而且为了一张表而关库也会对其它正常的业务产生影响 ，所以这在时间上和空间上都是不可取的。
2、检查误删除的表或分区是否有dmp备份，如有可以从dmp恢复；
那么无备份、无归档的情况下TRUNCATE掉的对象，是否还有更加迅捷的方法来恢复数据呢？笔者有位资深DBA朋友通过PLSQL编写的一个存储过程包Fy_Recover_Data，可以在不影响数据业务正常运行的情况下去快速恢复表。它是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包。
Fy_Recover_Data包的工作原理是：构造出一个结构相同、且具有完整元数据信息和格式化了的用户数据块的傀儡表对象，然后将被TRUNCATE的用户数据块找出，再将其数据内容部分嫁接到傀儡对象的用户数据块，使Oracle以为这是傀儡对象的数据，Oracle就可扫描并读出数据内容。
以下是Fy_Recover_Data包的详细使用操作过程：
(1) 先把Fy_Recover_Data包拷贝到数据库主机相关目录下（oracle用户）
[oracle@dbaedu1 shsnc]$ ls -lrt -rw-r--r-- 1 oracle oinstall 12888 Apr 7 00:42 FY_Recover_Data.zip [oracle@dbaedu1 shsnc]$ |
(2) 在dbauser用户下创建test_emp表
SYS@PROD> conn dbauser/####### Connected. DBAUSER@PROD> create table dbauser.test_emp as select * from dba_objects; Table created. DBAUSER@PROD> select count(*) from dbauser.test_emp; COUNT(*) ---------- 86975 |
(3) 用truncate删除test_emp表
SQL> truncate table dbauser.test_emp; Table truncated. SQL> select count(*) from dbauser.test_emp; COUNT(*) ---------- 0 |
(4) 在linux中的oracle用户下解压FY_Recover_Data.zip包
$ unzip FY_Recover_Data.zip Archive: FY_Recover_Data.zip inflating: FY_Recover_Data.SQL [oracle@dbaedu1 shsnc]$ ls -lrt -rw-r--r-- 1 oracle oinstall 79775 Apr 7 00:41 FY_Recover_Data.pck [oracle@dbaedu1 shsnc]$ |
(5) 在sys用户下执行存储过程
SQL> @/home/oracle/shsnc/FY_Recover_Data.SQL Package created. Package body created. |
(6) 查看test_emp表在数据文件中的目录
select file_name from dba_data_files f, dba_tables t where t.owner='DBAUSER' and t.table_name='TEST_EMP' and t.tablespace_name = f.tablespace_name; FILE_NAME -------------------------------------------------------------------- /u01/app/oracle/oradata/PROD/users01.dbf |
(7) 通过脚本恢复，可以用sqlplus命令行或者plsqldeveloper执行
exec fy_recover_data.recover_truncated_table('DBAUSER','TEST_EMP'); |
(8) 切换到DBAUSER用户下查看会发现多了些不一样以test_emp的表，这时找到相关有数据的表，把数据插入原表test_emp
DBAUSER@PROD> select count(*) from dbauser.test_emp$$; COUNT(*) ---------- 86975 SQL> insert into test_emp select * from TEST_EMP$$; 86975 rows created. SQL> commit; Commit complete. SQL> select count(*) from test_emp; COUNT(*) ---------- 86975 |
(9) 恢复数据后，把恢复时产生的2个表空间删除，再删除对应数据文件
SQL> conn as sysdba Connected. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u03/oracle/oradata/datafile/o1_mf_system_xr5ht70z_.dbf /u03/oracle/oradata/datafile/o1_mf_sysaux_xr5ht730_.dbf /u03/oracle/oradata/datafile/o1_mf_undotbs1_xr5ht73b_.dbf /u03/oracle/oradata/datafile/o1_mf_users_xr5ht740_.dbf /u03/oracle/oradata/datafile/o1_mf_biboss_cx415lcj_.dbf /u03/oracle/oradata/datafile/FY_REC_DATA.DAT /u03/oracle/oradata/datafile/FY_RST_DATA.DAT 7 rows selected. SQL>drop tablespace FY_REC_DATA INCLUDING CONTENTS; Tablespace dropped. SQL>drop tablespace FY_RST_DATA INCLUDING CONTENTS; Tablespace dropped. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u03/oracle/oradata/datafile/o1_mf_system_xr5ht70z_.dbf /u03/oracle/oradata/datafile/o1_mf_sysaux_xr5ht730_.dbf /u03/oracle/oradata/datafile/o1_mf_undotbs1_xr5ht73b_.dbf /u03/oracle/oradata/datafile/o1_mf_users_xr5ht740_.dbf /u03/oracle/oradata/datafile/o1_mf_biboss_cx415lcj_.dbf |
(10)然后去操作系统下把对应的数据文件删除即可。
对于使用工具fy_recover_data进行数据恢复，需要确保：
①truncate之后，需要保证没有新的数据进入表中，否则无法还原；
②存放该表的数据文件块不能被覆盖，否则无法完整还原数据。
在发生故障后，可以迅速使用：
SQL> altertablespace users read only;
SQL> altertablespace users read write;
来关闭/开启表空间的写功能，这样可以保证数据文件不会被覆写。
当然，最后希望大家永远不要用到今天分享的这个package。
