Home » RDBMS Server » Server Utilities » EXPDP hangs (Oracle 11g R2)
EXPDP hangs [message #616170] Thu, 12 June 2014 23:06 Go to next message
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
Re: EXPDP hangs [message #616173 is a reply to message #616170] Fri, 13 June 2014 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: EXPDP hangs [message #616188 is a reply to message #616173] Fri, 13 June 2014 03:39 Go to previous messageGo to next message
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?
Re: EXPDP hangs [message #616209 is a reply to message #616188] Fri, 13 June 2014 08:00 Go to previous messageGo to next message
BlackSwan
Messages: 22801
Registered: January 2009
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
/
Re: EXPDP hangs [message #616215 is a reply to message #616209] Fri, 13 June 2014 09:27 Go to previous messageGo to next message
rchenna
Messages: 25
Registered: February 2008
Junior Member
This query did not give any output.
Re: EXPDP hangs [message #616216 is a reply to message #616215] Fri, 13 June 2014 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 22801
Registered: January 2009
Senior Member
If the error continues to occur, contact Oracle Customer Support and report the error.
Re: EXPDP hangs [message #616218 is a reply to message #616188] Fri, 13 June 2014 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
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 Go to previous messageGo to next message
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

Re: EXPDP hangs [message #616338 is a reply to message #616320] Mon, 16 June 2014 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: EXPDP hangs [message #616414 is a reply to message #616320] Mon, 16 June 2014 12:48 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 437
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

Re: EXPDP hangs [message #616974 is a reply to message #616170] Mon, 23 June 2014 13:24 Go to previous message
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
Previous Topic: EXPDP getting error ORA-39002
Next Topic: special character while loading
Goto Forum:
  


Current Time: Mon Sep 22 22:13:06 CDT 2014

Total time taken to generate the page: 0.08981 seconds