1.1 核心业务表误 truncate 恢复

1.1

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$$