truncate表后的快速恢复方法

在数据库运维中，相信大家都遇到过truncate表后，又需要找回数据的情况。但技术上因truncate表后不会产生日志记录和未生成回滚段，因此不能使用常规在线方式恢复，当然也不能用闪回恢复。

常用的补救方法有：

1、有备份的情况下可以用rman恢复，但是在生产业务库中，一般不能轻易停库，而且为了一张表而关库也会对其它正常的业务产生影响 ，所以这在时间上和空间上都是不可取的。

2、检查误删除的表或分区是否有dmp备份，如有可以从dmp恢复；

那么无备份、无归档的情况下TRUNCATE掉的对象，是否还有更加迅捷的方法来恢复数据呢？笔者有位资深DBA朋友通过PLSQL编写的一个存储过程包Fy_Recover_Data，可以在不影响数据业务正常运行的情况下去快速恢复表。它是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包。



Fy_Recover_Data包的工作原理是：构造出一个结构相同、且具有完整元数据信息和格式化了的用户数据块的傀儡表对象，然后将被TRUNCATE的用户数据块找出，再将其数据内容部分嫁接到傀儡对象的用户数据块，使Oracle以为这是傀儡对象的数据，Oracle就可扫描并读出数据内容。

其原理用图示描述如下：



（Fy_Recover_Data包的工作原理）

以下是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。