oracle資料庫災難挽救應急方案之DDL誤操作恢復（truncate）

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

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;