OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » EXPDP hangs (Oracle 11g R2)

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

EXPDP hangs [message #616170]

Thu, 12 June 2014 23:06

rchenna
Messages: 25
Registered: February 2008

Junior Member

Hi all,

I have an export issue using EXPDP command. I spent enough time on Google. It was working till 4 days back. When I use EXPDP command, I could see following output after a very long time.

[oracle@adarna trace]$ expdp SEPHORA_CORP_NE_UAT/sephora_corp_ne_uat tables=fr_employees directory=dmpdir dumpfile=V5_DEMO_SEPHORA_IDC_12Jun2014.dmp logfile=V5_DEMO_SEPHORA_IDC_12Jun2014.log

Export: Release 11.2.0.1.0 - Production on Thu Jun 12 20:52:46 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user SEPHORA_CORP_NE_UAT
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-31635: unable to establish job resource synchronization

Report message to a moderator

Re: EXPDP hangs [message #616173 is a reply to message #616170]

Fri, 13 June 2014 00:06

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

Senior Member
Account Moderator

ORA-31635: unable to establish job resource synchronization
 *Cause:  A lock used in synchronizing Data Pump resources during job
          creation and deletion could not be obtained during job creation.
          This indicates that a process for some other Data Pump job has not
          released the lock due to an internal error.
 *Action: Eliminate the processes for any failed Data Pump job and try to
          create the new job again.  If the error continues to occur, contact
          Oracle Customer Support and report the error.

Report message to a moderator

Re: EXPDP hangs [message #616188 is a reply to message #616173]

Fri, 13 June 2014 03:39

rchenna
Messages: 25
Registered: February 2008

Junior Member

Thanks for the reply.

I ran the following command and got this result. I am sure I am only person using this database. I am not sure why one record STATE showing as EXECUTING

SELECT owner_name, job_name, operation, state FROM DBA_DATAPUMP_JOBS;

V6_DEMO_QA            	SYS_EXPORT_SCHEMA_01          	EXPORT                        	EXECUTING  
SYSTEM                	SYS_IMPORT_SCHEMA_02          	IMPORT                        	NOT RUNNING
SYSTEM                	SYS_IMPORT_SCHEMA_01          	IMPORT                        	NOT RUNNING
SEPHORA_STORES_EX_UAT 	SYS_EXPORT_SCHEMA_01          	EXPORT                        	NOT RUNNING
V6_DEMO_QA            	BIN$+7R4xF57EgTgQAB/AQBr6w==$0	EXPORT                        	NOT RUNNING
SYSTEM                	SYS_IMPORT_SCHEMA_03          	IMPORT                        	NOT RUNNING
SYSTEM                	SYS_EXPORT_SCHEMA_01          	EXPORT                        	NOT RUNNING
NETSUITE_V5_DEV       	SYS_EXPORT_SCHEMA_01          	EXPORT                        	NOT RUNNING
SEPHORA_STORES_EX_PROD	SYS_EXPORT_SCHEMA_01          	EXPORT                        	NOT RUNNING

How to remove the EXECUTING state record?

Report message to a moderator

Re: EXPDP hangs [message #616209 is a reply to message #616188]

Fri, 13 June 2014 08:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

it appears a copy of V6_DEMO_QA exists within the RECYCLEBIN

post results from SQL below

SELECT DECODE(request,0,'Holder: ','Waiter: ')||vl.sid sess, status,
id1, id2, lmode, request, vl.type
FROM V$LOCK vl, v$session vs
WHERE (id1, id2, vl.type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
and vl.sid = vs.sid
ORDER BY id1, request
/

Report message to a moderator

Re: EXPDP hangs [message #616215 is a reply to message #616209]

Fri, 13 June 2014 09:27

rchenna
Messages: 25
Registered: February 2008

Junior Member

This query did not give any output.

Report message to a moderator

Re: EXPDP hangs [message #616216 is a reply to message #616215]

Fri, 13 June 2014 09:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If the error continues to occur, contact Oracle Customer Support and report the error.

Report message to a moderator

Re: EXPDP hangs [message #616218 is a reply to message #616188]

Fri, 13 June 2014 10:22

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

Senior Member
Account Moderator

V6_DEMO_QA            	BIN$+7R4xF57EgTgQAB/AQBr6w==$0	EXPORT                        	NOT RUNNING

Always use PURGE option when you drop a data pump master table.
Now purge your recycle bin.

2/
Quote:

How to remove the EXECUTING state record?

expdp user/psw attach=V6_DEMO_QA.SYS_EXPORT_SCHEMA_01
Export> kill_job

3/
Drop all datapump master tables you no more need.

[Updated on: Fri, 13 June 2014 10:22]

Report message to a moderator

Re: EXPDP hangs [message #616320 is a reply to message #616170]

Sun, 15 June 2014 18:03

rchenna
Messages: 25
Registered: February 2008

Junior Member

I have removed all the master tables and purged them. I found zero records in DBA_DATAPUMP_JOBS table. When I run EXPDP commands, I could see now following output and it hangs there.

[oracle@adarna ~]$ expdp system/sys schemas=v6_demo_qa directory=dmpdir dumpfile=v6_demo_qa_06Jun2014.dmp logfile=v6_demo_qa_06Jun2014.log

Export: Release 11.2.0.1.0 - Production on Sun Jun 15 15:53:28 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=v6_demo_qa directory=dmpdir dumpfile=v6_demo_qa_06Jun2014.dmp logfile=v6_demo_qa_06Jun2014.log

In the Alert log I could see corresponding messages

Sun Jun 15 15:52:15 2014
DM00 started with pid=37, OS id=776, job SYSTEM.SYS_EXPORT_SCHEMA_01
Sun Jun 15 15:53:29 2014
DM00 started with pid=37, OS id=1215, job SYSTEM.SYS_EXPORT_SCHEMA_01
Sun Jun 15 15:53:29 2014
DW00 started with pid=38, OS id=1233, wid=1, job SYSTEM.SYS_EXPORT_SCHEMA_01

Any further help is really appreciated

Report message to a moderator

Re: EXPDP hangs [message #616338 is a reply to message #616320]

Mon, 16 June 2014 00:18

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

Senior Member
Account Moderator

Use the different %DATAPUMP%,, V$LOCK and V$SESSION views to know what it does and what it is waiting for.

Report message to a moderator

Re: EXPDP hangs [message #616414 is a reply to message #616320]

Mon, 16 June 2014 12:48

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

You can also check the job status every 15 (or other) seconds, open another terminal session and:

$ expdp / ATTACH=SYSTEM.SYS_EXPORT_SCHEMA_01
export> STATUS=15
export> EXIT_CLIENT

Report message to a moderator

Re: EXPDP hangs [message #616974 is a reply to message #616170]

Mon, 23 June 2014 13:24

rchenna
Messages: 25
Registered: February 2008

Junior Member

My apologies for the delay.

I just the see the following information when I try to get the status

Export> STATUS=15

Job: SYS_EXPORT_SCHEMA_01
  Operation: EXPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/v6_demo_qa_16Jun2014.dmp
    bytes written: 4,096

Worker 1 Status:
  State: UNDEFINED

Report message to a moderator

Previous Topic:	EXPDP getting error ORA-39002
Next Topic:	special character while loading

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 19 01:59:35 CDT 2025