truncate表後恢復方法總結

1.1 BLOG文件結構圖

1.2 前言部分

1.2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① truncate操作後的恢復方法（重點）

Tips：

① 若文章程式碼格式有錯亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

② 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- ------------------- ---------- ---------

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZFXXDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZFXXDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

1.2.2 相關參考文章連結

1.2.3 本文簡介

truncate操作是比較危險的操作，不記錄redo，不能通過閃回查詢來找回資料，但是隻要段所佔用的塊沒有全部被重新佔用的情況下，我們還是可以通過一些特殊的辦法來找回truncate掉的資料，因為當Truncate命令發起之後，Oracle實際上並沒有在刪除底層資料塊上的資料，而是要等到重用的時候才會把這一部分資料回收，於是這給了我們一個能夠恢復資料庫的機會。

總體而言，恢復的辦法是通過一些大牛寫的工具來恢復，分為收費和免費的，我們下邊分別說明。實驗部分我們只實驗fy_recover_data包和gdul工具。

有的實驗是很久之前做的，這篇文章釋出太晚，因為中間學習了DUL和BBED的相關知識。

1.3 收費軟體

這裡簡單列舉一下，具體內容請到相關網站了解：

工具名稱	下載地址	作者	軟體
ODU	http://www.oracleodu.com/cn/	老熊	命令列操作
PRM-DUL	http://www.parnassusdata.com/	Maclean Liu	圖形介面操作
AUL/mydul	http://www.dbatools.net/mydul/	d.c.b.a/樓方鑫	命令列

1.4 免費軟體

1.4.1 fy_recover_data包

作者個人資訊：

WWW.HelloDBA.COM

Created By: Fuyuncat

Created Date: 08/08/2012

Email: Fuyuncat@gmail.com

Latest Version: http://www.HelloDBA.com/download/FY_Recover_Data.zip

該包採用純plsql語句恢復被truncate掉的表，操作比較簡單，下載可以去官網下載，或者小麥苗的雲盤共享目錄。

Fy_Recover_Data是利用Oracle表掃描機制、資料嫁接機制恢復TRUNCATE或者損壞資料的工具包。由純PLSQL編寫，原理圖如下：

包內容：

1.4.2 gdul工具

GDUL是老耿開發的一款類dul工具，當資料庫由於某種原因無法開啟時，可以利用GDUL把表資料直接讀取出來，工具下載地址參考小麥苗的blog，老耿的資訊如下：

*********************************************************************

GDUL for ORACLE DB.

Version 4.0.0.1, build date: 2016.04.12.

Email: dbtool@aliyun.com

WeChat official account: dbtool

QQ group: 235019291

*********************************************************************

1.4.2.1 gDUL功能特點

v 完整支援多種格式匯出，包括expdp,exp,text格式。目前市面上的類dul工具只有gDUL支援expdp格式。

v 支援ASM檔案系統，並內建asmcmd命令。

v 支援絕大多數列型別，支援常見的NUMBER,CHAR, VARCHAR2, DATE，LOB, LONG等型別。。其中 SecureFile LOB 支援壓縮，尚不支援去重和加密。

v 支援匯出常規表、IOT、Cluster 表、分割槽表、壓縮表。

v 支援 truncated 表、刪除行恢復。

v 支援常規表空間和 bigfile 表空間。

v 支援主流硬體平臺(HP-UX,AIX, Solaris, Linux, Windows)，各個平臺僅需單一的可執行檔案，方便分發。

v 重點是——永久免費使用，無需額外費用，不開源。

1.4.3 dul

DUL 是 Data Unloader 的縮寫，是一個荷蘭的 Oracle 工程師開發的，他的名字為 Bernard Van Duijnen。 DUL 是一個 C 開發的小程式，編譯後整個程式只有一個檔案，大小也不過幾百 KB，它工作時不需 Oracle RDBMS 以及任何的 Oracle 的程式、元件，它可以直接從一個壞了資料庫的資料檔案中讀取資料，生成 IMP 或 SQL*Loader 可以識別的檔案。

DUL 不是一個商用化的產品，Oracle 不賣、不提供也不支援它的使用。DUL 只有在 Oracle 的內部網站才可以下載到，因此也只有 Oracle 的 Supporter 才能下載到有這個工具，如果與 Oracle 的 Supporter 熟悉，沒準他私底下會給你一個，這個工具也因此有一些流落到民間，被一些人收入囊中，奉為珍寶。

不同的平臺、不同版本的資料庫都有相應的 DUL 軟體，9.x 及之前 DUL 是沒有 License 限制的，也就是有這個工具可以無限制的使用，不過最新的 DUL 在這方面已經改進了，kamus 說最新 DUL 拿到手只能用一個月。

關於這一小點稍總結一下，獲得 DUL 有以下幾種途徑：

如果你是 Oracle 的 Supporter ，可以在內部網站下載，地址為： http://www.nl.oracle.com/support/dul/

如果你有 Oracle 的 Supporter 的朋友可以向他們要一個，itpub 也幾位斑竹都到 Oracle 了，如 coolyl，kamus,lunar。

一些 dul 流落到民間，可以向有這軟體的朋友要一個，不過他們不一定有你需要的那個。

所以關於DUL我們不做過多的解釋。

1.4.4 bbed來恢復

這個比較複雜，若對oracle不熟悉或者bbed不熟悉都不推薦使用這個，具體案例參考：http://blog.itpub.net/26736162/viewspace-2080727/

第二章實驗部分

2.1 實驗環境介紹

專案	db
db 型別	單例項
db version	11.2.0.4.0
db 儲存	FS
主機IP地址/hosts配置	192.168.59.129
OS版本及kernel版本	AIX 7.1 64位
歸檔模式	Archive Mode
ORACLE_SID	oralhr

2.2 實驗目標

將truncate掉的表資料成功找回。

2.3 實驗過程

2.3.1 fy_recover_data包恢復truncate的表

[ZFXDESKDB1:oracle]:/oracle>ORACLE_SID=oraESKDB1

[ZFXDESKDB1:oracle]:/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 15:51:55 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SYS@oraESKDB1> set time on;

15:52:10 SYS@oraESKDB1> set timing on;

15:52:10 SYS@oraESKDB1> set serveroutput on;

15:52:10 SYS@oraESKDB1> create table scott.TB_0321 as SELECT * FROM dba_objects;

Table created.

Elapsed: 00:00:00.59

15:52:18 SYS@oraESKDB1> SELECT COUNT(1) FROM scott.TB_0321;

COUNT(1)

----------

86651

Elapsed: 00:00:00.19

15:52:24 SYS@oraESKDB1> INSERT INTO scott.TB_0321 SELECT * FROM scott.TB_0321;

86651 rows created.

Elapsed: 00:00:00.26

15:52:30 SYS@oraESKDB1> COMMIT;

Commit complete.

Elapsed: 00:00:00.01

15:52:30 SYS@oraESKDB1> INSERT INTO scott.TB_0321 SELECT * FROM scott.TB_0321;

COMMIT;

173302 rows created.

Elapsed: 00:00:00.43

15:53:02 SYS@oraESKDB1> SELECT COUNT(1) FROM scott.TB_0321;

COUNT(1)

----------

346604

Elapsed: 00:00:00.27

16:15:18 SYS@oraESKDB1> SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0321';

D.BYTES/1024/1024

-----------------

40

Elapsed: 00:00:00.44

16:15:25 SYS@oraESKDB1> truncate table scott.TB_0321;

Table truncated.

Elapsed: 00:00:00.20

16:15:46 SYS@oraESKDB1> SELECT COUNT(1) FROM scott.TB_0321;

COUNT(1)

----------

0

Elapsed: 00:00:00.01

====》資料已經被truncate掉了，下邊我們來恢復

16:15:52 SYS@oraESKDB1> @/oracle/FY_Recover_Data.pck

Package created.

Elapsed: 00:00:00.06

Package body created.

Elapsed: 00:00:00.03

16:15:59 SYS@oraESKDB1> exec fy_recover_data.recover_truncated_table('scott','TB_0321');

16:16:06: Use existing Directory Name: FY_DATA_DIR

16:16:07: Recover Table: SCOTT.TB_0321$

16:16:09: Restore Table: SCOTT.TB_0321$$

16:16:24: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1

16:16:24: begin to recover table SCOTT.TB_0321

16:16:24: Use existing Directory Name: TMP_HF_DIR

16:17:09: Recovering data in datafile +DATA/oraeskdb/datafile/users.351.902678817

16:17:09: Use existing Directory Name: TMP_HF_DIR

16:39:16: 4984 truncated data blocks found.

16:39:16: 346604 records recovered in backup table SCOTT.TB_0321$$

16:39:17: Total: 4984 truncated data blocks found.

16:39:17: Total: 346604 records recovered in backup table SCOTT.TB_0321$$

16:39:17: Recovery completed.

16:39:17: Data has been recovered to SCOTT.TB_0321$$

PL/SQL procedure successfully completed.

Elapsed: 00:23:11.59

16:39:17 SYS@oraESKDB1> SELECT COUNT(1) FROM scott.TB_0321$$;

COUNT(1)

----------

346604

Elapsed: 00:00:01.55

16:40:51 SYS@oraESKDB1>

16:40:51 SYS@oraESKDB1> alter table scott.TB_0321 nologging;

Table altered.

Elapsed: 00:00:00.03

16:41:43 SYS@oraESKDB1> insert /*+append*/ into scott.TB_0321 select * from scott.TB_0321$$;

346604 rows created.

Elapsed: 00:00:00.86

16:41:52 SYS@oraESKDB1> commit;

Commit complete.

Elapsed: 00:00:00.01

16:41:55 SYS@oraESKDB1> alter table scott.TB_0321 logging;

Table altered.

Elapsed: 00:00:00.02

16:42:06 SYS@oraESKDB1>

16:42:06 SYS@oraESKDB1> drop tablespace FY_REC_DATA including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:08.00

16:42:35 SYS@oraESKDB1> drop tablespace FY_RST_DATA including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:07.59

16:42:44 SYS@oraESKDB1>

資料成功恢復。

2.3.2 gdul恢復truncate的表

set time on;

set timing on;

set serveroutput on;

drop table scott.TB_0322_05;

create table scott.TB_0322_05 as SELECT * FROM dba_objects;

SELECT COUNT(1) FROM scott.TB_0322_05;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

SELECT COUNT(1) FROM scott.TB_0322_05;

SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0322_05';

truncate table scott.TB_0322_05;

alter system checkpoint;

col ownere format a10

col DIRECTORY_NAME format a30

col DIRECTORY_PATH format a50

select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

bootstrap

desc scott.TB_0322_05

unload table scott.TB_0322_05

scan tablespace 4

untrunc table scott.TB_0322_05

cp SCOTT_TB_0322_05.dmp /oracle/app/oracle/admin/oralhr/dpdump/

impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05

15:41:04 SQL> set time on;

15:59:49 SQL> set timing on;

15:59:49 SQL> set serveroutput on;

15:59:49 SQL> drop table scott.TB_0322_05;

create table scott.TB_0322_05 as SELECT * FROM dba_objects;

SELECT COUNT(1) FROM scott.TB_0322_05;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

Table dropped.

Elapsed: 00:00:00.07

15:59:49 SQL> COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

SELECT COUNT(1) FROM scott.TB_0322_05;

SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0322_05';

truncate table scott.TB_0322_05;

alter system checkpoint;

Table created.

Elapsed: 00:00:00.97

15:59:50 SQL> 15:59:50 SQL>

COUNT(1)

----------

75707

Elapsed: 00:00:00.86

15:59:51 SQL>

75707 rows created.

Elapsed: 00:00:00.23

15:59:52 SQL>

Commit complete.

Elapsed: 00:00:00.17

15:59:52 SQL>

151414 rows created.

Elapsed: 00:00:00.50

15:59:52 SQL>

Commit complete.

Elapsed: 00:00:00.23

15:59:52 SQL>

302828 rows created.

Elapsed: 00:00:01.63

15:59:54 SQL>

Commit complete.

Elapsed: 00:00:00.22

15:59:54 SQL>

605656 rows created.

Elapsed: 00:00:06.19

16:00:00 SQL>

Commit complete.

Elapsed: 00:00:00.02

16:00:01 SQL>

COUNT(1)

----------

1211312

Elapsed: 00:00:00.07

16:00:01 SQL> 16:00:01 SQL>

D.BYTES/1024/1024

-----------------

136

Elapsed: 00:00:00.17

16:00:01 SQL> 16:00:01 SQL> 16:00:01 SQL>

Table truncated.

Elapsed: 00:00:01.26

16:00:02 SQL> 16:00:02 SQL>

System altered.

Elapsed: 00:00:00.15

16:00:02 SQL>

16:00:02 SQL> SELECT COUNT(1) FROM scott.TB_0322_05;

COUNT(1)

----------

0

Elapsed: 00:00:00.00

16:02:35 SQL>

[oracle@ZFFR4CB1101:/home/oracle/gdul]$ ./gdul

*********************************************************************

GDUL for ORACLE DB.

Version 3.5.0.1, build date: 2016.03.07.

Email: gengyonghui@aliyun.com

QQ group: 235019291, WeChat Official Account: dbtool

*********************************************************************

GDUL> bootstrap

Bootstrap finish.

GDUL> desc scott.TB_0322_05

object_id: 78302, dataobj#: 78303, cluster tab#: 0

segment header: (ts#: 4, rfile#: 4, block#: 682))

Seg Column# Column# Name Null? Type

------------ ---------- -------------------- --------------- --------------

1 1 OWNER VARCHAR2(30)

2 2 OBJECT_NAME VARCHAR2(128)

3 3 SUBOBJECT_NAME VARCHAR2(30)

4 4 OBJECT_ID NUMBER

5 5 DATA_OBJECT_ID NUMBER

6 6 OBJECT_TYPE VARCHAR2(19)

7 7 CREATED DATE

8 8 LAST_DDL_TIME DATE

9 9 TIMESTAMP VARCHAR2(19)

10 10 STATUS VARCHAR2(7)

11 11 TEMPORARY VARCHAR2(1)

12 12 GENERATED VARCHAR2(1)

13 13 SECONDARY VARCHAR2(1)

14 14 NAMESPACE NUMBER

15 15 EDITION_NAME VARCHAR2(30)

GDUL> unload table scott.TB_0322_05

2016-03-22 16:01:54...unloaded "SCOTT"."TB_0322_05" 0 rows

GDUL> scan tablespace 4

start scan tablespace 4...

scan tablespace completed.

GDUL> untrunc table scott.TB_0322_05

2016-03-22 16:04:29...untruncating table TB_0322_05 1211312 rows unloaded.

GDUL>

16:02:35 SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------ ------------------------------ -----------------------------------------------------------------------

SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

8 rows selected.

Elapsed: 00:00:00.00

16:05:29 SQL>

[oracle@ZFFR4CB1101:/home/oracle/gdul/dump]$ impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05

Import: Release 11.2.0.3.0 - Production on Tue Mar 22 16:16:48 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."TB_0322_05" 117.1 MB 1211312 rows

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:16:59

[oracle@ZFFR4CB1101:/home/oracle/gdul/dump]$

[oracle@ZFFR4CB2101:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 22 16:17:39 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> SELECT COUNT(1) FROM scott.TB_0322_05;

COUNT(1)

----------

1211312

SQL>

資料成功恢復。

2.4 實驗總結

總體而言用fy_recover_data包或GDUL工具都是非常好的，fy_recover_data可以恢復truncate的資料，但不能恢復drop的資料，而GDUL工具就比較全面了，具體可以參考前邊的簡介或下載文件來看，小麥苗的共享雲盤裡也有比較全的文件。

---------------------------------------------------------------------------------------------------------------------

About Me

...............................................................................................................................

● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2082965/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2016-03-10 10:00~ 2016-04-15 19:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。