Oracle imp crashes without error

exportimportoracle-10goracle-11g-r2

I have Oracle 10g XE database locally. I export schema with something like

exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp

Exp is 10.2.0.1.0 version – so is the DB.

Than I take this dump to another machine and try to import it to another remote machine. With

imp %USER%/%PASSWD%/'192.168.0.100/%SID%' ignore=y file=dump.dmp full=y

Oracle client with imp is version 11.2.0.1.0 and Oracle DB (normal, not express) is version 11.2.0.2.0

The problem is that after printing several messages about importing tables it suddenly stops:

. . importing table                      "TABLE_1"      17180 rows imported
. . importing table                      "TABLE_2"          0 rows imported
. . importing table                      "TABLE_3"         49 rows imported$:

and does not even give a newline symbol in the end.

The problem persists with rows=N and with only selected tables from this schema.

So can anybody tell:

  1. Where can be some error logs to know what is wrong?
  2. Some workaround (still using exp/imp utilities preferably) – we need to import dumps regularly (it is for continuous integration server), we need to do it from remote host (so impdp is hardly an option with need to mount so remote volumes), dependency on other DB is highly undesirable (dblink).

UPD: Unfortunately for all curious we have solved the problem switching continuous integration system to Oracle XE, where is no such problem. Sysadm has already switched off this server, so I cannot try anything. Sorry.

Best Answer

  1. Maybe an error appears in the target database's alert log? Run this:

    SQL> show parameter background_dump_dest

    ...and go to the directory given to check the alert_<SID>.log. Or do a trace of the imp session in the target database instance. (Usually the errors appear in the exp / imp output themselves (saved to a log with LOG=log_file.log ) but I assume the export ran file with no warnings and completes with):

    "Export terminated successfully without warnings."

    When you are copying the file, are you sure it's not getting corrupted somehow? (md5sum remains the same)

  2. Did you try importing into an empty schema so you don't have to set IGNORE=Y? And when you wrote:

    The problem persists with rows=N and with only selected tables from this schema.

Did you mean no matter which one or two tables you choose, it still crashes? Try simulating an import with SHOW=Y?

Related Question