求助!Exp成功无报错信息,imp却报奇怪错误

[xhtang888]

oracle 10.2.0 (windows xp) 将一个大表数据export出来,34G, 文件直接export 到 USB 硬盘,再将此文件ftp 到 linux (Red hat AS4)机器上, 在 oracle 10.2 (Linux) 下导入,导入 60% 数据时候,报错:
Import: Release 10.2.0.1.0 - Production on Mi Nov 21 13:45:19 2007
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

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "TEST"
illegal lob length marker 33238
bytesread = 00000000000
TABLE = TEST
IMP-00098: INTERNAL ERROR: impgst2
IMP-00018: partial impo?'?l???‰“’E3”??=?^??=?ompleted: 144371 rows imported
IMP-00008: unrecognized statement in the export file:

IMP的语句:imp test/test tables="("test")" file=test.dmp IGNORE=Y commit=Y buffer=1000000000 log=test.log
exp 的LOG:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TEST
...........................................................................
...........................................................................
...........................................................................
....................................
261869 rows exported
Export terminated successfully without warnings.

TEST表包含LOB 数据:
Name Null? Type
----------------------------------------- -------- ----------------------------
BBID_ID NOT NULL NUMBER(19)
PATH VARCHAR2(255 CHAR)
DATA BLOB
DT_EXPIRATION DATE
ID_RPK NUMBER(19)

导出就一个表的数据, 一个文件,导入时的设置应该没错,不然也不会导进去60% 多的数据,DB 的 charset 也一致.

将此文件放到 一个 10g (winxp )的DB 下去 IMP ,也是相同的错, 网上对 illegal lob length marker 33238
bytesread = 00000000000 说明很少,不知道 确切原因.

请大家看看,有没遇到过,知道确切原因的? 如果是 EXP DUMP 文件有问题,LOG 却是成功无错的, 那以后做EXP 也不放心, 有没有检查DUMP文件有无错的 方法?

[hongqi162]

我又一次导出过30G的dmp文件，包含BLOB字段，导入的时候也出现的问题，原因是表空间不足

[Croatia]

具体的原因，我也不是很清楚。看时看到有人出过和你类似的问题。

察看了一下错误信息,

因为是IMP-00008发生了，导致IMP-00018的只是部分文件的导入。到最后的IMP-00098的内部错误。

IMP-00008: unrecognized statement in the export file: string
Cause: Import did not recognize a statement in the export file. Either the export file was corrupted, or an Import internal error has occurred.
Action: If the export file was corrupted, retry with a new export file. Otherwise, report this as an Import internal error and submit the export file to customer support.

我觉得可能的原因：
1。你指定的buffer不足。
2。ftp的时候，你用了什么模式来传送的？

[xhtang888]

buffer=1000000000 应该够大了, 之后我试过 IMP 从另外一个DB 导出的数据,也是这个表,但数据少一点,EXP 分开成4个文件,每个文件最大10G,一共31G,导入就成功.
FTP 确信是BIN模式的.

有没可能导出原表存在坏块,EXP 的时候是没提示的?

[xhtang888]

"原因是表空间不足" -- 不是这个原因,表空间足够,如果是也会有相关提示的错误.

"那你把你现在这次的导出也分成几个文件看看" -- 那个DB不容易接触到, 就算是分成小文件导出成功,导入成功, 也没有找到这次问题的真正原因啊, EXP 导出没有报错, 为什么 IMP就有这样的错误? 如果不能确定原因,那么以后的 EXP 你怎么能够确认 是一定成功,一定可用的呢?

[Croatia]

导出的文件有错误的话，他会提示你的。

那你把你现在这次的导出也分成几个文件看看呢。

[Croatia]

你非要知道原因的话，只有我上面写的。直接联系oracle客户服务中心，把你的文件想办法发给他们。
他们会给你答案。

[xhtang888]

还是导出的数据有问题

[crazylaa]

exp的时候有没有指定direct=y?如果指定了，有的错导出时是不会报的。

[EastHoo]

我觉得不会是buffer参数值问题，因为buffer只是数据缓冲区，只影响时间导入导出的性能而已。
会不会是导出的文件太大，linux (Red hat AS4)不支持？（我对这系统不熟悉，抱歉，建议分割成过个文件试试）。

还有请教下楼主，你这样中途导入失败，岂不是很危险，毕竟已经有很多数据被导入并且commit了，不知道您是怎么处理的。

[hunterjk]

blob类型的数据可以导入吗？

[傻儿哥]

关注一下....

[傻儿哥]

搜了下metalink,下面的 贴子比较象.加上 show=y
参数尝试下吧
||||||||||||||||||
IMP-00098: INTERNAL ERROR: impgst2Segmentation Fault - core dumped [ID 578616.1]

--------------------------------------------------------------------------------

修改时间 21-JAN-2009 类型 PROBLEM 状态 PUBLISHED

In this Document
Symptoms
Cause
Solution
References



--------------------------------------------------------------------------------



Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 10.2.0.4
This problem can occur on any platform.

Symptoms
An import command fails with the following error:

illegal lob length marker 65535
bytesread = 00000000000
TABLE =
IMP-00098: INTERNAL ERROR: impgst2Segmentation Fault - core dumped

If executing the same import command by adding "show=y" same error is reported:

illegal lob length marker 65535
bytesread = 00000000000
TABLE =
IMP-00098: INTERNAL ERROR: impgst2Segmentation Fault - core dumped




Cause
The fact that running import with "show=y" generates same error indicates that the export dump file is corrupted.

When "show=y" is used the content of the export dump file is listed to the display and not imported.

There are two ways to corrupt an export dump file:
- the export tool corrupts the file itself;
- the file is corrupted during the transfer (move/copy) from the source to the target machine; this is the most common cause of the error.




Solution
Since the export dump file is corrupt, the export needs to be done again:

- After the export is finished one should always check if the export dump file is corrupted on the
source machine using imp with "show=y". If getting the above errors then the file is corrupted on the source machine;

- Assuming the file is not corrupted on the source machine, after transferring the file, one must execute imp with "show=y" on target machine. If getting the above errors then the file was corrupted during transfer.
Use ftp command in binary mode as a way of transferring the dump file between source and target machines.

- If imp with "show=y" reports no errors after the above steps then proceed with the real import (without "show=y").

NOTE: A similar case is reported in Note 3890213.8 and may need to be investigated as well.

References
NOTE:3890213.8 - Bug 3890213 - IMP-98 possible importing zero length Lobs with CHUNKSIZE > 32767

[SambaGao]

关注。