案例:Oracle dul数据挖掘 没有备份情况下非常规恢复drop删除的数据表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了案例:Oracle dul数据挖掘 没有备份情况下非常规恢复drop删除的数据表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9870字，纯文字阅读大概需要15分钟。
内容图文
通过Oracle dul工具在没有备份情况下进行非常规恢复，找出drop删除的Oracle数据表中的数据进行恢复
dul对被drop对象进行恢复，需要提供两个信息
1.被删除表所属表空间(非必须)
2.被删除表结构(必须)
1.Oracle数据库中模拟删除表
--创建测试表
SQL> create table t_dul_drop tablespace czum
2 as
3 select * from dba_tables;
Table created.
--备份被删除表数据，便于比较和提供测试表结构
SQL> create table t_dul_drop_bak tablespace users
2 as select * from t_dul_drop;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> select count(*) from t_dul_drop;
COUNT(*)
----------
1785
SQL> drop table chf.t_dul_drop purge;
Table dropped.
SQL> alter system checkpoint;
System altered.
2.使用logminer找到data_object_id
delete from "SYS"."OBJ$" where "OBJ#" = ‘68474‘ and "DATAOBJ#" = ‘68474‘ and "OWNER#" = ‘61‘ and "NAME" = ‘T_DUL_DROP‘ and "NAMESPACE" = ‘1‘ and "SUBNAME" IS NULL and "TYPE#" = ‘2‘ and "CTIME" = TO_DATE(‘04-FEB-13‘, ‘DD-MON-RR‘) and "MTIME" = TO_DATE(‘04-FEB-13‘, ‘DD-MON-RR‘) and "STIME" = TO_DATE(‘04-FEB-13‘, ‘DD-MON-RR‘) and "STATUS" = ‘1‘ and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = ‘0‘ and "OID$" IS NULL and "SPARE1" = ‘6‘ and "SPARE2" = ‘1‘ and "SPARE3" = ‘61‘ and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = ‘AAAAASAABAAAPzCAAV‘;
这里可以知道，被删除表的data_object_id为68474
3.DUL恢复被删除表
--dul版本
E:\dul10>dul.exe
Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013
with 64-bit io functions
Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal use Only
DUL> ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
Parameter altered
--扫描所属表空间
DUL> scan tablespace 6;
Scanning tablespace 6, data file 6 ...
13 segment header and 331 data blocks
tablespace 6, data file 6: 1279 blocks scanned
Reading EXT.dat 13 entries loaded and sorted 13 entries
Reading SEG.dat 13 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries
--scan tables得到需求表(可以核对数据样例)
DUL> scan tables;
UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)
, COL004 VARCHAR2(20), COL005 VARCHAR2(30), COL006 VARCHAR2(5), COL007 NUMBER
, COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER
, COL012 NUMBER, COL013 NUMBER, COL014 NUMBER, COL015 CHAR
, COL016 NUMBER, COL017 NUMBER, COL018 VARCHAR2(3), COL019 VARCHAR2(1)
, COL020 NUMBER, COL021 NUMBER, COL022 NUMBER, COL023 NUMBER
, COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER
, COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)
, COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)
, COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)
, COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)
, COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 VARCHAR2(8), COL047 VARCHAR2(3)
, COL048 VARCHAR2(3), COL049 VARCHAR2(8), COL050 VARCHAR2(8), COL051 VARCHAR2(5)
, COL052 VARCHAR2(2), COL053 VARCHAR2(2), COL054 VARCHAR2(3), COL055 VARCHAR2(7) )
STORAGE( DATAOBJNO 68474 );
--恢复删除表(业务提供表结构)
DUL> unload table t_dul_drop(
2 OWNER VARCHAR2(30),
3 TABLE_NAME VARCHAR2(30),
4 TABLESPACE_NAME VARCHAR2(30),
5 CLUSTER_NAME VARCHAR2(30),
6 IOT_NAME VARCHAR2(30),
7 STATUS VARCHAR2(8) ,
8 PCT_FREE NUMBER ,
9 PCT_USED NUMBER ,
10 INI_TRANS NUMBER ,
11 MAX_TRANS NUMBER ,
12 INITIAL_EXTENT NUMBER ,
13 NEXT_EXTENT NUMBER ,
14 MIN_EXTENTS NUMBER ,
15 MAX_EXTENTS NUMBER ,
16 PCT_INCREASE NUMBER ,
17 FREELISTS NUMBER ,
18 FREELIST_GROUPS NUMBER ,
19 LOGGING VARCHAR2(3) ,
20 BACKED_UP VARCHAR2(1) ,
21 NUM_ROWS NUMBER ,
22 BLOCKS NUMBER ,
23 EMPTY_BLOCKS NUMBER ,
24 AVG_SPACE NUMBER ,
25 CHAIN_CNT NUMBER ,
26 AVG_ROW_LEN NUMBER ,
27 AVG_SPACE_FREELIST_BLOCKS NUMBER ,
28 NUM_FREELIST_BLOCKS NUMBER ,
29 DEGREE VARCHAR2(20),
30 INSTANCES VARCHAR2(20),
31 CACHE VARCHAR2(10),
32 TABLE_LOCK VARCHAR2(8) ,
33 SAMPLE_SIZE NUMBER ,
34 LAST_ANALYZED DATE ,
35 PARTITIONED VARCHAR2(3) ,
36 IOT_TYPE VARCHAR2(12),
37 TEMPORARY VARCHAR2(1) ,
38 SECONDARY VARCHAR2(1) ,
39 NESTED VARCHAR2(3) ,
40 BUFFER_POOL VARCHAR2(7) ,
41 FLASH_CACHE VARCHAR2(7) ,
42 CELL_FLASH_CACHE VARCHAR2(7) ,
43 ROW_MOVEMENT VARCHAR2(8) ,
44 GLOBAL_STATS VARCHAR2(3) ,
45 USER_STATS VARCHAR2(3) ,
46 DURATION VARCHAR2(15),
47 SKIP_CORRUPT VARCHAR2(8) ,
48 MONITORING VARCHAR2(3) ,
49 CLUSTER_OWNER VARCHAR2(30),
50 DEPENDENCIES VARCHAR2(8) ,
51 COMPRESSION VARCHAR2(8) ,
52 COMPRESS_FOR VARCHAR2(12),
53 DROPPED VARCHAR2(3) ,
54 READ_ONLY VARCHAR2(3) ,
55 SEGMENT_CREATED VARCHAR2(3) ,
56 RESULT_CACHE VARCHAR2(7))
57 STORAGE( DATAOBJNO 68474 );
. unloading table T_DUL_DROP
DUL: Warning: Recreating file "T_DUL_DROP.ctl"
1785 rows unloaded
4.模拟业务规则提供，创建表
SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0; Table created.
5.导入Oracle被删除的数据
e:\dul10>sqlldr chf/xifenfei control=T_DUL_DROP.ctl SQL*Loader: Release 11.2.0.3.0 - Production on Mon Feb 4 23:35:57 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 64 Commit point reached - logical record count 128 Commit point reached - logical record count 192 Commit point reached - logical record count 256 Commit point reached - logical record count 320 Commit point reached - logical record count 384 Commit point reached - logical record count 448 Commit point reached - logical record count 512 Commit point reached - logical record count 576 Commit point reached - logical record count 640 Commit point reached - logical record count 704 Commit point reached - logical record count 768 Commit point reached - logical record count 832 Commit point reached - logical record count 896 Commit point reached - logical record count 960 Commit point reached - logical record count 1024 Commit point reached - logical record count 1088 Commit point reached - logical record count 1152 Commit point reached - logical record count 1216 Commit point reached - logical record count 1280 Commit point reached - logical record count 1344 Commit point reached - logical record count 1408 Commit point reached - logical record count 1472 Commit point reached - logical record count 1536 Commit point reached - logical record count 1600 Commit point reached - logical record count 1664 Commit point reached - logical record count 1728 Commit point reached - logical record count 1785
6.验证恢复数据结果
SQL> select count(*) from t_dul_drop;
COUNT(*)
----------
1785
SQL> select owner,table_name from t_dul_drop where rownum<10;
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS IDL_CHAR$
SYS IDL_UB2$
SYS IDL_SB4$
SYS ERROR$
SYS SETTINGS$
SYS NCOMP_DLL$
SYS PROCEDUREJAVA$
SYS PROCEDUREC$
SYS PROCEDUREPLSQL$
9 rows selected.
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:Oracle dul数据挖掘 没有备份情况下非常规恢复drop删除的数据表
原文唯一网址：http://www.oracleplus.net/arch/oracle-20160522-216.html
Oracle研究中心
关键词：
Oracle dul数据挖掘
没有备份情况下非常规恢复drop删除的数据表
案例:Oracle dul数据挖掘 没有备份情况下非常规恢复drop删除的数据表
标签：
本文系统来源：http://www.cnblogs.com/piguanjia/p/5608270.html
内容总结
以上是互联网集市为您收集整理的案例:Oracle dul数据挖掘 没有备份情况下非常规恢复drop删除的数据表全部内容，希望文章能够帮你解决案例:Oracle dul数据挖掘 没有备份情况下非常规恢复drop删除的数据表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】