truncate 表恢复实验

1、用spuser建表
create table test1 as select * from all_tables;

select count(*) from TEST1 t;
99

2、编译包
sys用户编译包FY_Recover_Data

3、truncate表
truncate table TEST1;

4、恢复数据
(1)将数据文件拷贝出来

先查看在哪个数据文件中
select T.owner,T.segment_name,A.file_name,A.tablespace_name
 from dba_extents t,DBA_DATA_FILES A
 where T.file_id=A.file_id  AND t.owner = 'SPUSER'  AND t.segment_name = 'TEST1'
-----------------ASM拷贝数据-----------------------------
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
CRS/
DATA/
REC/
ASMCMD> cd DATA
ASMCMD> cd TEST
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.260.909987057
SYSTEM.259.909987051
UNDOTBS1.261.909987063
UNDOTBS2.263.909987079
USERS.264.909987079
ASMCMD> cp USERS.264.909987079 /home/grid/user01.dbf
copying +DATA/TEST/DATAFILE/USERS.264.909987079 -> /home/grid/user01.dbf
ASMCMD> exit
------------------------------------------------------------------
--------------------------裸设备拷贝数据----------------------------
rman>copy datafile ''/dev/rlvrawtest'' to ''/u01/test/datafile/test01.dbf'';
asm应该也可以用此方法拷贝出来。
-----------------------------------------------------------------

[grid@rac1 ~]$ exit
logout
[root@rac1 ~]# cd /home/grid/
[root@rac1 grid]# ls
Desktop  oradiag_grid  user01.dbf
[root@rac1 grid]# cp user01.dbf /home/oracle/
[root@rac1 grid]# cd /home/oracle/
[root@rac1 oracle]# ls
ashrpt_1_0611_2020.html            oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686.rpm  oradiag_oracle
database                           oracleasmlib-2.0.4-1.el5.i386.rpm              unixODBC-devel-2.2.11-7.1.i386.rpm
libaio-devel-0.3.106-3.2.i386.rpm  oracleasm-support-2.1.7-1.el5.i386.rpm         user01.dbf
[root@rac1 oracle]# chmod 777 user01.dbf 

(2)执行恢复操作
[root@rac1 oracle]# su - oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 14 01:56:27 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> exec fy_recover_data.recover_truncated_table('SPUSER','TEST1',1,'/home/oracle','/home/oracle/user01.dbf');

PL/SQL procedure successfully completed.

这里的
exec fy_recover_data.recover_truncated_table('SPUSER','TEST1',1,'/home/oracle','/home/oracle/user01.dbf;/home/oracle/user01.dbf;');
---如果是多个数据文件用分号分隔

(3)验证
select * from spuser.test1$$
恢复的数据到test1$$ 这个表里面了