OraFAQ Forum: Server Utilities » IMPDP hangs on table data

Home » RDBMS Server » Server Utilities » IMPDP hangs on table data (11.1.0.7.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

IMPDP hangs on table data [message #458028]

Wed, 26 May 2010 19:05

tmcallister
Messages: 107
Registered: December 2007

Senior 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?

Report message to a moderator

Re: IMPDP hangs on table data [message #458029 is a reply to message #458028]

Wed, 26 May 2010 19:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: IMPDP hangs on table data [message #458143 is a reply to message #458029]

Thu, 27 May 2010 10:05

tmcallister
Messages: 107
Registered: December 2007

Senior 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.

Report message to a moderator

Re: IMPDP hangs on table data [message #458144 is a reply to message #458143]

Thu, 27 May 2010 10:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> Something must be seriously wrong.
expdp & impdp still are infected by bugs, IMO.

Report message to a moderator

Re: IMPDP hangs on table data [message #610458 is a reply to message #458144]

Thu, 20 March 2014 04:46

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

Report message to a moderator

Re: IMPDP hangs on table data [message #610459 is a reply to message #610458]

Thu, 20 March 2014 04:55

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: IMPDP hangs on table data [message #610484 is a reply to message #610459]

Thu, 20 March 2014 09:42

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.

Report message to a moderator

Re: IMPDP hangs on table data [message #610490 is a reply to message #610484]

Thu, 20 March 2014 11:22

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: IMPDP hangs on table data [message #611071 is a reply to message #610490]

Thu, 27 March 2014 08:20

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.

Report message to a moderator

Re: IMPDP hangs on table data [message #611076 is a reply to message #611071]

Thu, 27 March 2014 08:48

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 11:12:43 CST 2025