Home » RDBMS Server » Server Utilities » IMPDP hangs (10.2.0.4)
IMPDP hangs [message #518564] Thu, 04 August 2011 12:21 Go to next message
varunvir
Messages: 330
Registered: November 2007
Senior Member
Experts,
I am using expdp/impdp to migrate 4 TB database from solaris
to Linux.But the import process is taking forever.
Can you please tell me any other methods for migrating.

Thanks,
Varun
Re: IMPDP hangs [message #518565 is a reply to message #518564] Thu, 04 August 2011 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59159
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
RMAN?

Regards
Michel
Re: IMPDP hangs [message #518566 is a reply to message #518565] Thu, 04 August 2011 12:30 Go to previous messageGo to next message
varunvir
Messages: 330
Registered: November 2007
Senior Member
Michel,
Do you mean RMAN Duplicate?

Thanks,
Varun
Re: IMPDP hangs [message #518570 is a reply to message #518566] Thu, 04 August 2011 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59159
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Backup and Recovery Advanced User's Guide
Chapter 15 RMAN Cross-Platform Transportable Databases and Tablespaces

Regards
Michel

[Updated on: Thu, 04 August 2011 13:10]

Report message to a moderator

Re: IMPDP hangs [message #518572 is a reply to message #518570] Thu, 04 August 2011 13:28 Go to previous messageGo to next message
varunvir
Messages: 330
Registered: November 2007
Senior Member
Thanks Michel
Re: IMPDP hangs [message #518575 is a reply to message #518564] Thu, 04 August 2011 15:52 Go to previous messageGo to next message
John Watson
Messages: 4573
Registered: January 2010
Location: Global Village
Senior Member
Perhaps you could tune the export/import process a bit. Some suggestions:

Set all the check constraints to ENABLE NOVALIDATE before you export.
Set all indexes to NOLOGGING before you export.
Set all the destination tablespaces to NOLOGGING before you import.
Set your PGA_AGGREGATE_TARGET to many GB more than usual.
Set DB_BLOCK_CHECKING/CHECKSUM=false
Set "_smm_max_size"=4000000
Set "_pga_max_size"=4000000000
Use lots of Data Pump worker processes
Don't use network mode

Just a few ideas - please don't blame me if they don't work. If they do, or if you have any other ideas, I would like to know. I have a problem at the moment with a Data Pump exp/imp that is taking too long.
Re: IMPDP hangs [message #518580 is a reply to message #518575] Thu, 04 August 2011 21:25 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
>I have a problem at the moment with a Data Pump exp/imp that is taking too long.

ALTER SESSION SET SQL_TRACE=TRUE

above can provide details about where time is being spent
query v$session_longops
Re: IMPDP hangs/oracle10g/windowsXP [message #571215 is a reply to message #518580] Thu, 22 November 2012 04:03 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi ,
I am also facing the same issue of hanging impdp operation at the place of "Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX". The dumpfile contains only metadata only.

Import> status

Job: IMPFULLVERSION
  Operation: IMPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: E:\tools\oracle10g_EE\oracle\product\10.2.0\db_1\admin\orcl\dpdump\expdp_fullNov16.dmp

Worker 1 Status:
  State: EXECUTING
  Object Schema: VERSION
  Object Name: ABC_P
  Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
  Completed Objects: 93
  Worker Parallelism: 1


if I exclude the above object during impdp , then the process will hang on some other object. Please suggest a solution.
Re: IMPDP hangs/oracle10g/windowsXP [message #571220 is a reply to message #571215] Thu, 22 November 2012 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59159
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan told what to do.

Regards
Michel
Re: IMPDP hangs/oracle10g/windowsXP [message #571279 is a reply to message #571220] Thu, 22 November 2012 22:41 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi,

The hanging at the place of index of impdp process is solved by adding tablespace . Actually I am importing only metadata and enough space was also there in the tablespace alloted , again it shows the error 'ORA-01659: unable to allocate MINEXTENTS beyond string in tablespace string '. Now the impdp statement got end with the below error :

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.

Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" stopped due to fatal error at 10:05:53


in the alert_orcl.log , the error is

Errors in file e:\tools\oracle10g_ee\oracle\product\10.2.0\db_1\admin\orcl\bdump\orcl_dw01_5956.trc:
ORA-00600: internal error code, arguments: [kkzlcsl-1], [0], [], [], [], [], [], []
Re: IMPDP hangs/oracle10g/windowsXP [message #571283 is a reply to message #571279] Fri, 23 November 2012 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 19633
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORA-00600 is usually bad news and means that you should contact Oracle support.
Re: IMPDP hangs/oracle10g/windowsXP [message #571289 is a reply to message #571283] Fri, 23 November 2012 00:50 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Okay thanks. Can anyone help to understand the other issue I faced? Even after having enough tablespace , It has given the error -'ORA-01659: unable to allocate MINEXTENTS beyond string in tablespace string '.
Re: IMPDP hangs/oracle10g/windowsXP [message #571297 is a reply to message #571289] Fri, 23 November 2012 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59159
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01659: unable to allocate MINEXTENTS beyond %s in tablespace %s
 *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
          for the segment being created.
 *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
          tablespace or retry with smaller value for MINEXTENTS, NEXT or
          PCTINCREASE

Regards
Michel
Re: IMPDP hangs/oracle10g/windowsXP [message #571314 is a reply to message #571297] Fri, 23 November 2012 04:24 Go to previous message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Thanks!!
Previous Topic: Wrapping an oracle package
Next Topic: Unable to import one procedure from full database dump ( 10g )
Goto Forum:
  


Current Time: Mon Sep 22 07:29:22 CDT 2014

Total time taken to generate the page: 0.04751 seconds