Fy_Recover_Data 2014.03.07 Update -- You can recover truncated table from offline data files now

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2016-03-07 01:52:17

Share to 

-- 05/03/2014, Fuyuncat:                                                ---   --   1. Fixed bugs                                                      ---   --   2. Use existing dirctory if applicable                             ---   --   3. Recover data from offline files                                 --- In case you found that you truncated a table should not be done. You may copy the data files of the tablespace to a local location. And then use Fy_Recover_Data to recover data from those offline files. This will guarantee minimuming data lost. (Bear in mind, once table is truncated, the data blocks will be reclaimed by Oracle immediately, and you might be allocated to other objects if necessary.)  SQL代码 ASMCMD> cp data1/dbat1/datafile/ASM_DATA.360.839931591 /tmp/ams_data.dat   copying +data1/dbat1/datafile/ASM_DATA.360.839931591 -> /tmp/ams_data.dat   ASMCMD> exit      oracle@dbsvr:dbat1:> sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 7 12:48:45 2014      Copyright (c) 1982, 2011, Oracle.  All rights reserved.         Connected to:   Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production   With the Partitioning, Automatic Storage Management, OLAP, Data Mining   and Real Application Testing options      HelloDBA.COM> !cp /u01/app/oracle/oradata/dbat1/asm_data_fs.dat /tmp/asm_data_2      HelloDBA.COM> exec fy_recover_data.recover_truncated_table('SYS','T1',1,'/tmp','/tmp/asm_data_1;/tmp/asm_data_2;');   16:06:27: Use existing Directory Name: FY_DATA_DIR   16:06:28: Recover Table: SYS.T1$   16:06:28: Restore Table: SYS.T1$$   16:06:43: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT2   16:06:43: begin to recover table SYS.T1   16:06:43: Use existing Directory Name: TMP_HF_DIR   16:06:44: Recovering data in datafile /tmp/asm_data_1   16:06:44: Use existing Directory Name: FY_DATA_DIR   16:06:49: 153 truncated data blocks found.   16:06:49: 4348 records recovered in backup table SYS.T1$$   16:06:49: Recovering data in datafile /tmp/asm_data_2   16:06:49: Use existing Directory Name: FY_DATA_DIR   16:06:50: 0 truncated data blocks found.   16:06:50: 0 records recovered in backup table SYS.T1$$   16:06:50: Total: 153 truncated data blocks found.   16:06:50: Total: 4348 records recovered in backup table SYS.T1$$   16:06:50: Recovery completed.   16:06:50: Data has been recovered to SYS.T1$$      PL/SQL procedure successfully completed.   Click Here to download source code of the package.   --- Fuyuncat ---