如何抽取损坏的dmp文件中的数据

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

在某些情况下，如果没有物理备份，只要逻辑的导出备份，需要进行恢复时，发现dmp文件又损坏了，通常来讲是比较悲剧的。针对这样的极端情况，Oracle DUL是可以经常支持的（目前ODU尚未支持）。
如下是我的一些简单的测试，供参考！
##### 测试DUL是否支持抽取exp的dmp

[oracle@10gasm dul]$ ./dul
Data UnLoader: 10.2.0.5.32 - Internal Only - on Sun Aug 17 04:45:52 2014
with 64-bit io functions
Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
Reading USER.dat 59 entries loaded
Reading OBJ.dat 50948 entries loaded and sorted 50948 entries
Reading TAB.dat 1586 entries loaded
Reading COL.dat 55063 entries loaded and sorted 55063 entries
Reading TABPART.dat 85 entries loaded and sorted 85 entries
Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries
Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries
Reading INDPART.dat 93 entries loaded and sorted 93 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2236 entries loaded
Reading LOB.dat 529 entries loaded
Reading ICOL.dat 3658 entries loaded
Reading COLTYPE.dat 1688 entries loaded
Reading TYPE.dat 1885 entries loaded
Reading ATTRIBUTE.dat 7051 entries loaded
Reading COLLECTION.dat 551 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 10 entries loaded
Reading PROPS.dat 27 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
Disk group DATA1, dul group_cid 0
Discovered disk /dev/sdd as diskgroup DATA1, disk number 0 size 1024 Mb File1 starts at 2, dul_disk_cid 0
Discovered disk /dev/sdc as diskgroup DATA1, disk number 1 size 1024 Mb without File1 meta data, dul_disk_cid 1
DUL: Warning: Dictionary cache DC_ASM_EXTENTS is empty
DUL: Error: Filedir block not allocated, file does not exist
DUL: Error: Could not load asm meta data for group DATA1 file 9
Oracle data file size 503324672 bytes, block size 8192
Found db_id = 2100824985
Found db_name = TEST
Oracle data file size 57679872 bytes, block size 8192
Oracle data file size 272637952 bytes, block size 8192
Oracle data file size 5251072 bytes, block size 8192
Oracle data file size 209723392 bytes, block size 8192
Oracle data file size 10493952 bytes, block size 8192
Oracle data file size 104865792 bytes, block size 8192
DUL> scan dump file /home/oracle/roger.dmp ;
0: CSET: 1 (US7ASCII)
3: SEAL EXPORT:V10.02.01
20: DBA ROGER
28: TYPE USERS
9103: CREATE CLUSTER "TEST_CLUSTER" ("OBJECT_ID" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER"
9294: CREATE INDEX "TEST_CLUSTER_IDX_ID" ON CLUSTER "TEST_CLUSTER" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER"
9535: TABLE "AAA"
9547: CREATE TABLE "AAA" ("OWNER" VARCHAR2(30), "OBJECT_ID" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
9767: INSERT INTO "AAA" ("OWNER", "OBJECT_ID") VALUES (:1, :2)
9824: BIND information for 2 columns
col[  1] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[  2] type 2 max length 22
Conventional export
9842: start of table data
9944: CREATE INDEX "AAA_ID_IDX" ON "AAA" ("OBJECT_ID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING
10130: ALTER INDEX "AAA_ID_IDX" UNUSABLE
11159: TABLE "T"
11169: CREATE TABLE "T" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
11661: INSERT INTO "T" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
11920: BIND information for 13 columns
col[  1] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[  2] type 1 max length 128 cset 852 (ZHS16GBK) form 1
col[  3] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[  4] type 2 max length 22
col[  5] type 2 max length 22
col[  6] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[  7] type 12 max length 7
col[  8] type 12 max length 7
col[  9] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1
col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1
Conventional export
12014: start of table data
12811: ALTER TABLE "T" ADD SUPPLEMENTAL LOG GROUP "GGS_51357" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") ALWAYS
18880: TABLE "T0521"
18894: CREATE TABLE "T0521" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
19390: INSERT INTO "T0521" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
19653: BIND information for 13 columns
col[  1] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[  2] type 1 max length 128 cset 852 (ZHS16GBK) form 1
col[  3] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[  4] type 2 max length 22
col[  5] type 2 max length 22
col[  6] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[  7] type 12 max length 7
col[  8] type 12 max length 7
col[  9] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1
col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1
Conventional export
19747: start of table data
20544: TABLE "T0727"
20558: CREATE TABLE "T0727" ("A" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
20750: INSERT INTO "T0727" ("A") VALUES (:1)
20788: BIND information for 1 columns
col[  1] type 2 max length 22
.......
Conventional export
93063204: start of table data
93063207: CREATE INDEX "IDX1_YZ_CLGC_ZMMD_BAK" ON "YZ_CLGC_ZMMD_BAK" ("MAIL_NO" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING LOCAL(PARTITION "PART_131029"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131030"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131031"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131101"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131102"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131103"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131104"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131105"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131106"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131107"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131108"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131109"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_MAX"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING ) 
93293775: ENDPARTITION
93293788: ENDTABLE
93293814: CREATE SYNONYM "V$PARAMETER" FOR "SYS"."SHOW_HIDDEN_V$PARAMETER"
93294070: TABLE "TEST_KILLDB2"
93294091: ALTER TABLE "TEST_KILLDB2" ADD FOREIGN KEY ("OBJECT_ID") REFERENCES "TEST_KILLDB1" ("OBJECT_ID") ENABLE
93294195: ENDTABLE
93294681: TABLE "TEST_CLUSTER"
93294761: TABLE "AAA"
93294837: TABLE "T"
93294907: TABLE "TEST_ASM_READ"
93295013: TABLE "TEST_KILLDB1"
93295116: TABLE "TEST_KILLDB2"
93295219: TABLE "TTT"
93295295: TABLE "YZ_CLGC_ZMMD_BAK"
93295410: ENDTABLE
93295442: EXIT
93295447: EXIT
DUL>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

[oracle@10gasm dul]$ ./dul

Data UnLoader: 10.2.0.5.32 - Internal Only - on Sun Aug 17 04:45:52 2014

with 64-bit io functions

Strictly Oracle Internal Use Only

DUL: Warning: Recreating file "dul.log"

Reading USER.dat 59 entries loaded

Reading OBJ.dat 50948 entries loaded and sorted 50948 entries

Reading TAB.dat 1586 entries loaded

Reading COL.dat 55063 entries loaded and sorted 55063 entries

Reading TABPART.dat 85 entries loaded and sorted 85 entries

Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries

Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries

Reading INDPART.dat 93 entries loaded and sorted 93 entries

Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries

Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries

Reading IND.dat 2236 entries loaded

Reading LOB.dat 529 entries loaded

Reading ICOL.dat 3658 entries loaded

Reading COLTYPE.dat 1688 entries loaded

Reading TYPE.dat 1885 entries loaded

Reading ATTRIBUTE.dat 7051 entries loaded

Reading COLLECTION.dat 551 entries loaded

Reading BOOTSTRAP.dat 57 entries loaded

Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries

Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries

Reading UNDO.dat 21 entries loaded

Reading TS.dat 10 entries loaded

Reading PROPS.dat 27 entries loaded

Database character set is ZHS16GBK

Database national character set is AL16UTF16

Disk group DATA1, dul group_cid 0

Discovered disk /dev/sdd as diskgroup DATA1, disk number 0 size 1024 Mb File1 starts at 2, dul_disk_cid 0

Discovered disk /dev/sdc as diskgroup DATA1, disk number 1 size 1024 Mb without File1 meta data, dul_disk_cid 1

DUL: Warning: Dictionary cache DC_ASM_EXTENTS is empty

DUL: Error: Filedir block not allocated, file does not exist

DUL: Error: Could not load asm meta data for group DATA1 file 9

Oracle data file size 503324672 bytes, block size 8192

Found db_id = 2100824985

Found db_name = TEST

Oracle data file size 57679872 bytes, block size 8192

Oracle data file size 272637952 bytes, block size 8192

Oracle data file size 5251072 bytes, block size 8192

Oracle data file size 209723392 bytes, block size 8192

Oracle data file size 10493952 bytes, block size 8192

Oracle data file size 104865792 bytes, block size 8192

DUL> scan dump file /home/oracle/roger.dmp ;

0: CSET: 1 (US7ASCII)

3: SEAL EXPORT:V10.02.01

20: DBA ROGER

28: TYPE USERS

9103: CREATE CLUSTER "TEST_CLUSTER" ("OBJECT_ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER"

9294: CREATE INDEX "TEST_CLUSTER_IDX_ID" ON CLUSTER "TEST_CLUSTER" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER"

9535: TABLE "AAA"

9547: CREATE TABLE "AAA" ("OWNER" VARCHAR2(30), "OBJECT_ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS

9767: INSERT INTO "AAA" ("OWNER", "OBJECT_ID") VALUES (:1, :2)

9824: BIND information for 2 columns

col[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1

col[ 2] type 2 max length 22

Conventional export

9842: start of table data

9944: CREATE INDEX "AAA_ID_IDX" ON "AAA" ("OBJECT_ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING

10130: ALTER INDEX "AAA_ID_IDX" UNUSABLE

11159: TABLE "T"

11169: CREATE TABLE "T" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS

11661: INSERT INTO "T" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)

11920: BIND information for 13 columns

col[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1

col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1

col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1

col[ 4] type 2 max length 22

col[ 5] type 2 max length 22

col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1

col[ 7] type 12 max length 7

col[ 8] type 12 max length 7

col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1

col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1

col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1

col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1

col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1

Conventional export

12014: start of table data

12811: ALTER TABLE "T" ADD SUPPLEMENTAL LOG GROUP "GGS_51357" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") ALWAYS

18880: TABLE "T0521"

18894: CREATE TABLE "T0521" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS

19390: INSERT INTO "T0521" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)

19653: BIND information for 13 columns

col[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1

col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1

col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1

col[ 4] type 2 max length 22

col[ 5] type 2 max length 22

col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1

col[ 7] type 12 max length 7

col[ 8] type 12 max length 7

col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1

col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1

col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1

col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1

col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1

Conventional export

19747: start of table data

20544: TABLE "T0727"

20558: CREATE TABLE "T0727" ("A" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS

20750: INSERT INTO "T0727" ("A") VALUES (:1)

20788: BIND information for 1 columns

col[ 1] type 2 max length 22

.......

Conventional export

93063204: start of table data

93063207: CREATE INDEX "IDX1_YZ_CLGC_ZMMD_BAK" ON "YZ_CLGC_ZMMD_BAK" ("MAIL_NO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING LOCAL(PARTITION "PART_131029" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131030" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131031" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131101" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131102" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131103" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131104" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131105" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131106" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131107" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131108" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_131109" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING, PARTITION "PART_MAX" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" NOLOGGING )

93293775: ENDPARTITION

93293788: ENDTABLE

93293814: CREATE SYNONYM "V$PARAMETER" FOR "SYS"."SHOW_HIDDEN_V$PARAMETER"

93294070: TABLE "TEST_KILLDB2"

93294091: ALTER TABLE "TEST_KILLDB2" ADD FOREIGN KEY ("OBJECT_ID") REFERENCES "TEST_KILLDB1" ("OBJECT_ID") ENABLE

93294195: ENDTABLE

93294681: TABLE "TEST_CLUSTER"

93294761: TABLE "AAA"

93294837: TABLE "T"

93294907: TABLE "TEST_ASM_READ"

93295013: TABLE "TEST_KILLDB1"

93295116: TABLE "TEST_KILLDB2"

93295219: TABLE "TTT"

93295295: TABLE "YZ_CLGC_ZMMD_BAK"

93295410: ENDTABLE

93295442: EXIT

93295447: EXIT

DUL>

当使用scan dump file扫描完exp的dmp文件之后，最后是讲scan的数据输出到文件，方便查看，然后可以直接抽取我们需要的表，如下：

DUL> unexp TABLE "ROGER"."T" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), 
2  "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE,
3   "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1))
4  dump file /home/oracle/roger.dmp from 12014;
Unloaded 9 rows, end of table marker at 12810
DUL>

DUL> unexp TABLE "ROGER"."T" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30),

2 "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE,

3 "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1))

4 dump file /home/oracle/roger.dmp from 12014;

Unloaded 9 rows, end of table marker at 12810

DUL>

使用unexp命令抽取之后，最后需会产生2个文件，其中是一个sqlldr的ctl文件，最后我们通过sqlldr讲数据加载到数据库即可。如下：

[oracle@10gasm dul]$ sqlplus roger/roger
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 17 04:57:45 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(1) from t;
COUNT(1)
----------
9
SQL> truncate table t;
Table truncated.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@10gasm dul]$ sqlldr roger/roger control=dump000.ctl 
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Aug 17 04:58:26 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Commit point reached - logical record count 9
[oracle@10gasm dul]$ sqlplus roger/roger
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 17 04:58:31 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(1) from t;
COUNT(1)
----------
9
SQL>

[oracle@10gasm dul]$ sqlplus roger/roger

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 17 04:57:45 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select count(1) from t;

COUNT(1)

----------

SQL> truncate table t;

Table truncated.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@10gasm dul]$ sqlldr roger/roger control=dump000.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Aug 17 04:58:26 2014

Commit point reached - logical record count 9

[oracle@10gasm dul]$ sqlplus roger/roger

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 17 04:58:31 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select count(1) from t;

COUNT(1)

----------

SQL>

当测试完DUL的exp dmp支持之后，我比较好奇是否会支持expdp呢？如下是测试过程！

###### 测试DUL是否支持expdp

[oracle@10gasm ~]$ expdp roger/roger directory=ASM_TO_FS dumpfile=roger02.dmp 
Export: Release 10.2.0.1.0 - Production on Sunday, 17 August, 2014 5:00:16
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "ROGER"."SYS_EXPORT_SCHEMA_01":  roger/******** directory=ASM_TO_FS dumpfile=roger02.dmp 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120.5 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/CLUSTER/CLUSTER
Processing object type SCHEMA_EXPORT/CLUSTER/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ROGER"."TTT"                               74.22 MB  796592 rows
. . exported "ROGER"."TEST_BIG"                          4.655 MB   49885 rows
. . exported "ROGER"."TEST_ASM_READ"                     874.7 KB   10000 rows
. . exported "ROGER"."TEST_KILLDB1"                      5.460 KB      13 rows
. . exported "ROGER"."TEST_KILLDB2"                      5.937 KB       4 rows
. . exported "ROGER"."AAA"                               5.320 KB       9 rows
. . exported "ROGER"."T"                                 9.335 KB       9 rows
. . exported "ROGER"."T0521"                             9.335 KB       9 rows
. . exported "ROGER"."T0727"                             4.929 KB       3 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131029"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131030"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131031"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131101"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131102"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131103"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131104"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131105"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131106"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131107"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131108"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131109"        0 KB       0 rows
. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_MAX"           0 KB       0 rows
Master table "ROGER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ROGER.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/arch/roger02.dmp
Job "ROGER"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:00:55

[oracle@10gasm ~]$ expdp roger/roger directory=ASM_TO_FS dumpfile=roger02.dmp

Export: Release 10.2.0.1.0 - Production on Sunday, 17 August, 2014 5:00:16

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

FLASHBACK automatically enabled to preserve database integrity.

Starting "ROGER"."SYS_EXPORT_SCHEMA_01": roger/******** directory=ASM_TO_FS dumpfile=roger02.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 120.5 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/CLUSTER/CLUSTER

Processing object type SCHEMA_EXPORT/CLUSTER/INDEX

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "ROGER"."TTT" 74.22 MB 796592 rows

. . exported "ROGER"."TEST_BIG" 4.655 MB 49885 rows

. . exported "ROGER"."TEST_ASM_READ" 874.7 KB 10000 rows

. . exported "ROGER"."TEST_KILLDB1" 5.460 KB 13 rows

. . exported "ROGER"."TEST_KILLDB2" 5.937 KB 4 rows

. . exported "ROGER"."AAA" 5.320 KB 9 rows

. . exported "ROGER"."T" 9.335 KB 9 rows

. . exported "ROGER"."T0521" 9.335 KB 9 rows

. . exported "ROGER"."T0727" 4.929 KB 3 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131029" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131030" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131031" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131101" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131102" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131103" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131104" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131105" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131106" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131107" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131108" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_131109" 0 KB 0 rows

. . exported "ROGER"."YZ_CLGC_ZMMD_BAK":"PART_MAX" 0 KB 0 rows

Master table "ROGER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for ROGER.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/arch/roger02.dmp

Job "ROGER"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:00:55

下面我们来测试，Oracle DUL是否支持expdp的dmp文件的抽取。
++++使用unpump命令查看dmp文件头信息

DUL> unpump header dump file /home/oracle/arch/roger02.dmp ;
Version is 257
check sum is 2743005495
data pump id is 6783164
master_obj_no is 52434
header blocks is 1
data pump file number is 1
block size is 4096
character set id is 852
master table block offset is 20449
(Master table is at byte offset (20449 -1) * 4096 = 83755008)
master table size is 306616
++++扫描expdp的dmp文件
DUL> unpump scan dump file /home/oracle/arch/roger02.dmp ;
Magic number found at offset 86016
Magic number found at offset 77918208
Magic number found at offset 82800640
Magic number found at offset 83697664
Magic number found at offset 83705856
Magic number found at offset 83714048
Magic number found at offset 83722240
Magic number found at offset 83734528
Magic number found at offset 83746816
Magic number found at offset 83755008

DUL> unpump header dump file /home/oracle/arch/roger02.dmp ;

Version is 257

check sum is 2743005495

data pump id is 6783164

master_obj_no is 52434

header blocks is 1

data pump file number is 1

block size is 4096

character set id is 852

master table block offset is 20449

(Master table is at byte offset (20449 -1) * 4096 = 83755008)

master table size is 306616

++++扫描expdp的dmp文件

DUL> unpump scan dump file /home/oracle/arch/roger02.dmp ;

Magic number found at offset 86016

Magic number found at offset 77918208

Magic number found at offset 82800640

Magic number found at offset 83697664

Magic number found at offset 83705856

Magic number found at offset 83714048

Magic number found at offset 83722240

Magic number found at offset 83734528

Magic number found at offset 83746816

Magic number found at offset 83755008

这里需要注意的是，scan的结果有10条，表示这个expdp的dmp文件中包含了10个表的内容，如果我只想抽取之前的一个表怎么办呢？可以结合expdp的日志来进行判断，或者利用strings来确认。

这里假设我想抽取第一个表的数据：

DUL> unpump stream header dump file /home/oracle/arch/roger02.dmp from 86016 ;
magic number is 0xffff2424
version is 10
flags is 0x00
meta data length is 4758
relative meta data offset is 4096
table data length is 77821808
relative table data offset is 8856
granule length is 64
relative granule offset is 77830664
Table data from 94872 until 77916680
Meta data character set is 852, ZHS16GBK
<?xml version="1.0"?><ROWSET><ROW>
<STRMTABLE_T><VERS_MAJOR>1</VERS_MAJOR><VERS_MINOR>0 </VERS_MINOR><VERS_DPAPI>3</VERS_DPAPI><ENDIANNESS>2</ENDIANNESS><CHARSET>ZHS16GBK</CHARSET><NCHARSET>AL16UTF16</NCHARSET><DBTIMEZONE>+00:00</DBTIMEZONE><FDO>0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823432323081123081141B0230083035407D00300000000000000000000000000000000000000000000000000000000000000000000000000</FDO><OBJ_NUM>51588</OBJ_NUM><OWNER_NAME>ROGER</OWNER_NAME><NAME>TTT</NAME><PROPERTY>536870912</PROPERTY><COL_LIST><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>1</COL_NUM><INTCOL_NUM>1</INTCOL_NUM><SEGCOL_NUM>1</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OWNER</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>2</COL_NUM><INTCOL_NUM>2</INTCOL_NUM><SEGCOL_NUM>2</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OBJECT_NAME</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>128</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>128</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>3</COL_NUM><INTCOL_NUM>3</INTCOL_NUM><SEGCOL_NUM>3</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>SUBOBJECT_NAME</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>4</COL_NUM><INTCOL_NUM>4</INTCOL_NUM><SEGCOL_NUM>4</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OBJECT_ID</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>5</COL_NUM><INTCOL_NUM>5</INTCOL_NUM><SEGCOL_NUM>5</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>DATA_OBJECT_ID</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>6</COL_NUM><INTCOL_NUM>6</INTCOL_NUM><SEGCOL_NUM>6</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OBJECT_TYPE</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>19</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>19</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>7</COL_NUM><INTCOL_NUM>7</INTCOL_NUM><SEGCOL_NUM>7</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>CREATED</NAME><TYPE_NUM>12</TYPE_NUM><LENGTH>7</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>8</COL_NUM><INTCOL_NUM>8</INTCOL_NUM><SEGCOL_NUM>8</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>LAST_DDL_TIME</NAME><TYPE_NUM>12</TYPE_NUM><LENGTH>7</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>9</COL_NUM><INTCOL_NUM>9</INTCOL_NUM><SEGCOL_NUM>9</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>TIMESTAMP</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>19</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>19</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>10</COL_NUM><INTCOL_NUM>10</INTCOL_NUM><SEGCOL_NUM>10</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>STATUS</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>7</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>7</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>11</COL_NUM><INTCOL_NUM>11</INTCOL_NUM><SEGCOL_NUM>11</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>TEMPORARY</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>1</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>1</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>12</COL_NUM><INTCOL_NUM>12</INTCOL_NUM><SEGCOL_NUM>12</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>GENERATED</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>1</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>1</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>13</COL_NUM><INTCOL_NUM>13</INTCOL_NUM><SEGCOL_NUM>13</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>SECONDARY</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>1</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>1</CHARLENGTH></COL_LIST_ITEM></COL_LIST></STRMTABLE_T>
</ROW></ROWSET>
DUL>

DUL> unpump stream header dump file /home/oracle/arch/roger02.dmp from 86016 ;

magic number is 0xffff2424

version is 10

flags is 0x00

meta data length is 4758

relative meta data offset is 4096

table data length is 77821808

relative table data offset is 8856

granule length is 64

relative granule offset is 77830664

Table data from 94872 until 77916680

Meta data character set is 852, ZHS16GBK

<?xml version="1.0"?><ROWSET><ROW>

<STRMTABLE_T><VERS_MAJOR>1</VERS_MAJOR><VERS_MINOR>0 </VERS_MINOR><VERS_DPAPI>3</VERS_DPAPI><ENDIANNESS>2</ENDIANNESS><CHARSET>ZHS16GBK</CHARSET><NCHARSET>AL16UTF16</NCHARSET><DBTIMEZONE>+00:00</DBTIMEZONE><FDO>0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823432323081123081141B0230083035407D00300000000000000000000000000000000000000000000000000000000000000000000000000</FDO><OBJ_NUM>51588</OBJ_NUM><OWNER_NAME>ROGER</OWNER_NAME><NAME>TTT</NAME><PROPERTY>536870912</PROPERTY><COL_LIST><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>1</COL_NUM><INTCOL_NUM>1</INTCOL_NUM><SEGCOL_NUM>1</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OWNER</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>2</COL_NUM><INTCOL_NUM>2</INTCOL_NUM><SEGCOL_NUM>2</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OBJECT_NAME</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>128</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>128</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>3</COL_NUM><INTCOL_NUM>3</INTCOL_NUM><SEGCOL_NUM>3</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>SUBOBJECT_NAME</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>30</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>30</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>4</COL_NUM><INTCOL_NUM>4</INTCOL_NUM><SEGCOL_NUM>4</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OBJECT_ID</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>5</COL_NUM><INTCOL_NUM>5</INTCOL_NUM><SEGCOL_NUM>5</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>DATA_OBJECT_ID</NAME><TYPE_NUM>2</TYPE_NUM><LENGTH>22</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>6</COL_NUM><INTCOL_NUM>6</INTCOL_NUM><SEGCOL_NUM>6</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>OBJECT_TYPE</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>19</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>19</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>7</COL_NUM><INTCOL_NUM>7</INTCOL_NUM><SEGCOL_NUM>7</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>CREATED</NAME><TYPE_NUM>12</TYPE_NUM><LENGTH>7</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>8</COL_NUM><INTCOL_NUM>8</INTCOL_NUM><SEGCOL_NUM>8</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>LAST_DDL_TIME</NAME><TYPE_NUM>12</TYPE_NUM><LENGTH>7</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>0</CHARSETID><CHARSETFORM>0</CHARSETFORM><CHARLENGTH>0</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>9</COL_NUM><INTCOL_NUM>9</INTCOL_NUM><SEGCOL_NUM>9</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>TIMESTAMP</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>19</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>19</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>10</COL_NUM><INTCOL_NUM>10</INTCOL_NUM><SEGCOL_NUM>10</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>STATUS</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>7</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>7</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>11</COL_NUM><INTCOL_NUM>11</INTCOL_NUM><SEGCOL_NUM>11</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>TEMPORARY</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>1</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>1</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>12</COL_NUM><INTCOL_NUM>12</INTCOL_NUM><SEGCOL_NUM>12</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>GENERATED</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>1</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>1</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>51588</OBJ_NUM><COL_NUM>13</COL_NUM><INTCOL_NUM>13</INTCOL_NUM><SEGCOL_NUM>13</SEGCOL_NUM><PROPERTY>14336</PROPERTY><NAME>SECONDARY</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>1</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>1</CHARLENGTH></COL_LIST_ITEM></COL_LIST></STRMTABLE_T>

</ROW></ROWSET>

DUL>

上面的结果中，最为关键的一行是：Table data from 94872 until 77916680
根据这行结果，我们知道该表的offset为94872到 77916680，因此下面根据偏移量来抽取该表的数据：

DUL> unpump TABLE  ROGER.TTT
2  (OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(128), SUBOBJECT_NAME VARCHAR2(30), OBJECT_ID NUMBER, DATA_OBJECT_ID NUMBER, 
3  OBJECT_TYPE VARCHAR2(19), CREATED DATE, LAST_DDL_TIME DATE, TIMESTAMP VARCHAR2(19), STATUS VARCHAR2(7), TEMPORARY VARCHAR2(1), GENERATED VARCHAR2(1), 
4  SECONDARY VARCHAR2(1)) dump file /home/oracle/arch/roger02.dmp from 94872 until 77916680;
DUL: Warning: Recreating file "dump000.ctl"
796592 rows unloaded
DUL> exit
Life is DUL without it
[oracle@10gasm dul]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 17 05:57:46 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn roger/roger
Connected.
SQL> truncate table ttt;
Table truncated.
SQL> !
[oracle@10gasm dul]$ ls -ltr dump*
-rw-r--r-- 1 oracle oinstall 121674640 Aug 17 05:57 dump000.dat
-rw-r--r-- 1 oracle oinstall      1067 Aug 17 05:57 dump000.ctl
[oracle@10gasm dul]$ sqlldr roger/roger control=dump000.ctl 
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Aug 17 05:58:18 2014
Copyright (c) 1982, 2005, Oracle.  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 796470
Commit point reached - logical record count 796534
Commit point reached - logical record count 796592
[oracle@10gasm dul]$ 
[oracle@10gasm dul]$ exit
exit
SQL> select count(1) from ttt;
COUNT(1)
----------
796592
SQL>

DUL> unpump TABLE ROGER.TTT

2 (OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(128), SUBOBJECT_NAME VARCHAR2(30), OBJECT_ID NUMBER, DATA_OBJECT_ID NUMBER,

3 OBJECT_TYPE VARCHAR2(19), CREATED DATE, LAST_DDL_TIME DATE, TIMESTAMP VARCHAR2(19), STATUS VARCHAR2(7), TEMPORARY VARCHAR2(1), GENERATED VARCHAR2(1),

4 SECONDARY VARCHAR2(1)) dump file /home/oracle/arch/roger02.dmp from 94872 until 77916680;

DUL: Warning: Recreating file "dump000.ctl"

796592 rows unloaded

DUL> exit

Life is DUL without it

[oracle@10gasm dul]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 17 05:57:46 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> conn roger/roger

Connected.

SQL> truncate table ttt;

Table truncated.

SQL> !

[oracle@10gasm dul]$ ls -ltr dump*

-rw-r--r-- 1 oracle oinstall 121674640 Aug 17 05:57 dump000.dat

-rw-r--r-- 1 oracle oinstall 1067 Aug 17 05:57 dump000.ctl

[oracle@10gasm dul]$ sqlldr roger/roger control=dump000.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Aug 17 05:58:18 2014

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 796470

Commit point reached - logical record count 796534

Commit point reached - logical record count 796592

[oracle@10gasm dul]$

[oracle@10gasm dul]$ exit

exit

SQL> select count(1) from ttt;

COUNT(1)

----------

796592

SQL>

我们可以看到，目前DUL 算是比较完美的支持了exp和expdp 的dmp。（我测试过程中遇到了一些其他的状况，这里不描述了）。

Tags: ASM, dmp corruption, exp, expdp, oracle full text index
Posted in backup & rcovery on 8月 17, 2014

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

如何抽取损坏的dmp文件中的数据

Leave a Reply