Home » RDBMS Server » Server Utilities » IMPDP hangs on table data (11.1.0.7.0)
IMPDP hangs on table data [message #458028] Wed, 26 May 2010 19:05 Go to next message
tmcallister
Messages: 97
Registered: December 2007
Member
I'm trying to do a network datapump between oracle databases, and it seems to continually hang when it gets to the point where it should be processing table data.

C:\>impdp DP_USER/DP_USER parfile=sde_webmap_2.par

Import: Release 11.1.0.7.0 - 64bit Production on Wednesday, 26 May, 2010 17:42:03

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DP_USER"."SYS_IMPORT_FULL_01":  DP_USER/******** parfile=sde_webmap_2.par
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.006 GB
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE


It just sits at this point indefinitely.

The parfile for those interested:
directory=datapumps
logfile=sde_webmap_2.log
network_link=backup
full=y
INCLUDE=SCHEMA:"IN ('SDE_WEBMAP_BUSINESS','SDE_WEBMAP_BUSINESS_A','SDE_WEBMAP_BUSINESS_B')"


And the results from V$SESSION_LONGOPS

69    SYS_IMPORT_FULL_01        IMPORT    0    1031    MB    5/26/2010 5:50:37 PM    5/26/2010 6:03:29 PM


Any ideas?
Re: IMPDP hangs on table data [message #458029 is a reply to message #458028] Wed, 26 May 2010 19:22 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
select * from v$sess_io where sid = (select sid from v$session where program like 'udi%');

While impdp is running invoke SQL above multiple times with a few seconds delay between runs.

The returned values will be increasing if/when impdp is actively working
Re: IMPDP hangs on table data [message #458143 is a reply to message #458029] Thu, 27 May 2010 10:05 Go to previous messageGo to next message
tmcallister
Messages: 97
Registered: December 2007
Member
BlackSwan wrote on Wed, 26 May 2010 18:22
select * from v$sess_io where sid = (select sid from v$session where program like 'udi%');


I had to change the like clause to 'impdp%' to get any results, and once I did this is what I got.

131	4967	49704	1	5316	42


The third column CONSISTENT_GETS (49704) is in increasing by about 4 every 5 seconds, and the V$SESSION_LONGOPS query still shows 0 progress.

140	69	SYS_IMPORT_FULL_01		IMPORT	0	1031	MB


16+ hours to datapump ~1gb? Something must be seriously wrong. I'm going to try doing the same but without the database link.
Re: IMPDP hangs on table data [message #458144 is a reply to message #458143] Thu, 27 May 2010 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
> Something must be seriously wrong.
expdp & impdp still are infected by bugs, IMO.
Re: IMPDP hangs on table data [message #610458 is a reply to message #458144] Thu, 20 March 2014 04:46 Go to previous messageGo to next message
johnny.renegard@gmail.com
Messages: 3
Registered: March 2014
Location: Sweden
Junior Member
Hi! We have encountered the same problem... Did you find any solution?

We are using the database link locally (just for better performance compared to export to file and then import).
We have:
- Win server 2008 64bit using Oracle 11gR2 32-bit installation.

We have a lot of stored functions (~658) in the schema we import. No tables with long-typed columns.
On one server it works fine (it is not connected to internet, only local network). On the other server we have approximately 50% success rate (this one is located on a wide company network with access to internet).

We suspect that the operation impdp can be sensitive to network delay variations or heavy load on server. But that's just guessing so far. We plan on upgrading to 64bit oracle, though some crucial 3-party stuff is not yet compatible with 64-bit.

Regards,
Johnny
Re: IMPDP hangs on table data [message #610459 is a reply to message #610458] Thu, 20 March 2014 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59749
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First you have to find where the import stay using "status" parameter.

Quote:
impdp can be sensitive to network delay variations or heavy load on server


Are you making an impdp across network that is using NETWORK_LINK option?

Re: IMPDP hangs on table data [message #610484 is a reply to message #610459] Thu, 20 March 2014 09:42 Go to previous messageGo to next message
johnny.renegard@gmail.com
Messages: 3
Registered: March 2014
Location: Sweden
Junior Member
Hi!

We use network_link with the following commands:

impdp xxx/yyy@TEST1 directory=SERVERTEMPFOLDER logfile=CopyOfRUS_3.log network_link=RUS_3_LINK remap_schema=RUS_3:RUS_4 exclude=table:\"IN ('CHANGELOG', 'MAINTENANCE')\" table_exists_action=replace

create database link RUS_3_LINK connect to RUS_3 identified by xxx using 'TEST1'

I will try to reproduce the error again and check status parameters... It works from my development env. all the time but not from the customers computer located geografically far away.


Re: IMPDP hangs on table data [message #610490 is a reply to message #610484] Thu, 20 March 2014 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59749
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
all the time but not from the customers computer located geografically far away.


I vote for a network issue. Smile

Re: IMPDP hangs on table data [message #611071 is a reply to message #610490] Thu, 27 March 2014 08:20 Go to previous messageGo to next message
johnny.renegard@gmail.com
Messages: 3
Registered: March 2014
Location: Sweden
Junior Member
Now it happened again... this was the status of the impdp-job:
---
Job: SYS_IMPORT_SCHEMA_01
Operation: IMPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: VIDSEL_2
Object Name: PO_F21_97
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1,098
Worker Parallelism: 1
---
What differs the table PO_F21_97 from the other tables is that it contains large geometries (sdo_geometry). When we kill the job and then try it again it usually works. It seems that the job hangs when impdp is initializing the tables that is going to be filled.

When attaching to the job there were no action at all. tried the 'continue' command without any change in behaviour.
Re: IMPDP hangs on table data [message #611076 is a reply to message #611071] Thu, 27 March 2014 08:48 Go to previous message
Michel Cadot
Messages: 59749
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try to import first the metadata alone then, in another job, the data.

Previous Topic: ORA-39070: Unable to open the log file
Next Topic: LOADER. Get all errors at once
Goto Forum:
  


Current Time: Sun Nov 23 07:46:21 CST 2014

Total time taken to generate the page: 0.23343 seconds