Home » RDBMS Server » Server Utilities » datapump Export taking long time(HUNG) (11gr2 LINUX)
datapump Export taking long time(HUNG) [message #564425] Thu, 23 August 2012 11:46 Go to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
i'm doing full database export
Expdp directory=xxx.dmp dumpfile=aaa.dmp logfile=xxx.log FULL=Y
: :: : : :: : : : ;
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.87 MB
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/DB_LINK


then my export hangs..... Sad
checked in alert log nothing found.

and then killed the job and reran again but same....checked the status and it's saying EXECUTING, dont know where i'm doing wrong, can anyone please help me out

thanks in advance
Re: datapump Export taking long time(HUNG) [message #564426 is a reply to message #564425] Thu, 23 August 2012 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


determine Session ID of the export session
then periodically issue SQL below

SELECT * FROM V$SESS_IO WHERE SID = <export_session_ID>;

if/when one or more of the reported value is increasing, then export session is busying working
& you need to be patient
Re: datapump Export taking long time(HUNG) [message #564427 is a reply to message #564425] Thu, 23 August 2012 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Exclude the db links and retry.
If it works then you are sure the problem is in the db links and then you could:
1/ Try each one
2/ Export in another way.

You could also try to export ONLY the db links.

Regards
Michel
Re: datapump Export taking long time(HUNG) [message #564435 is a reply to message #564427] Thu, 23 August 2012 14:14 Go to previous messageGo to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
Thank you all but its not only stopping at db links when i try other time it's stopping at some other place(ex: object grants)
Re: datapump Export taking long time(HUNG) [message #564436 is a reply to message #564435] Thu, 23 August 2012 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is not stopping but working or waiting for something to be able to work.
You have to post more information for more help starting with BlackSwan query and the following one (still periodically):
select last_call_et, state, wait_time, second_in_waits, event
from v$session
where sid=<export_session_ID>
/

Regards
Michel

[Updated on: Thu, 23 August 2012 14:22]

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564439 is a reply to message #564436] Thu, 23 August 2012 15:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Thank you all but its not only stopping at db links when i try other time it's stopping at some other place(ex: object grants)

you have a mystery & we have no clues.
Only YOU can provide insight as to what the Oracle engine is actually doing.

What is the approximate size of the FULL database that you are attempting to export?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: datapump Export taking long time(HUNG) [message #564453 is a reply to message #564439] Thu, 23 August 2012 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is the approximate size of the FULL database that you are attempting to export?

Quote:
Total estimation using BLOCKS method: 24.87 MB


Regards
Michel
Re: datapump Export taking long time(HUNG) [message #564507 is a reply to message #564453] Fri, 24 August 2012 08:41 Go to previous messageGo to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
Size of db is around 3.5GB
Re: datapump Export taking long time(HUNG) [message #564508 is a reply to message #564507] Fri, 24 August 2012 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But with 25MB of real data if Data Pump is correct.

Did you do what we asked you?

Regards
Michel
Re: datapump Export taking long time(HUNG) [message #564707 is a reply to message #564508] Mon, 27 August 2012 14:00 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Somewhere along the Oracle10g there was a bug involving datapump hanging on ... Which version do you use? Usually you can see the waitstats of the datapump process(es)
Re: datapump Export taking long time(HUNG) [message #564712 is a reply to message #564707] Mon, 27 August 2012 16:30 Go to previous messageGo to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
I'm using 11.2.0.2.0 LINUX

again i started datapump export for multiple schemas

$ expdp dumpfile=dds_0827.dmp directory=EXPORT_DIR schemas=schema1,schema2
Export: Release 11.2.0.2.0 - Production on Mon Aug 27 20:30:48 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_03": /******** AS SYSDBA dumpfile=dds_0827.dmp directory=EXPORT_DIR schemas=schema1,schema2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.757 GB
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/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

just Hanging
Re: datapump Export taking long time(HUNG) [message #564713 is a reply to message #564712] Mon, 27 August 2012 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
unless & until you post requested details, You're On Your Own (YOYO)!
Re: datapump Export taking long time(HUNG) [message #564714 is a reply to message #564713] Mon, 27 August 2012 18:01 Go to previous messageGo to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
Total DB size around 60 GB
11gR2
Linux 5.6
exporting 2 schemas

hanging..........

Tell me what else need from me........any suggestions??

Thanks in advance
Re: datapump Export taking long time(HUNG) [message #564715 is a reply to message #564714] Mon, 27 August 2012 18:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
use your browser to search for question marks.
ensure you have provided an answer to each & every question in this thread asked of you
Re: datapump Export taking long time(HUNG) [message #564716 is a reply to message #564715] Mon, 27 August 2012 18:21 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Webcrawling found someone post on a similar problem on OTN Maybe it got fixed in 11.2.0.3.3
Also check this page, title "On 4 undocumented DataPump parameters"
Ain't cool waiting for a future version? Range of versions believed to be affected Versions BELOW 12.1; Bug 9791589 - Data Pump export hang / uses excessive memory with many "WITH GRANT OPTION" grants - superceded [ID 9791589.8]

[Updated on: Mon, 27 August 2012 18:35]

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564962 is a reply to message #564716] Wed, 29 August 2012 14:18 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I use the following and export one schema at a time. If one of the schemas hang then you know where to look.
ECSCDAQ > select 'expdp  system/manager schemas='||username||
  2  ' directory=MY_DIR dumpfile='||username||'.dmp logfile=expdp'
  3  ||username||'.log'
  4  from dba_users order by username;

expdp  system/manager schemas=APPQOSSYS directory=MY_DIR dumpfile=APPQOSSYS.dmp logfile=expdpAPPQOSSYS.log
expdp  system/manager schemas=CDA_APPS directory=MY_DIR dumpfile=CDA_APPS.dmp logfile=expdpCDA_APPS.log
expdp  system/manager schemas=CDA_DEV_MON directory=MY_DIR dumpfile=CDA_DEV_MON.dmp logfile=expdpCDA_DEV_MON.log
expdp  system/manager schemas=CDA_PV_APPS directory=MY_DIR dumpfile=CDA_PV_APPS.dmp logfile=expdpCDA_PV_APPS.log
expdp  system/manager schemas=CDA_PV_RO_USER directory=MY_DIR dumpfile=CDA_PV_RO_USER.dmp logfile=expdpCDA_PV_RO_USER.log
expdp  system/manager schemas=CDA_RO_USER directory=MY_DIR dumpfile=CDA_RO_USER.dmp logfile=expdpCDA_RO_USER.log
expdp  system/manager schemas=CDA_SHARED directory=MY_DIR dumpfile=CDA_SHARED.dmp logfile=expdpCDA_SHARED.log
expdp  system/manager schemas=CDA_SHARED_USER directory=MY_DIR dumpfile=CDA_SHARED_USER.dmp logfile=expdpCDA_SHARED_USER.log
expdp  system/manager schemas=CDA_WR_USER directory=MY_DIR dumpfile=CDA_WR_USER.dmp logfile=expdpCDA_WR_USER.log
expdp  system/manager schemas=CMS_PV_WR_USER directory=MY_DIR dumpfile=CMS_PV_WR_USER.dmp logfile=expdpCMS_PV_WR_USER.log
expdp  system/manager schemas=CMS_WR_USER directory=MY_DIR dumpfile=CMS_WR_USER.dmp logfile=expdpCMS_WR_USER.log
expdp  system/manager schemas=CSCDA_QAR directory=MY_DIR dumpfile=CSCDA_QAR.dmp logfile=expdpCSCDA_QAR.log
expdp  system/manager schemas=CSCDA_QAW directory=MY_DIR dumpfile=CSCDA_QAW.dmp logfile=expdpCSCDA_QAW.log
expdp  system/manager schemas=CTXSYS directory=MY_DIR dumpfile=CTXSYS.dmp logfile=expdpCTXSYS.log
expdp  system/manager schemas=DBSNMP directory=MY_DIR dumpfile=DBSNMP.dmp logfile=expdpDBSNMP.log
expdp  system/manager schemas=DIP directory=MY_DIR dumpfile=DIP.dmp logfile=expdpDIP.log
expdp  system/manager schemas=DMSYS directory=MY_DIR dumpfile=DMSYS.dmp logfile=expdpDMSYS.log
expdp  system/manager schemas=ECSCDAQ directory=MY_DIR dumpfile=ECSCDAQ.dmp logfile=expdpECSCDAQ.log
expdp  system/manager schemas=EXFSYS directory=MY_DIR dumpfile=EXFSYS.dmp logfile=expdpEXFSYS.log
expdp  system/manager schemas=IMAGE_SERVICE directory=MY_DIR dumpfile=IMAGE_SERVICE.dmp logfile=expdpIMAGE_SERVICE.log
expdp  system/manager schemas=IMAGE_SERVICE_USER directory=MY_DIR dumpfile=IMAGE_SERVICE_USER.dmp logfile=expdpIMAGE_SERVICE_USER.log
expdp  system/manager schemas=IPSOFT directory=MY_DIR dumpfile=IPSOFT.dmp logfile=expdpIPSOFT.log
expdp  system/manager schemas=MDDATA directory=MY_DIR dumpfile=MDDATA.dmp logfile=expdpMDDATA.log
expdp  system/manager schemas=MDSYS directory=MY_DIR dumpfile=MDSYS.dmp logfile=expdpMDSYS.log
expdp  system/manager schemas=MGMT_VIEW directory=MY_DIR dumpfile=MGMT_VIEW.dmp logfile=expdpMGMT_VIEW.log
expdp  system/manager schemas=OLAPSYS directory=MY_DIR dumpfile=OLAPSYS.dmp logfile=expdpOLAPSYS.log
expdp  system/manager schemas=ORACLE_OCM directory=MY_DIR dumpfile=ORACLE_OCM.dmp logfile=expdpORACLE_OCM.log
expdp  system/manager schemas=ORDDATA directory=MY_DIR dumpfile=ORDDATA.dmp logfile=expdpORDDATA.log
expdp  system/manager schemas=ORDPLUGINS directory=MY_DIR dumpfile=ORDPLUGINS.dmp logfile=expdpORDPLUGINS.log
expdp  system/manager schemas=ORDSYS directory=MY_DIR dumpfile=ORDSYS.dmp logfile=expdpORDSYS.log
expdp  system/manager schemas=OUTLN directory=MY_DIR dumpfile=OUTLN.dmp logfile=expdpOUTLN.log
expdp  system/manager schemas=SI_INFORMTN_SCHEMA directory=MY_DIR dumpfile=SI_INFORMTN_SCHEMA.dmp logfile=expdpSI_INFORMTN_SCHEMA.log
expdp  system/manager schemas=SPATIAL_CSW_ADMIN_USR directory=MY_DIR dumpfile=SPATIAL_CSW_ADMIN_USR.dmp logfile=expdpSPATIAL_CSW_ADMIN_USR.log
expdp  system/manager schemas=SPATIAL_WFS_ADMIN_USR directory=MY_DIR dumpfile=SPATIAL_WFS_ADMIN_USR.dmp logfile=expdpSPATIAL_WFS_ADMIN_USR.log
expdp  system/manager schemas=SYS directory=MY_DIR dumpfile=SYS.dmp logfile=expdpSYS.log
expdp  system/manager schemas=SYSTEM directory=MY_DIR dumpfile=SYSTEM.dmp logfile=expdpSYSTEM.log
expdp  system/manager schemas=TEST directory=MY_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
expdp  system/manager schemas=WMSYS directory=MY_DIR dumpfile=WMSYS.dmp logfile=expdpWMSYS.log
expdp  system/manager schemas=XDB directory=MY_DIR dumpfile=XDB.dmp logfile=expdpXDB.log
expdp  system/manager schemas=XS$NULL directory=MY_DIR dumpfile=XS$NULL.dmp logfile=expdpXS$NULL.log
expdp  system/manager schemas=YSUN directory=MY_DIR dumpfile=YSUN.dmp logfile=expdpYSUN.log

[Updated on: Wed, 29 August 2012 14:19]

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #676642 is a reply to message #564425] Wed, 26 June 2019 13:07 Go to previous messageGo to next message
Nitheesan
Messages: 1
Registered: June 2019
Junior Member
Use - EXCLUDE=DB_LINK - will work

Thanks!
Re: datapump Export taking long time(HUNG) [message #676643 is a reply to message #676642] Wed, 26 June 2019 13:18 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Nitheesan wrote on Wed, 26 June 2019 11:07
Use - EXCLUDE=DB_LINK - will work

Thanks!

You don't get a second chance to make a FIRST impression.
This thread is only about 7 YEARS old & last time OP visited this forum was 2013, so I doubt he will benefit from your response.

Please do NOT resurrect zombie threads that have been inactive for YEARS.
Previous Topic: ORA-04031 error with EXPDP
Next Topic: SQL*Loader - wildcard character
Goto Forum:
  


Current Time: Thu Mar 28 09:46:16 CDT 2024