Oracle中使用包FY_Recover_Data.pck来恢复truncate误操作的表
【摘要】 一、简介 二、实验环境介绍 三、 实验代码 四、实验过程 4.1、准备数据 4.2、执行误操作 4.3、开始恢复 4.4、清理临时数据 一、简介之前整理的内容：http://blog.itpub.net/26736162/viewspace-2082965/误操作执行truncate后的恢复方法很多，这里再次介绍fy_recover_data包，由大师Fuyunca开发。该包采用纯plsq...
一、简介
之前整理的内容：http://blog.itpub.net/26736162/viewspace-2082965/
误操作执行truncate后的恢复方法很多，这里再次介绍fy_recover_data包，由大师Fuyunca开发。该包采用纯plsql语句恢复被truncate掉的表，操作比较简单，包的内容参考本文后边的附录部分。
Fy_Recover_Data是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包。由纯PLSQL编写，原理图如下：
二、实验环境介绍
| 项目 | db |
|---|---|
| db 类型 | 单实例 |
| db version | 19.3.0.0 |
| db 存储 | FS |
| 主机IP地址/hosts配置 | 192.168.66.35 |
| OS版本及kernel版本 | CentOS 7.6 |
| 归档模式 | Archive Mode |
| ORACLE_SID | lhrsdb |
三、 实验代码
-- 准备数据
set timing on;
set serveroutput on;
create table lhr.LHRTB_210702 as SELECT * FROM dba_objects;
SELECT COUNT(1) FROM lhr.LHRTB_210702;
INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;
INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;
commit;
SELECT COUNT(1) FROM lhr.LHRTB_210702;
SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='LHRTB_210702';
truncate table lhr.LHRTB_210702;
SELECT COUNT(1) FROM lhr.LHRTB_210702;
-- 数据已经被truncate掉了，下边我们来恢复
@/tmp/FY_Recover_Data.pck
exec fy_recover_data.recover_truncated_table('LHR','LHRTB_210702');
SELECT COUNT(1) FROM lhr.LHRTB_210702$$;
alter table lhr.LHRTB_210702 nologging;
insert /*+append*/ into lhr.LHRTB_210702 select * from lhr.LHRTB_210702$$;
commit;
alter table lhr.LHRTB_210702 logging;
SELECT COUNT(1) FROM lhr.LHRTB_210702;
-- 清理数据
drop tablespace FY_REC_DATA including contents and datafiles;
drop tablespace FY_RST_DATA including contents and datafiles;
四、实验过程
4.1、准备数据
Microsoft Windows [版本 10.0.17134.765]
(c) 2018 Microsoft Corporation。保留所有权利。
C:\Users\lhrxxt>sqlplus sys/lhr@192.168.66.35:11521/lhrsdb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 2 16:00:36 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SYS@192.168.66.35:11521/lhrsdb> set timing on;
SYS@192.168.66.35:11521/lhrsdb> set serveroutput on;
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> select * from v$version;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
Version 19.3.0.0.0
Elapsed: 00:00:00.15
SYS@192.168.66.35:11521/lhrsdb> create user lhr identified by lhr;
User created.
Elapsed: 00:00:00.26
SYS@192.168.66.35:11521/lhrsdb> grant dba to lhr;
Grant succeeded.
Elapsed: 00:00:00.12
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> create table lhr.LHRTB_210702 as SELECT * FROM dba_objects;
Table created.
Elapsed: 00:00:01.83
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;
72690 rows created.
Elapsed: 00:00:00.67
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;
145380 rows created.
Elapsed: 00:00:00.91
SYS@192.168.66.35:11521/lhrsdb> commit;
Commit complete.
Elapsed: 00:00:00.11
SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702;
COUNT(1)
----------
290760
Elapsed: 00:00:00.19
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='LHRTB_210702';
D.BYTES/1024/1024
-----------------
45
Elapsed: 00:00:00.60
4.2、执行误操作
SYS@192.168.66.35:11521/lhrsdb> truncate table lhr.LHRTB_210702;
Table truncated.
Elapsed: 00:00:00.59
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702;
COUNT(1)
----------
0
Elapsed: 00:00:00.18
4.3、开始恢复
SYS@192.168.66.35:11521/lhrsdb> @D:\FY_Recover_Data.pck
Package created.
Elapsed: 00:00:00.29
Package body created.
Elapsed: 00:00:00.67
SYS@192.168.66.35:11521/lhrsdb> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
1 SYSAUX YES NO YES 0
0 SYSTEM YES NO YES 0
2 UNDOTBS1 YES NO YES 0
4 USERS YES NO YES 0
3 TEMP NO NO YES 0
Elapsed: 00:00:00.16
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> exec fy_recover_data.recover_truncated_table('LHR','LHRTB_210702');
16:03:26: New Directory Name: FY_DATA_DIR
16:03:26: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
16:03:26: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
16:03:27: Recover Table: LHR.LHRTB_210702$
16:03:27: Restore Table: LHR.LHRTB_210702$$
16:03:49: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
16:03:49: begin to recover table LHR.LHRTB_210702
16:03:50: New Directory Name: TMP_HF_DIR
16:03:52: Recovering data in datafile /opt/oracle/oradata/LHRSDB/users01.dbf
16:03:52: Use existing Directory Name: TMP_HF_DIR
16:06:25: 5662 truncated data blocks found.
16:06:25: 290760 records recovered in backup table LHR.LHRTB_210702$$
16:06:25: Total: 5662 truncated data blocks found.
16:06:25: Total: 290760 records recovered in backup table LHR.LHRTB_210702$$
16:06:25: Recovery completed.
16:06:25: Data has been recovered to LHR.LHRTB_210702$$
PL/SQL procedure successfully completed.
Elapsed: 00:02:59.27
SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702$$;
COUNT(1)
----------
290760
Elapsed: 00:00:00.52
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> alter table lhr.LHRTB_210702 nologging;
Table altered.
Elapsed: 00:00:00.25
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> insert /*+append*/ into lhr.LHRTB_210702 select * from lhr.LHRTB_210702$$;
290760 rows created.
Elapsed: 00:00:01.99
SYS@192.168.66.35:11521/lhrsdb> commit;
Commit complete.
Elapsed: 00:00:00.91
SYS@192.168.66.35:11521/lhrsdb> alter table lhr.LHRTB_210702 logging;
Table altered.
Elapsed: 00:00:00.11
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702;
COUNT(1)
----------
290760
Elapsed: 00:00:00.25
4.4、清理临时数据
SYS@192.168.66.35:11521/lhrsdb> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
1 SYSAUX YES NO YES 0
0 SYSTEM YES NO YES 0
2 UNDOTBS1 YES NO YES 0
4 USERS YES NO YES 0
3 TEMP NO NO YES 0
6 FY_REC_DATA YES NO YES 0
7 FY_RST_DATA YES NO YES 0
7 rows selected.
SYS@192.168.66.35:11521/lhrsdb> set pagesize 9999 line 9999
SYS@192.168.66.35:11521/lhrsdb> col TS_Name format a30
SYS@192.168.66.35:11521/lhrsdb> col PDBNAME format a15
SYS@192.168.66.35:11521/lhrsdb> col TS_NAME format a20
SYS@192.168.66.35:11521/lhrsdb> col LOGGING format a10
SYS@192.168.66.35:11521/lhrsdb> WITH wt1 AS
2 (SELECT ts.TABLESPACE_NAME,
3 df.all_bytes,
4 decode(df.TYPE,
5 'D',
6 nvl(fs.FREESIZ, 0),
7 'T',
8 df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
9 df.MAXSIZ,
10 ts.BLOCK_SIZE,
11 ts.LOGGING,
12 ts.FORCE_LOGGING,
13 ts.CONTENTS,
14 ts.EXTENT_MANAGEMENT,
15 ts.SEGMENT_SPACE_MANAGEMENT,
16 ts.RETENTION,
17 ts.DEF_TAB_COMPRESSION,
18 df.ts_df_count
19 FROM dba_tablespaces ts,
20 (SELECT 'D' TYPE,
21 TABLESPACE_NAME,
22 COUNT(*) ts_df_count,
23 SUM(BYTES) all_bytes,
24 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
25 FROM dba_data_files d
26 GROUP BY TABLESPACE_NAME
27 UNION ALL
28 SELECT 'T',
29 TABLESPACE_NAME,
30 COUNT(*) ts_df_count,
31 SUM(BYTES) all_bytes,
32 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
33 FROM dba_temp_files d
34 GROUP BY TABLESPACE_NAME) df,
35 (SELECT TABLESPACE_NAME,
36 SUM(BYTES) FREESIZ
37 FROM dba_free_space
38 GROUP BY TABLESPACE_NAME
39 UNION ALL
40 SELECT tablespace_name,
41 SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
42 FROM gv$sort_usage a,
43 dba_tablespaces d
44 WHERE a.tablespace = d.tablespace_name
45 GROUP BY tablespace_name) fs
46 WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
47 AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
48 SELECT (SELECT A.TS#
49 FROM V$TABLESPACE A
50 WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
51 t.TABLESPACE_NAME TS_Name,
52 round(t.all_bytes / 1024 / 1024) ts_size_M,
53 round(t.freesiz / 1024 / 1024) Free_Size_M,
54 round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
55 round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
56 round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g,
57 round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /
58 MAXSIZ,
59 3) USED_per_MAX,
60 round(t.BLOCK_SIZE) BLOCK_SIZE,
61 t.LOGGING,
62 t.ts_df_count
63 FROM wt1 t
64 UNION ALL
65 SELECT to_number('') TS#,
66 'ALL TS:' TS_Name,
67 round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
68 round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
69 round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
70 round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,
71 round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,
72 to_number('') "USED,% of MAX Size",
73 to_number('') BLOCK_SIZE,
74 '' LOGGING,
75 to_number('') ts_df_count
76 FROM wt1 t
77 order by TS#
78 ;
TS# TS_NAME TS_SIZE_M FREE_SIZE_M USED_SIZE_M USED_PER MAX_SIZE_G USED_PER_MAX BLOCK_SIZE LOGGING TS_DF_COUNT
---------- -------------------- ---------- ----------- ----------- ---------- ---------- ------------ ---------- ---------- -----------
0 SYSTEM 910 4 906 99.54 32 2.764 8192 LOGGING 1
1 SYSAUX 670 30 640 95.476 32 1.952 8192 LOGGING 1
2 UNDOTBS1 345 271 74 21.413 32 .225 8192 LOGGING 1
3 TEMP 33 27 6 18.182 32 .018 8192 NOLOGGING 1
4 USERS 344 2 341 99.291 32 1.042 8192 LOGGING 1
6 FY_REC_DATA 0 0 0 100 0 100 8192 LOGGING 1
7 FY_RST_DATA 57 3 54 95.154 32 .165 8192 LOGGING 1
ALL TS: 2358.625 338 2021 85.678 192
8 rows selected.
Elapsed: 00:00:00.59
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> drop tablespace FY_REC_DATA including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:12.01
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb>
SYS@192.168.66.35:11521/lhrsdb> drop tablespace FY_RST_DATA including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:11.43
SYS@192.168.66.35:11521/lhrsdb> drop table lhr.LHRTB_210702$$;
drop table lhr.LHRTB_210702$$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.15
SYS@192.168.66.35:11521/lhrsdb>
总体而言用fy_recover_data包是非常好的，fy_recover_data可以恢复truncate的数据，但不能恢复drop的数据。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）