truncate恢复方法集合(二）
【转载声明】本文为转载文章，原作者「李华荣」，版权归原作者所有。 原文链接：https://mp.weixin.qq.com/s/mEcv0Qj-6JWjwsv6mvl7Bw
公众号：杨建荣的学习笔记 · 作者：李华荣 · 发布：2016-04-24 23:59:40 · 原文链接
1.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 |
1.2 实验目标
将truncate掉的表数据成功找回。
1.3 实验过程
1.3.1 fy_recover_data包恢复truncate的表
15:52:10SYS@oraESKDB1> create table scott.TB_0321 as SELECT * FROM dba_objects;
Tablecreated.
Elapsed:00:00:00.59
15:53:02SYS@oraESKDB1> SELECT COUNT(1) FROM scott.TB_0321;
COUNT(1)
———-
346604
16:15:25SYS@oraESKDB1> truncate table scott.TB_0321;
Tabletruncated.
16:15:46SYS@oraESKDB1> SELECT COUNT(1) FROM scott.TB_0321;
COUNT(1)
———-
0
Elapsed:00:00:00.01
====》数据已经被truncate掉了，下边我们来恢复
16:15:52SYS@oraESKDB1> @/oracle/FY_Recover_Data.pck
Packagecreated.
Elapsed:00:00:00.06
Packagebody created.
Elapsed:00:00:00.03
16:15:59SYS@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 tableSCOTT.TB_0321$$
16:39:17:Recovery completed.
16:39:17: Data has been recovered to SCOTT.TB_0321$$
PL/SQLprocedure successfully completed.
Elapsed:00:23:11.59
16:39:17SYS@oraESKDB1> SELECT COUNT(1) FROM scott.TB_0321$$;
COUNT(1)
———-
346604
Elapsed:00:00:01.55
16:40:51SYS@oraESKDB1> alter table scott.TB_0321 nologging;
Tablealtered.
Elapsed:00:00:00.03
16:41:43SYS@oraESKDB1> insert /*+append*/ into scott.TB_0321select * from scott.TB_0321$$;
346604rows created.
Elapsed:00:00:00.86
16:41:52SYS@oraESKDB1> commit;
Commitcomplete.
Elapsed:00:00:00.01
数据成功恢复。
1.3.2 gdul恢复truncate的表
16:00:01SQL> SELECT COUNT(1) FROM scott.TB_0322_05;
COUNT(1)
———-
1211312
Elapsed:00:00:00.07
16:00:01SQL> 16:00:01 SQL>
D.BYTES/1024/1024
—————–
136
Elapsed:00:00:00.17
16:00:01SQL> 16:00:01 SQL> 16:00:01 SQL> truncate table scott.TB_0322_05
Tabletruncated.
16:00:02SQL> SELECT COUNT(1) FROM scott.TB_0322_05;
COUNT(1)
———-
0
Elapsed:00:00:00.00
[oracle@ZFFR4CB1101:/home/oracle/gdul]$./gdul
*********************************************************************
GDUL for ORACLE DB.
Version 3.5.0.1, build date: 2016.03.07.
Copyright (c) 2007, 2016. Andy Geng. ALL RIGHTS RESERVED.
Email: gengyonghui@aliyun.com
QQ group: 235019291, WeChat Official Account:dbtool
*********************************************************************
GDUL>bootstrap
Bootstrapfinish.
GDUL>desc scott.TB_0322_05
object_id:78302, dataobj#: 78303, cluster tab#: 0
segmentheader: (ts#: 4, rfile#: 4, block#: 682))
SegColumn# 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-2216:01:54…unloaded “SCOTT”.”TB_0322_05″ 0 rows
GDUL>scan tablespace 4
startscan tablespace 4…
scantablespace completed.
GDUL>untrunc table scott.TB_0322_05
2016-03-2216:04:29…untruncating table TB_0322_05 1211312 rows unloaded.
GDUL>
16:02:35SQL> select * from dba_directories where directoy_name=’DATA_PUMP_DIR’;
OWNER DIRECTORY_NAME DIRECTORY_PATH
———————————————————————————————————————————–
/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
[oracle@ZFFR4CB1101:/home/oracle/gdul/dump]$impdp scott/tigerdirectory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmpLOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05
Mastertable “SCOTT”.”SYS_IMPORT_TABLE_01″ successfullyloaded/unloaded
Starting”SCOTT”.”SYS_IMPORT_TABLE_01″: scott/******** directory=DATA_PUMP_DIRdumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05
Processingobject type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported”SCOTT”.”TB_0322_05″ 117.1 MB 1211312 rows
Job”SCOTT”.”SYS_IMPORT_TABLE_01″ successfully completed at16:16:59
[oracle@ZFFR4CB2101:/home/oracle]$ sqlplus /as sysdba
SQL>SELECT COUNT(1) FROM scott.TB_0322_05;
COUNT(1)
———-
1211312
数据成功恢复。
1.4 实验总结
总体而言用fy_recover_data包或GDUL工具都是非常好的，fy_recover_data可以恢复truncate的数据，但不能恢复drop的数据，而GDUL工具就比较全面了，具体可以参考前边的简介或下载文档来看，小麦苗的共享云盘里也有比较全的文档。
本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
ITPUB BLOG：http://blog.itpub.net/26736162