Oracle使用fy_recover_data恢復truncate刪除的數據

(一)truncate操作概述

在生產中,truncate是使用較多的命令,在使用不當的情況下,往往會造成表的數據全部丟失,恢復較為困難。對於truncate恢復,常見的有以下幾種方法可以進行恢復:

  • 使用數據泵導入。該方法操作簡單,前提是必須要有備份可用,並且會有數據的丟失;
  • 使用RMAN進行不完全恢復。可將資料庫恢復到truncate之前的時刻,但是恢復時間較長;
  • 使用odu、prm-dul、GDUL等收費軟體進行恢復;
  • 使用fy_recover_data包;

使用RMAN進行異機恢復已在之前測試過,詳見://www.cnblogs.com/lijiaman/p/11577001.html

(二)FY_Recover_Data介紹

FY_Recover_Data是中國Oracle ACE大佬黃瑋(個人網站://www.hellodba.com)開發的一個package,該腳本專門用於對truncate的表進行恢復。

根據作者所述,其原理:如果我們已經有一套元數據及數據塊,然後將被TRUNCATE的用戶數據塊的內容取代其用戶數據塊的內容,是否可以「騙」過Oracle,讓它讀出這些數據呢?
回顧一下表掃描的過程,這個方法應該是可行的。我們只要想辦法構造出一個結構相同、且具有完整元數據資訊和格式化了的用戶數據塊的傀儡表對象,然後將被TRUNCATE的用戶數據塊找出,再將其數據內容部分嫁接到傀儡對象的用戶數據塊,使Oracle以外這是傀儡對象的數據,就能讓Oracle掃描並讀出數據內容。其原理用圖示描述如下:

                                                +-------------------------+  
                                                | Copy Of Dummy Data File |  
                                                |  (With Formmated Blocks)|  
                                                +-------------------------+  
                                                            ||  
                                                            \/  
                                                (Blcok Header, Block Tail)  
                                                            ||  
                                                            \/  
+-------------------+                                +----------------+     Table Scan    +---------------+  
| Source Data File  | => (Data Block Content) =>     |  Dummy Table   |    ============>  | Restore Table |  
|(Without Meta Data)|                                |(With Meta Data)|                   +---------------+  
+-------------------+                                +----------------+

FY_Recover_Data對於表恢復的支援性如下:

壓縮表 支援
索引組織表 支援
分區表 支援 
行鏈接/行遷移 不支援
標準SQL類型 支援
BLOB/CLOB 支援Store in Row
離線恢復 支援
作業系統平台 全部
資料庫版本 9i以上

        
本文使用FY_Recover_Data對truncate的幾種情況進行恢複測試,以驗證fy_recover_data的恢復能力。

(三)操作過程記錄

(3.1)使用fy_recover_data包執行truncate恢復,truncate後未有新數據進入表

STEP1:創建測試表,並執行truncate

SQL> create table test01 as select * from dba_objects;

SQL> select count(*) from test01;

  COUNT(*)
----------
     86968

SQL> 
SQL> truncate table test01;

Table truncated

SQL> select count(*) from test01;

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

STEP2:導入FY_Recover_Data.pck包

[oracle@source-node ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 10:50:17 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/FY_Recover_Data.pck          -- 第一次執行發現第30行存在「&」符號,刪除該符號

Enter value for files: 
old  30:   -- 1. Temp Restore and Recover tablespace & files      ---
new  30:   -- 1. Temp Restore and Recover tablespace       ---

Package created.

Warning: Package Body created with compilation errors.

SQL> @/home/oracle/FY_Recover_Data.pck      -- 刪除「&」符號後導入成功

Package created.

Package body created.

STEP3:開始執行恢復,只需要兩個參數:schema和table_name

[oracle@source-node ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 11:11:20 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set time on 
11:11:43 SQL> set serveroutput on
11:11:54 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01');
11:12:01: Use existing Directory Name: FY_DATA_DIR
11:12:02: Recover Table: LIJIAMAN.TEST01$
11:12:02: Restore Table: LIJIAMAN.TEST01$$
11:12:09: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1
11:12:09: begin to recover table LIJIAMAN.TEST01
11:12:09: Use existing Directory Name: TMP_HF_DIR
11:12:09: Recovering data in datafile
/u01/app/oracle/oradata/testdb1/users01.dbf
11:12:09: Use existing Directory Name: TMP_HF_DIR
11:12:39: 1242 truncated data blocks found.
11:12:39: 86968 records recovered in backup table LIJIAMAN.TEST01$$
11:12:39: Total: 1242 truncated data blocks found.
11:12:39: Total: 86968 records recovered in backup table LIJIAMAN.TEST01$$
11:12:39: Recovery completed.
11:12:39: Data has been recovered to LIJIAMAN.TEST01$$

PL/SQL procedure successfully completed.

STEP4:根據恢復日誌,會創建臨時中轉表test01$和test01$$,恢復的數據保存在test01$$中

SQL> show user
User is "LIJIAMAN"

SQL> select count(*) from test01$$;

  COUNT(*)
----------
     86968

--將數據還原到test01表中

SQL> insert into test01 select * from test01$$;

--確認數據已經還原回來

SQL> select count(*) from test01;

  COUNT(*)
----------
     86968

經過測試,如果表被truncate後,未執行其它操作,數據可以使用fy_recover_data恢復回來。

#######################################################################################

(3.2)使用fy_recover_data包執行truncate恢復,truncate後有新數據進入表(新插入的數據比truncate之前多)

STEP1:創建測試表、序列、存儲過程

SQL> create table test01
  2  (
  3      col1      number,
  4      col2      number,
  5      col3      date,
  6      col4      varchar2(30),
  7      col5      varchar2(100)
  8  );

Table created
SQL> --創建自增序列
SQL> CREATE SEQUENCE seq01
  2    START WITH 1
  3    MAXVALUE 99999999
  4    MINVALUE 0
  5    CYCLE
  6    CACHE 10
  7    ORDER;

Sequence created

 
SQL> --創建隨機數據插入存儲過程,其中col1列單調遞增
create or replace procedure p_insert_test01 IS
v_col1 NUMBER;
BEGIN
FOR i IN 1..10000 LOOP
select seq01.nextval INTO v_col1 from dual;
insert into test01(col1,col2,col3,col4,col5)
values
(v_col1,
(select round(dbms_random.value(10000, 100000000)) from dual),
sysdate,
(select dbms_random.string('a', 25) from dual),
(select dbms_random.string('a', 85) from dual));
END LOOP;
commit;
end p_insert_test01;

STEP2:測試表插入10000條數據,col1列的值從1到10000

SQL> exec p_insert_test01;
PL/SQL procedure successfully completed

SQL> select count(*) from test01;

  COUNT(*)
----------
     10000

SQL> SELECT MIN(col1),MAX(col1) FROM test01;

MIN(COL1)  MAX(COL1)
---------- ----------
         1      10000

STEP3: 執行truncate操作

SQL> truncate table test01;

Table truncated

STEP4: 接著往表裡插入20000條數據

SQL> exec p_insert_test01;
PL/SQL procedure successfully completed

SQL> exec p_insert_test01;
PL/SQL procedure successfully completed

SQL> select count(*) from test01;

  COUNT(*)
----------
     20000

SQL> SELECT MIN(col1),MAX(col1) FROM test01;

MIN(COL1)  MAX(COL1)
---------- ----------
     10001      30000

STEP5:執行恢復操作

[oracle@source-node ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 14:00:57 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on
SQL> set time on
14:01:06 SQL> 
14:01:09 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01');
14:01:13: Use existing Directory Name: FY_DATA_DIR
14:01:13: Recover Table: LIJIAMAN.TEST01$
14:01:14: Restore Table: LIJIAMAN.TEST01$$
14:01:18: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1
14:01:18: begin to recover table LIJIAMAN.TEST01
14:01:18: Use existing Directory Name: TMP_HF_DIR
14:01:18: Recovering data in datafile
/u01/app/oracle/oradata/testdb1/users01.dbf
14:01:18: Use existing Directory Name: TMP_HF_DIR
14:01:32: 402 truncated data blocks found.
14:01:32: 20000 records recovered in backup table LIJIAMAN.TEST01$$
14:01:32: Total: 402 truncated data blocks found.
14:01:32: Total: 20000 records recovered in backup table LIJIAMAN.TEST01$$
14:01:32: Recovery completed.
14:01:32: Data has been recovered to LIJIAMAN.TEST01$$

PL/SQL procedure successfully completed.

STEP6: 通過對test01$$表進行確認,發現返回的數據是truncate之後插入的數據,不符合要求

SQL> select count(*) from test01;

  COUNT(*)
----------
     20000
SQL> select count(*) from test01$$;

  COUNT(*)
----------
     20000

SQL> SELECT MIN(col1),MAX(col1) FROM test01;

MIN(COL1)  MAX(COL1)
---------- ----------
     10001      30000

SQL> SELECT MIN(col1),MAX(col1) FROM test01$$;

MIN(COL1)  MAX(COL1)
---------- ----------
     10001      30000

#######################################################################################

(3.3)使用fy_recover_data包執行truncate恢復,truncate後有新數據進入表(新插入的數據比truncate之前少)

STEP1:創建測試表、序列、存儲過程

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as lijiaman@192.168.10.11/testdb1
SQL> DROP TABLE  test01 PURGE;

Table dropped
SQL> create table test01
  2  (
  3  col1 number,
  4  col2 number,
  5  col3 date,
  6  col4 varchar2(30),
  7  col5 varchar2(100)
  8  );

Table created
SQL> DROP SEQUENCE seq01;

Sequence dropped
SQL> --創建自增序列
SQL> CREATE SEQUENCE seq01
  2  START WITH 1
  3  MAXVALUE 99999999
  4  MINVALUE 0
  5  CYCLE
  6  CACHE 10
  7  ORDER;

Sequence created
SQL> --創建隨機數據插入存儲過程,其中col1列單調遞增
SQL> create or replace procedure p_insert_test01 IS
  2  v_col1 NUMBER;
  3  BEGIN
  4  FOR i IN 1..10000 LOOP
  5  select seq01.nextval INTO v_col1 from dual;
  6  insert into test01(col1,col2,col3,col4,col5)
  7  values
  8  (v_col1,
  9  (select round(dbms_random.value(10000, 100000000)) from dual),
 10  sysdate,
 11  (select dbms_random.string('a', 25) from dual),
 12  (select dbms_random.string('a', 85) from dual));
 13  END LOOP;
 14  commit;
 15  end p_insert_test01;

16  /

Procedure created

STEP2:測試表插入10000條數據,col1列的值從1到10000

SQL> exec p_insert_test01;
PL/SQL procedure successfully completed

SQL> select count(*) from test01;

  COUNT(*)
----------
     10000

SQL> SELECT MIN(col1),MAX(col1) FROM test01;

MIN(COL1)  MAX(COL1)
---------- ----------
         1      10000

STEP3:執行truncate操作

SQL> truncate table test01;

Table truncated

STEP4:修改存儲過程,酶促插入100條數據

SQL> --創建隨機數據插入存儲過程,其中col1列單調遞增
SQL> create or replace procedure p_insert_test01 IS
  2  v_col1 NUMBER;
  3  BEGIN
  4  FOR i IN 1..100 LOOP
  5  select seq01.nextval INTO v_col1 from dual;
  6  insert into test01(col1,col2,col3,col4,col5)
  7  values
  8  (v_col1,
  9  (select round(dbms_random.value(10000, 100000000)) from dual),
10  sysdate,
11  (select dbms_random.string('a', 25) from dual),
12  (select dbms_random.string('a', 85) from dual));
 13  END LOOP;
 14  commit;
 15  end p_insert_test01;
 16  /

Procedure created

-- 測試表插入100條數據
SQL> exec p_insert_test01;
PL/SQL procedure successfully completed

STEP5:執行恢復操作

[oracle@source-node ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 14:22:34 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set time on
14:22:39 SQL> set serveroutput on
14:22:44 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01');
14:22:52: Use existing Directory Name: FY_DATA_DIR
14:22:52: Recover Table: LIJIAMAN.TEST01$
14:22:52: Restore Table: LIJIAMAN.TEST01$$
14:22:57: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1
14:22:57: begin to recover table LIJIAMAN.TEST01
14:22:57: Use existing Directory Name: TMP_HF_DIR
14:22:57: Recovering data in datafile
/u01/app/oracle/oradata/testdb1/users01.dbf
14:22:57: Use existing Directory Name: TMP_HF_DIR
14:23:06: 5 truncated data blocks found.
14:23:06: 100 records recovered in backup table LIJIAMAN.TEST01$$
14:23:06: Total: 5 truncated data blocks found.
14:23:06: Total: 100 records recovered in backup table LIJIAMAN.TEST01$$
14:23:06: Recovery completed.
14:23:06: Data has been recovered to LIJIAMAN.TEST01$$

PL/SQL procedure successfully completed.

STEP6: 通過對test01$$表進行確認,發現返回的數據是truncate之後插入的數據,不符合要求

SQL> select count(*) from test01;

  COUNT(*)
----------
       100

SQL> SELECT MIN(col1),MAX(col1) FROM test01;

MIN(COL1)  MAX(COL1)
---------- ----------
     10001      10100

SQL> 
SQL> select count(*) from test01$$;

  COUNT(*)
----------
       100

SQL> SELECT MIN(col1),MAX(col1) FROM test01$$;

MIN(COL1)  MAX(COL1)
---------- ----------
     10001      10100

#######################################################################################
(3.4)測試數據文件被覆蓋是否影響恢復

STEP1:創建測試表

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as lijiaman@192.168.10.11/testdb1

SQL> create table test01
  2  (
  3  col1 number,
  4  col2 number,
  5  col3 date,
  6  col4 varchar2(30),
  7  col5 varchar2(100)
  8  ) TABLESPACE USERS;

Table created

STEP2: 初始時候,表空間總共20MB,剩餘15.94MB

SQL> SELECT  SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
  2      round(SUM(a.bytes/1024/1024),2)  AS "Totle_size(MB)",
  3      round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)",
  4      round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2)  AS "Used_space(MB)",
  5      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%",
  6      round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2)                AS "Max_size(MB)",
  7      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%"
  8    FROM dba_data_files a,
  9      (SELECT SUM(NVL(bytes,0)) free_space1,
 10        file_id
 11      FROM dba_free_space
 12    GROUP BY file_id
 13     ) b
 14  WHERE a.file_id = b.file_id(+)
 15  AND   a.TABLESPACE_NAME = 'USERS'
 16   GROUP BY a.TABLESPACE_NAME;

TABLESPACENAME                                                                   Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent%
-------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------
USERS                                                                                        20          15.94           4.06         20.31           20        20.31

STEP3:test01表插入大量數據

SQL> exec p_insert_test01;
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
PL/SQL procedure successfully completed

SQL> /
begin p_insert_test01; end;

ORA-01653: unable to extend table LIJIAMAN.TEST01 by 128 in tablespace USERS
ORA-06512: at "LIJIAMAN.P_INSERT_TEST01", line 6
ORA-06512: at line 1

STEP4:此時,表空間總共20MB,剩餘0.94MB

SQL> SELECT  SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
  2      round(SUM(a.bytes/1024/1024),2)  AS "Totle_size(MB)",
  3      round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)",
  4      round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2)  AS "Used_space(MB)",
  5      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%",
  6      round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2)                AS "Max_size(MB)",
  7      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%"
  8    FROM dba_data_files a,
  9      (SELECT SUM(NVL(bytes,0)) free_space1,
 10        file_id
 11      FROM dba_free_space
 12    GROUP BY file_id
 13     ) b
 14  WHERE a.file_id = b.file_id(+)
 15  AND   a.TABLESPACE_NAME = 'USERS'
 16   GROUP BY a.TABLESPACE_NAME;

TABLESPACENAME                                                                   Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent%
-------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------
USERS                                                                                        20           0.94          19.06         95.31           20        95.31

STEP5:此時test01表有90000行數據

SQL> select count(*) from test01;

  COUNT(*)
----------
     90000

SQL> SELECT MIN(col1),MAX(col1) FROM test01;

MIN(COL1)  MAX(COL1)
---------- ----------
    109751     199750

STEP6:對test01執行truncate

SQL> truncate table test01;

Table truncated

STEP7:執行truncate後,空間已經釋放

SQL> SELECT  SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
  2      round(SUM(a.bytes/1024/1024),2)  AS "Totle_size(MB)",
  3      round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)",
  4      round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2)  AS "Used_space(MB)",
  5      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%",
  6      round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2)                AS "Max_size(MB)",
  7      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%"
  8    FROM dba_data_files a,
  9      (SELECT SUM(NVL(bytes,0)) free_space1,
 10        file_id
 11      FROM dba_free_space
 12    GROUP BY file_id
 13     ) b
 14  WHERE a.file_id = b.file_id(+)
 15  AND   a.TABLESPACE_NAME = 'USERS'
 16   GROUP BY a.TABLESPACE_NAME;

TABLESPACENAME                                                                   Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent%
-------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------
USERS                                                                                        20          15.88           4.12         20.63           20        20.63

STEP8:創建表test02,用來覆蓋test01釋放的空間

SQL> create table test02 as select * from dba_objects;

Table created

STEP9:test02表創建之後,剩餘空間為5.88MB,可以說明:test02表的數據佔用了test01表釋放出來的空間,即test01表的部分數據已經被覆蓋

SQL> SELECT  SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
  2      round(SUM(a.bytes/1024/1024),2)  AS "Totle_size(MB)",
  3      round(SUM(NVL(b.free_space1/1024/1024,0)),2) AS "Free_space(MB)",
  4      round(SUM(a.bytes/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024,0)),2)  AS "Used_space(MB)",
  5      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0))) *100/SUM(a.bytes/1024/1024),2) AS "Used_percent%",
  6      round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2)                AS "Max_size(MB)",
  7      ROUND((SUM(a.bytes/1024/1024)-SUM(NVL(b.free_space1/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024),2) AS "Max_percent%"
  8    FROM dba_data_files a,
  9      (SELECT SUM(NVL(bytes,0)) free_space1,
 10        file_id
 11      FROM dba_free_space
 12    GROUP BY file_id
 13     ) b
 14  WHERE a.file_id = b.file_id(+)
 15  AND   a.TABLESPACE_NAME = 'USERS'
 16   GROUP BY a.TABLESPACE_NAME;

TABLESPACENAME                                                                   Totle_size(MB) Free_space(MB) Used_space(MB) Used_percent% Max_size(MB) Max_percent%
-------------------------------------------------------------------------------- -------------- -------------- -------------- ------------- ------------ ------------
USERS                                                                                        20           5.88          14.12         70.63           20        70.63

STEP10:執行恢復操作

[oracle@source-node ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 21 15:09:58 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set time on
15:10:05 SQL> set serveroutput on
15:10:10 SQL> exec fy_recover_data.recover_truncated_table('LIJIAMAN','TEST01');
15:10:17: Use existing Directory Name: FY_DATA_DIR
15:10:17: Recover Table: LIJIAMAN.TEST01$
15:10:17: Restore Table: LIJIAMAN.TEST01$$
15:10:22: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1
15:10:22: begin to recover table LIJIAMAN.TEST01
15:10:22: Use existing Directory Name: TMP_HF_DIR
15:10:22: Recovering data in datafile
/u01/app/oracle/oradata/testdb1/users01.dbf
15:10:22: Use existing Directory Name: TMP_HF_DIR
15:10:31: 645 truncated data blocks found.
15:10:31: 24439 records recovered in backup table LIJIAMAN.TEST01$$
15:10:31: Total: 645 truncated data blocks found.
15:10:31: Total: 24439 records recovered in backup table LIJIAMAN.TEST01$$
15:10:31: Recovery completed.
15:10:31: Data has been recovered to LIJIAMAN.TEST01$$

PL/SQL procedure successfully completed.

STEP11: 發現只恢復了部分數據,不符合要求

-- truncate之前test01表有90000行數據,恢復了24339行數據

SQL> select count(*) from test01$$;

  COUNT(*)
----------
     24439

SQL> SELECT MIN(col1),MAX(col1) FROM test01;

MIN(COL1)  MAX(COL1)
---------- ----------

SQL> 
SQL> SELECT MIN(col1),MAX(col1) FROM test01$$;

MIN(COL1)  MAX(COL1)
---------- ----------
    109751     199750

(四)總結

對於使用工具fy_recover_data進行數據恢復,需要確保:

①truncate之後,需要保證沒有新的數據進入表中,否則無法還原;

②存放該表的數據文件塊不能被覆蓋,否則無法完整還原數據。

在發生故障後,可以迅速使用:

SQL> alter tablespace users read only;
SQL> alter tablespace users read write;

來關閉/開啟表空間的寫功能,這樣可以保證數據文件不會被覆寫。

【完】


     Copyright © 2020 ruiliair. All Rights Reserved.