Oracle impdp – How to Debug a Hanging Import

impdporacle-11g-r2

I am currently trying to import a database dump in Oracle XE 11gR2 On windows 7. I have the following command.

 impdp 'APPLICATION/APPLICATION@XE' dumpfile=APPLICATION.dmp REMAP_TABLESPACE=APPLICATION:USERS logfile=APPLICATIONIMPORT.log

I tried running the command in the windows command prompt but… nothing happens. The process does not exit or do anything. It print a copyright and just… hang without using any CPU or anything. The log file is not created, and SQL Developper show no import task when I query with SELECT * FROM DBA_DATAPUMP_JOBS;

I there anyway to see what is going on? Any log, table, place where an error could be outputted? That would really help me resolve this problem.

Note: If that can help, I am an administrator on my machine but part of a Windows Domain. It screwed up the Oracle XE install the first time but I got around by running the installer with a local admin account.

Best Answer

I got it to work with a weird fix : I removed the 'APPLICATION/APPLICATION@XE' and entered the username/password manually with the interactive prompt.

I consulted this page: http://www.orafaq.com/wiki/Datapump and realized that I was not getting an equivalent of this line Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production, which tipped me it was a connection problem. By trying to remove the username/password from the command line it suddenly fixed the problem.

I am afraid I can't exactly say why this helped, but I hope this can be useful to someone.

Related Solutions

How to find the schema name of a data pump dmp file

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump.

For example:

[oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt

Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "PHIL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "PHIL"."SYS_SQL_FILE_FULL_01":  phil/******** directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt 
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PHIL"."SYS_SQL_FILE_FULL_01" successfully completed at 15:15:05

[oracle@oel61 ~]$

Looking for CREATE USER DDL statements in the file should show you what schemas are needed in order to perform an import.

For example, from my test dump:

-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "PHIL" IDENTIFIED BY VALUES 'S:924B2E756404611021428644B4DF06A4A7BAB886837FCCFA510151E0FC44;181446AE258EE2F6'
      DEFAULT TABLESPACE "PHILDATA"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "PHIL";
GRANT CREATE SESSION TO "PHIL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "PHIL";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "PHIL" DEFAULT ROLE ALL;

The only drawback is that if the .dmp file is large the resulting SQL dump will be huge.

Imp paused mid import

your database is running in archivelog mode. An initial backup + archives allow you to recover your database to any point in time between the backup and the last available archivelog file. If this is a big import you are most likely going to make a backup afterwards. If you have no need to recover the database to a state between now and the previous backup, you could use rman to delete the archived log files, freeing space so the archiver can continue. An other option is to increase the db_recovery_file_dest_size parameter that could be in place since you claim to have plenty of space available.

sqlplus / as sysdba
show parameter db_recovery_file_dest_size
alter system set db_recovery_file_dest_size = {A_BIGGER_SIZE}

rman target=/
delete archivelog all;

don't forget to make a backup, deleting the archives makes recovery impossible.

Best Answer

Related Solutions

How to find the schema name of a data pump dmp file

Imp paused mid import

Related Question