Truncate异常恢复二之DBMS恢复

Truncate异常恢复一之Truncate前后对比
Truncate异常恢复二之DBMS恢复
Truncate异常恢复三之GDUL恢复
Truncate异常恢复四之BBED恢复
Truncate异常恢复四之BBED恢复

构建测试表

SQL> create table test_trun as select * from dba_objects;

Table created.

SQL> select count(*) from test_trun;

  COUNT(*)
----------
     72885

创建DBMS包

SQL> @FY_Recover_Data.pck
Enter value for files: recover

Package created.


Package body created.

恢复测试

SQL> truncate table devin.test_trun;

Table truncated.

SQL> exec fy_recover_data.recover_truncated_table('devin','test_trun');
17:02:57: Use existing Directory Name: FY_DATA_DIR
17:02:59: Recover Table: DEVIN.TEST_TRUN$
17:02:59: Restore Table: DEVIN.TEST_TRUN$$
17:03:05: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
17:03:05: begin to recover table DEVIN.TEST_TRUN
17:03:05: Use existing Directory Name: TMP_HF_DIR
17:03:05: Recovering data in datafile /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf
17:03:05: Use existing Directory Name: TMP_HF_DIR
17:04:09: 1426 truncated data blocks found.
17:04:09: 72885 records recovered in backup table DEVIN.TEST_TRUN$$
17:04:09: Total: 1426 truncated data blocks found.
17:04:09: Total: 72885 records recovered in backup table DEVIN.TEST_TRUN$$
17:04:09: Recovery completed.
17:04:09: Data has been recovered to DEVIN.TEST_TRUN$$

PL/SQL procedure successfully completed.


SQL> select count(*) from devin.test_trun$$;

  COUNT(*)
----------
     72885

SQL> insert into devin.test_trun select * from devin.test_trun$$;

72885 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from devin.test_trun;

  COUNT(*)
----------
     72885

离线数据文件恢复测试

新版fy_recover_data支持离线数据文件扫描恢复，测试如下：

SQL> create table devin.test_trun as select * from dba_objects;

Table created.

SQL> select count(*) from devin.test_trun;

  COUNT(*)
----------
     72901

SQL> select table_name,tablespace_name from dba_tables where table_Name='TEST_TRUN';

TABLE_NAME            TABLESPACE_NAME
--------------------------------------------------------
TEST_TRUN               DEVIN

SQL> truncate table devin.test_trun;

Table truncated.

SQL> !cp /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf /tmp/datafile_devin.dbf

SQL> exec fy_recover_data.recover_truncated_table('DEVIN','TEST_TRUN',1,'/tmp','/tmp/datafile_devin.dbf');
17:10:53: Use existing Directory Name: FY_DATA_DIR
17:10:54: Recover Table: DEVIN.TEST_TRUN$
17:10:54: Restore Table: DEVIN.TEST_TRUN$$
17:10:59: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
17:10:59: begin to recover table DEVIN.TEST_TRUN
17:11:00: Use existing Directory Name: TMP_HF_DIR
17:11:00: Recovering data in datafile /tmp/datafile_devin.dbf
17:11:00: Use existing Directory Name: FY_DATA_DIR
17:12:08: 1421 truncated data blocks found.
17:12:08: 72901 records recovered in backup table DEVIN.TEST_TRUN$$
17:12:08: Total: 1421 truncated data blocks found.
17:12:08: Total: 72901 records recovered in backup table DEVIN.TEST_TRUN$$
17:12:08: Recovery completed.
17:12:08: Data has been recovered to DEVIN.TEST_TRUN$$

PL/SQL procedure successfully completed.

SQL> select count(*) from devin.test_trun;

  COUNT(*)
----------
         0

SQL> select count(*) from devin.test_trun$$;

  COUNT(*)
----------
     72901

SQL> insert into devin.test_trun select * from devin.test_trun$$;

72901 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from devin.test_trun;

  COUNT(*)
----------
     72901