1.1 核心业务表误 truncate 恢复
1.1.1 模拟 truncate 误操作
创建 jason 业务表，如下： SQL> create table jason as select * from dba_objects; SQL> create index idx_jason_oid on jason(object_id);
用户在清理数据的时候，不小心清理 jason 业务表，如下： SQL> truncate table jason;
1.1.2 创建 truncate 恢复依赖 Pa ckage
SQL> @Recover_Truncate_Data.sql Package created. Package body created.
1.1.3 立即拷贝误删表所在数据文件
锁定被误truncate 表所在数据文件 ，如下： select f.file_name, f.bytes / 1024 / 1024 / 1024, f.maxbytes / 1024 / 1024 / 1024, f.autoextensible from dba_segments s, dba_data_files f where s.tablespace_name = f.tablespace_name and s.owner = 'SCOTT' and s.segment_name = 'JASON' and s.segment_type = 'TABLE';
拷贝数据文件至临时目录 ，如下： SQL> !cp /oracle/app/oradata/orcl/testtbs01.dbf /tmp/fs_data_1.dat
或者 ASMCMD> cp +DATA/orcl/datafile/testtbs.438.1082648913 /tmp/ams_data_1.dat
1.1.4 尝试使用联机数据文件恢复
使用联机数据文件进行恢复，如下： SQL> set time on SQL> set serveroutput on SQL> exec fy_recover_data.recover_truncated_table('SCOTT','JASON'); 11:11:28: New Directory Name: FY_DATA_DIR 11:11:28: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT 11:11:28: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT 11:11:29: Recover Table: SCOTT.JASON$ 11:11:29: Restore Table: SCOTT.JASON$$ 11:11:35: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT 11:11:35: begin to recover table SCOTT.JASON 11:11:35: New Directory Name: TMP_HF_DIR 11:11:35: Recovering data in datafile /oracle/app/oradata/orcl/testtbs01.dbf 11:11:35: Use existing Directory Name: TMP_HF_DIR 11:12:08: 1234 truncated data blocks found. 11:12:08: 86429 records recovered in backup table SCOTT.JASON$$ 11:12:08: Total: 1234 truncated data blocks found. 11:12:08: Total: 86429 records recovered in backup table SCOTT.JASON$$ 11:12:08: Recovery completed. 11:12:08: Data has been recovered to SCOTT.JASON$$ PL/SQL procedure successfully completed. 11:12:08 SQL>
通过恢复出来的数据，恢复 jason 业务表，如下： SQL> alter table jason rename to jason_old; SQL> alter table jason$$ rename to jason;
1.1.5 尝试使用离线数据文件恢复
使用离线数据文件进行恢复，如下： SQL> set time on SQL> set serveroutput on SQL> exec fy_recover_data.recover_truncated_table('SCOTT','JASON',1,'/tmp','/tmp/fs_data_1.dat;'); 11:28:00: Use existing Directory Name: FY_DATA_DIR 11:28:01: Recover Table: SCOTT.JASON$ 11:28:01: Restore Table: SCOTT.JASON$$ 11:28:07: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT 11:28:07: begin to recover table SCOTT.JASON 11:28:07: Use existing Directory Name: TMP_HF_DIR 11:28:07: Recovering data in datafile /tmp/fs_data_1.dat 11:28:07: Use existing Directory Name: FY_DATA_DIR 11:28:48: 1234 truncated data blocks found. 11:28:48: 86429 records recovered in backup table SCOTT.JASON$$ 11:28:48: Total: 1234 truncated data blocks found. 11:28:48: Total: 86429 records recovered in backup table SCOTT.JASON$$ 11:28:48: Recovery completed. 11:28:48: Data has been recovered to SCOTT.JASON$$ PL/SQL procedure successfully completed. 11:28:48 SQL>
通过恢复出来的数据，恢复 jason 业务表，如下： SQL> alter table jason rename to jason_old; SQL> alter table jason$$ rename to jason;
