联系：手机/微信(+86 17813235971) QQ(107644445)
标题：dul恢复drop表测试
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
dul对被drop对象进行恢复，需要提供两个信息
1.被删除表所属表空间(非必须)
2.被删除表结构(必须)
模拟删除表
--创建测试表
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.
使用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
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
模拟业务规则提供，创建表
SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0; Table created.
导入数据
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
恢复数据结果
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.
1.同dul恢复truncate表测试,dul最新版本不能用来挖缺少system的数据(drop，truncate，丢失system等)，主要体现在常规字符串(英文,汉字等)被转换为了16进制字符.初步判断是dul本身bug，等待下一版本修复
2.drop table 恢复使用scan tables，truncate table 恢复使用scan extents比较方便
create table t_dul_drop as select * from t_dul_drop_bak where 1=0;
请问这里where子句后的1 是字段吗？
创建一个t_dul_drop表，结构和t_dul_drop_bak一致，无数据
为什么能恢复啊？什么机制啊？
表只要没有被覆盖，实际上还存在文件中，通过工具扫描文件，找到对应的记录
就和win清理回收站，只要没有覆盖，依旧可以通过工具恢复原理一样