本案例来自某客户的expdp dmp从服务器上下载下来存档之后，删除了服务器上的dmp，后来需要导入的时候发现导入报错。
[oracle@dbtest ~]$ impdp \'/ as sysdba\' dumpfile=SHJZ_20211108.DMP directory=dir1
Import: Release 11.2.0.4.0 - Production on Wed Feb 2 00:25:59 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/root/SHJZ_20211108.DMP"看名字应该是每天都会对用户进行expdp导出。对于这类损坏的dmp文件应该如何恢复呢？oracle内部提供了DUL恢复工具，当然DUL的作用并不仅仅是抽取dmp文件，其他功能本文就不介绍了。
DUL针对exp dmp和expdp dmp分别使用unexp和unpump命令去处理，基本用法如下所示：
UNEXP [TABLE] [ owner . ] table name
( column list ) [ DIRECT ]
DUMP FILE dump file name
FROM begin offset [ UNTIL end offset ]
[ MINIMUM minimal number of columns COLUMNS ] ;
To unload data from a corrupted exp dump file. No special setup
or configuration is required, just the compatible parameter.
The start offset should be where a row actually begins.
UNPUMP
To unload data from a corrupted expdp (datapump) dump file.
This is still work in progress, the basic commands work
but rather complex to use. Contact me if this is needed.由于本案例是expdp dmp，所以只会演示expdp dmp的抽取。
可以通过dump expdp头部去查看dmp基本情况
DUL> unpump header dump file /root/SHJZ_20211108.DMP ;
Version is 769
check sum is 2419485821
data pump id is 6783164
master_obj_no is 174568
header blocks is 1
data pump file number is 1
block size is 4096
character set id is 852
master table block offset is 4074322
(Master table is at byte offset (4074322 -1) * 4096 = 16688418816)
master table size is 6707128从dmp的头部dump可以看到dmp的version以及字符集等等信息。由于该dmp是一个按用户导出的dmp，下面我只以某个表为例开始进行恢复演示。
首先扫描dmp文件，可以看到dmp里有多少个表，以及每个表的magic number offset。需要注意的是导出时产生的master table export开头的全局临时表也在其中。都在dmp的最后面记录。
DUL> unpump scan dump file /root/SHJZ_20211108.DMP ;
Magic number found at offset 1527808
Magic number found at offset 8841867264
Magic number found at offset 11197063168
Magic number found at offset 13662593024
Magic number found at offset 14433591296
Magic number found at offset 15110955008
Magic number found at offset 15561711616
Magic number found at offset 15794450432
Magic number found at offset 15963017216
Magic number found at offset 16080470016
Magic number found at offset 16177405952
Magic number found at offset 16241639424
Magic number found at offset 16276807680
Magic number found at offset 16309780480
Magic number found at offset 16336015360
Magic number found at offset 16360472576
Magic number found at offset 16384823296
Magic number found at offset 16408399872
Magic number found at offset 16428630016
Magic number found at offset 16447819776
Magic number found at offset 16465948672
Magic number found at offset 16482537472
Magic number found at offset 16495595520
Magic number found at offset 16505315328
Magic number found at offset 16513224704
Magic number found at offset 16519475200
Magic number found at offset 16525840384
Magic number found at offset 16532434944
Magic number found at offset 16538030080
Magic number found at offset 16543121408
Magic number found at offset 16548323328
Magic number found at offset 16553639936
Magic number found at offset 16559087616
Magic number found at offset 16564649984
Magic number found at offset 16570327040
Magic number found at offset 16576118784
Magic number found at offset 16582025216
Magic number found at offset 16588046336
Magic number found at offset 16592605184
Magic number found at offset 16597778432
Magic number found at offset 16601837568
Magic number found at offset 16606011392
Magic number found at offset 16610299904
Magic number found at offset 16614703104
Magic number found at offset 16619220992
Magic number found at offset 16623853568
Magic number found at offset 16628600832
Magic number found at offset 16633462784
Magic number found at offset 16638439424
Magic number found at offset 16642838528
Magic number found at offset 16647127040
Magic number found at offset 16650731520
Magic number found at offset 16654446592
Magic number found at offset 16658276352
Magic number found at offset 16662220800
Magic number found at offset 16664862720
Magic number found at offset 16667246592
Magic number found at offset 16669941760
Magic number found at offset 16672182272
Magic number found at offset 16674291712
Magic number found at offset 16676020224
Magic number found at offset 16678350848
Magic number found at offset 16680804352
Magic number found at offset 16683094016
Magic number found at offset 16683638784
Magic number found at offset 16684367872
Magic number found at offset 16684896256
Magic number found at offset 16685228032
Magic number found at offset 16685486080
Magic number found at offset 16685740032
Magic number found at offset 16685993984
Magic number found at offset 16686305280
Magic number found at offset 16686563328
Magic number found at offset 16686776320
Magic number found at offset 16687005696
Magic number found at offset 16687157248
Magic number found at offset 16687255552
Magic number found at offset 16687325184
Magic number found at offset 16687394816
Magic number found at offset 16687456256
Magic number found at offset 16687558656
Magic number found at offset 16687636480
Magic number found at offset 16687710208
Magic number found at offset 16687767552
Magic number found at offset 16687824896
Magic number found at offset 16687882240
Magic number found at offset 16687972352
Magic number found at offset 16687984640
Magic number found at offset 16687996928
Magic number found at offset 16688013312
Magic number found at offset 16688029696
Magic number found at offset 16688037888
Magic number found at offset 16688050176
Magic number found at offset 16688058368
Magic number found at offset 16688070656
Magic number found at offset 16688087040
Magic number found at offset 16688119808
Magic number found at offset 16688132096
Magic number found at offset 16688144384
Magic number found at offset 16688156672
Magic number found at offset 16688168960
Magic number found at offset 16688181248
Magic number found at offset 16688193536
Magic number found at offset 16688205824
Magic number found at offset 16688230400
Magic number found at offset 16688242688
Magic number found at offset 16688250880
Magic number found at offset 16688279552
Magic number found at offset 16688287744
Magic number found at offset 16688295936
Magic number found at offset 16688320512
Magic number found at offset 16688353280
Magic number found at offset 16688361472
Magic number found at offset 16688369664
Magic number found at offset 16688386048
Magic number found at offset 16688402432
Magic number found at offset 16688410624
Magic number found at offset 16688418816使用hexdump或者od -x去看对应的offset可以发现Magic number 基本都是0xffff2424。 Magic number个人认为意义在于能更快更好的定位到dmp中表的位置，比如我们这里要抽取表SP_SURVIVALVALIDATION，可以通过一些脚本去首先定位该表的 Magic number ，比如我这里的方法：
[root@dbtest ~]# strings /root/SHJZ_20211108.DMP |grep OWNER_NAME|sed 's/>/|/g'|sed 's/</|/g'|awk -F'|' '{print $37,$45}'|grep -v SYS_EXPORT
142469 PCHECK
142472 PFAMILY_INFO
142481 PEVALUATION_BATCH
142499 TDOLOG
142299 PLOWMONEY
142290 PLOWMONEY_0804
142461 PATT
142486 PFAMILY_RECORD
142289 PLOWMONEYBAK2019108
142298 PLOWMONEYNEW
142437 MESSAGES
142466 TUSER
142434 PALIMONY_VARY
142293 SP_SURVIVALVALIDATION
142417 PALIMONY
142449 PALIMONY_VARYDETAIL
142415 PMEMBER
142424 PMEMBER_VARY
142429 PCOMPARISON
142448 PALIMONY_DETAIL
142280 PATT2
142291 ST_PEOPLE
142423 PINCOME_VARY
142416 PINCOME
142304 ST_DEATH
142436 PCHECK_INFO
142443 PSURVEY_INFO
142287 P_WX_REPORT
142301 ST_RECORD
142422 PREDUCE_PAY
142440 PEVALUATE
142421 PPROPERTY
142425 PPROPERTY_VARY
142427 PPUB_DETAIL
142435 PREDUCE_VARY
142319 P_WX_POLICY_BULLETIN
142439 PMEMBER_TEMP
142458 TPOINT
142426 PPUB_NOTICE
142418 SMS_INFO
142454 TPOOR
142460 PMEMBER_RECORD
142281 TEMP_XX
142408 TROLETAUTH
142455 PFAMILY_CHANGE
142456 PFAMILY_MERGE
142457 PMEMBER_MERGE
142430 POBJECTION
142412 QH
142404 TAUTH
142410 TDIC
142459 TNOT_GIVE
142282 TPOOR_HQTEMP
142284 TPOOR_HQTEMP_QP
142409 TUSERTROLE
142438 MESSAGES_TEMPLATE
142462 PDB_PAY
142463 PDB_PAY_DETAIL
142300 PLOWMONEY_BAK20200801
142286 P_WX_HOTLINE
142446 SHIFT
142447 SHIFTORDER
142420 SMS_TEMPLET
142294 ST_CYCLE
142302 ST_CYCLE_VALIDATION
142295 ST_PUSH
142303 SYS_CONFIGURE
142465 TAPPROVALSET
142411 TBASE_SEQUENCE
142414 TCOMPUTE_BASE
142405 TDEPT
157891 TEMP_CARDNO
142431 TFORMULA
142452 TFORMULA_NOTE
142450 TGROUP
142428 TLABOR
142406 TMENU
142444 TPER_ANALYSIS
142413 TPER_INCOME
142283 TPOOR_HQTEMP_GP
142453 TPROHIBIT
142407 TROLE
142433 TSECURITY_TYPE
142432 TSTANDARD
142442 TSYNC
142451 TUSERGROUP左边是表在导出库里的object_id，可以看到 SP_SURVIVALVALIDATION 在第14行，那么该表在dmp中的Magic number offset为16309780480
DUL> unpump stream header dump file /root/SHJZ_20211108.DMP from 16309780480;
magic number is 0xffff2424
version is 10
flags is 0x00
meta data length is 6003
relative meta data offset is 4096
table data length is 26221534
relative table data offset is 10104
granule length is 24
relative granule offset is 26231640
Table data from 16309790584 until 16336012118
Meta data character set is 852, ZHS16GBK
<?xml version="1.0"?><ROWSET><ROW>
<STRMTABLE_T><VERS_MAJOR>1</VERS_MAJOR><VERS_MINOR>1 </VERS_MINOR><VERS_DPAPI>3</VERS_DPAPI><ENDIANNESS>2</ENDIANNESS><CHARSET>ZHS16GBK</CHARSET><NCHARSET>AL16UTF16</NCHARSET><DBTIMEZONE>+00:00</DBTIMEZONE><FDO>0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472347081123081141B0470083035407D00300000000000000000000000000000000000000000000000000000000000000000000000000</FDO><OBJ_NUM>142293</OBJ_NUM><OWNER_NAME>SHJZ</OWNER_NAME><NAME>SP_SURVIVALVALIDATION</NAME><PROPERTY>536870912</PROPERTY><COL_LIST><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>1</COL_NUM><INTCOL_NUM>1</INTCOL_NUM><SEGCOL_NUM>1</SEGCOL_NUM><COL_SORTKEY>1</COL_SORTKEY><BASE_INTCOL_NUM>1</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>SID</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>100</LENGTH><NOT_NULL>2</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>100</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>2</COL_NUM><INTCOL_NUM>2</INTCOL_NUM><SEGCOL_NUM>2</SEGCOL_NUM><COL_SORTKEY>2</COL_SORTKEY><BASE_INTCOL_NUM>2</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>PID</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>100</LENGTH><NOT_NULL>1</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>100</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>3</COL_NUM><INTCOL_NUM>3</INTCOL_NUM><SEGCOL_NUM>3</SEGCOL_NUM><COL_SORTKEY>3</COL_SORTKEY><BASE_INTCOL_NUM>3</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>VIDEO</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>500</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>500</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>4</COL_NUM><INTCOL_NUM>4</INTCOL_NUM><SEGCOL_NUM>4</SEGCOL_NUM><COL_SORTKEY>4</COL_SORTKEY><BASE_INTCOL_NUM>4</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>RESULTS</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>100</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>100</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>5</COL_NUM><INTCOL_NUM>5</INTCOL_NUM><SEGCOL_NUM>5</SEGCOL_NUM><COL_SORTKEY>5</COL_SORTKEY><BASE_INTCOL_NUM>5</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>VALIDATION_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>142293</OBJ_NUM><COL_NUM>6</COL_NUM><INTCOL_NUM>6</INTCOL_NUM><SEGCOL_NUM>6</SEGCOL_NUM><COL_SORTKEY>6</COL_SORTKEY><BASE_INTCOL_NUM>6</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>WHY</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>255</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>255</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>7</COL_NUM><INTCOL_NUM>7</INTCOL_NUM><SEGCOL_NUM>7</SEGCOL_NUM><COL_SORTKEY>7</COL_SORTKEY><BASE_INTCOL_NUM>7</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>CREATE_USER</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>100</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>100</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>8</COL_NUM><INTCOL_NUM>8</INTCOL_NUM><SEGCOL_NUM>8</SEGCOL_NUM><COL_SORTKEY>8</COL_SORTKEY><BASE_INTCOL_NUM>8</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>CREATE_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>142293</OBJ_NUM><COL_NUM>9</COL_NUM><INTCOL_NUM>9</INTCOL_NUM><SEGCOL_NUM>9</SEGCOL_NUM><COL_SORTKEY>9</COL_SORTKEY><BASE_INTCOL_NUM>9</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>UPDATE_USER</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>100</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>100</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>10</COL_NUM><INTCOL_NUM>10</INTCOL_NUM><SEGCOL_NUM>10</SEGCOL_NUM><COL_SORTKEY>10</COL_SORTKEY><BASE_INTCOL_NUM>10</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>UPDATE_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>142293</OBJ_NUM><COL_NUM>11</COL_NUM><INTCOL_NUM>11</INTCOL_NUM><SEGCOL_NUM>11</SEGCOL_NUM><COL_SORTKEY>11</COL_SORTKEY><BASE_INTCOL_NUM>11</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>VALIDATION_TYPE</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>10</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>10</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>12</COL_NUM><INTCOL_NUM>12</INTCOL_NUM><SEGCOL_NUM>12</SEGCOL_NUM><COL_SORTKEY>12</COL_SORTKEY><BASE_INTCOL_NUM>12</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>AUDIT_REASON</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>5</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>5</CHARLENGTH></COL_LIST_ITEM><COL_LIST_ITEM><OBJ_NUM>142293</OBJ_NUM><COL_NUM>13</COL_NUM><INTCOL_NUM>13</INTCOL_NUM><SEGCOL_NUM>13</SEGCOL_NUM><COL_SORTKEY>13</COL_SORTKEY><BASE_INTC
OL_NUM>13</BASE_INTCOL_NUM><BASE_COL_TYPE>0</BASE_COL_TYPE><PROPERTY>0</PROPERTY><NAME>REMARK</NAME><TYPE_NUM>1</TYPE_NUM><LENGTH>200</LENGTH><NOT_NULL>0</NOT_NULL><CHARSETID>852</CHARSETID><CHARSETFORM>1</CHARSETFORM><CHARLENGTH>200</CHARLENGTH></COL_LIST_ITEM></COL_LIST></STRMTABLE_T>
</ROW></ROWSET>可以看到表结构的xml信息以及从 Table data from 16309790584 until 16336012118 可以看出该表数据的offset区间。下面开始抽取工作：
DUL> unpump table SHJZ.SP_SURVIVALVALIDATION (SID VARCHAR2(100), PID VARCHAR2(100), VIDEO VARCHAR2(500), RESULTS VARCHAR2(100), VALIDATION_TIME DATE, WHY VARCHAR2(255), CREATE_USER VARCHAR2(100), CREATE_TIME DATE, UPDATE_USER VARCHAR2(100), UPDATE_TIME DATE, VALIDATION_TYPE VARCHAR2(10), AUDIT_REASON VARCHAR2(5), REMARK VARCHAR2(200)) dump file /root/SHJZ_20211108.DMP from 16309790584 until 16336012118;
168707 rows unloaded可以看到数据完整的抽取了出来，抽取出来的文件需要用sqlldr去导入
[oracle@dbtest dul]$ sqlldr SHJZ/SHJZ control=SHJZ_SP_SURVIVALVALIDATION.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Wed Feb 2 02:09:40 2011
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 322
Commit point reached - logical record count 386
Commit point reached - logical record count 450
Commit point reached - logical record count 514
Commit point reached - logical record count 578
...
Commit point reached - logical record count 168373
Commit point reached - logical record count 168437
Commit point reached - logical record count 168501
Commit point reached - logical record count 168565
Commit point reached - logical record count 168629
Commit point reached - logical record count 168693
Commit point reached - logical record count 168720
SQL> select count(*) FROM shjz.SP_SURVIVALVALIDATION;
COUNT(*)
----------
168694该表已经成功导入，整体的使用过程还是非常简单的，但是如果dmp中表非常多，那么手工单独去处理还是比较麻烦的，必须得用脚本来处理。
发表回复