Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘ORA-31626: job does not exist’

expdp throwing ORA-04031 (stream pool) error

Posted by Pavan DBA on December 4, 2013


A week back I have faced an error related to datapump export and would like to present my findings here.

When I executed datapump export (expdp) job on a 10.2.0.4 database, it got failed with below error message

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYS
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 600
ORA-39080: failed to create queues “KUPC$C_1_20070823095248” and “KUPC$S_1_20070823095248” for Data Pump job ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 1580
ORA-04031: unable to allocate 4194344 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”fixed allocation callback

when we look at last line, like me, many people wonder why it is showing stream pool here and what is the relation between stream pool and expdp?

Here is the background…

Datapump jobs (either expdp/impdp), when initiated, will use buffer queues in order to transfer the data directly from the master table to the dump file. Instead of creating new buffer queue, datapump operations will try to use the existing queues in stream pool memory area.

Normally, Stream pool will have buffer queues in order to store messages in memory that supports capture processes, apply processes, XStream outbound servers, and XStream inbound servers.

Ideally, Stream pool size will be managed (increase/decrease) automatically when ASMM or AMM is enabled. But, sometimes with this automatic memory management, it might happen that STREAM_POOL_SIZE has not been set and pointing to a size of 0. This is the cause for the error mentioned above.

Solution:

Set STREAM_POOL_SIZE parameter to a value greater than 0. Usually 48m should be the min size and this value will be more if there is a large database or a database with high work load.

So, friends, beware of Stream pool when doing expdp/impdp 🙂

 

Posted in export/import/Datapump | Tagged: , , , , , , , , , | 3 Comments »

How to resolve UDE-00008 and ORA-31626 during expdp?

Posted by Pavan DBA on April 23, 2012


Friends, want to share some interesting bug that I faced in datapump concept.

Recently when I am taking export backup of a database using expdp with full=y option, I got below error
UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2772
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3886
ORA-06512: at line 1
I thought there is some issue and searched over “My Oracle Support” and found it is a bug 5969934 that can occur from any version starting from 10.2.0.2 till 11.2.0.3

Reason for the same is as follows

The expdp client makes calls to DBMS_DATAPUMP package to start and monitor export job. Once the export job is underway, the client just monitors the job status by issuing DBMS_DATAPUMP.GET_STAUS. Therefore, if the export logfile says “job successfully completed”, the dump file generated by the job should be fine.

You can simply ignore the errors, since the dump file is still valid for an import.

In the 10.2.0.2 release, there were a number of problems that caused the expdp and impdp clients to exit prematurely, interpreting a nonfatal error as a fatal one, giving the appearance that the job had failed when it hadn’t. In fact, inspection of the log file, if one was specified for the job, showed that the job ran successfully to completion. Often a trace file written by one of the Data Pump processes would provide more detail on the error that had been misinterpreted as a fatal one. Many of these errors involved the queues used for communication between the Data Pump processes, but there were other issues as well.
.
With each subsequent release, these problems have been addressed, and the client has become more robust and rarely, if ever, runs into situations like this. However, this is the result of many bug fixes in subsequent releases, some in Data Pump and some in supporting layers. It’s impossible to know, at this point, what combination of bug fixes would address this specific failure, and even if that was possible, it wouldn’t address other possible failures that look very similar on the client side.
.
Relying on information in the log file is one way to verify that the job actually completed successfully. Problems like this one became much more intermittent by the 10.2.0.4 release and are rarely, if ever, seen in 11.1 or later.
So, as per the above comments, I checked my logfile and found that job is successful (below is output of last lines in logfile)
Master table “SYS”.”SYS_EXPORT_FULL_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /dbexports/fullexp_21apr2012_01.dmp
  /dbexports/fullexp_21apr2012_02.dmp
  /dbexports/fullexp_21apr2012_03.dmp
  /dbexports/fullexp_21apr2012_04.dmp
  /dbexports/fullexp_21apr2012_05.dmp
  /dbexports/fullexp_21apr2012_06.dmp
  /dbexports/fullexp_21apr2012_07.dmp
  /dbexports/fullexp_21apr2012_08.dmp
Job “SYS”.”SYS_EXPORT_FULL_01″ successfully completed at 18:23:21

 

Any time if you face this error, please don’t panic now and check your logfile immediately 🙂

Hope this helps and HAPPY LEARNING !!!

Posted in export/import/Datapump | Tagged: , , , , , , , , | 6 Comments »

 
%d bloggers like this: