Neil Chandler's DBA Blog

A resource for Database Professionals

Problem solving with DBMS_DATAPUMP

with 8 comments

Yesterday I had a problem… I noticed that one of our overnight jobs was failing. The job in question is an ad-hoc tidy-up job, ran as needed when a performance metric for a specific query indicates that we’re going to be in trouble soon.

So, what was the problem? I have the stack trace captured in the job output:

Finished shrinks at 02-APR-2012 00:12
 Creating job data pump export job LOG_EXPORT
 User:....... NEW_SCHEMA
 Tables:..... 'table-1','table-2'
 DECLARE
 *
 ERROR at line 1:
 ORA-31626: job does not exist
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356
 ORA-06512: at line 22



So, “job does not exist”! What does that mean, and what could be causing this to happen? What has changed? Let’s look at the code at line 22 and see what we’re running:

.
.
v_handle := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', 
                                 job_mode  => 'SCHEMA', 
                                 job_name  => 'LOG_EXPORT');
.
.

And that’s it. We’re initiating a DBMS_DATAPUMP job (to extract the data to an external file before manipulation.)

So, what has changed? Since the last invokation of this job, we have had a new software release, and for this product that means a whole new schema. We’ve missed something. We must have a permissions problem within the database. Fortunately, we keep the old schema in place, locked, following an upgrade so all we need to do is compare the permissions for the old and new schemas and we’re done.

select granted_role from dba_role_privs where grantee = 'OLD_SCHEMA'
minus
select granted_role from dba_role_privs where grantee = 'NEW_SCHEMA'
/

no rows selected

select privilege from dba_sys_privs where grantee = 'OLD_SCHEMA'
minus
select privilege from dba_sys_privs where grantee = 'NEW_SCHEMA'
/

no rows selected

select table_name,privilege from dba_tab_privs where grantee = 'OLD_SCHEMA'
minus
select table_name,privilege from dba_tab_privs where grantee = 'NEW_SCHEMA'
/

TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
DATA_PUMP_DIR                  READ
DATA_PUMP_DIR                  WRITE



Ah ha! There’s the difference! And it is definitely data pump related, so let’s run the grants into the database and run the job again. This will fix it.

grant READ  on DIRECTORY data_pump_dir to NEW_SCHEMA;
grant WRITE on DIRECTORY data_pump_dir to NEW_SCHEMA;

... and run the job again...

 DECLARE
 *
 ERROR at line 1:
 ORA-31626: job does not exist
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356
 ORA-06512: at line 22

What! Oh nuts. Why didn’t that work? [because you have not assigned a directory to DBMS_DATAPUMP yet! It'll be useful later though... Now stop jumping to conclusions! ed.] What are we going to do now? We know the permissions match. I’ve checked the O/S and there’s space in the filesystem. DBMS_DATAPUMP is not giving me enough information; what can I do to diagnose this problem further? I know! Why don’t I use datapump externally and see if that makes a difference…

expdp NEW_SCHEMA/..pwd..@..DB.. 
 directory=data_pump_dir 
 dumpfile=neilchandler.dmp 
 job_name=neilchandler

Export: Release 10.2.0.3.0 - 64bit Production on Tuesday, 03 April, 2012 12:54:27
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31626: job does not exist
ORA-31633: unable to create master table "NEW_SCHEMA.NEILCHANDLER"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'



Oh heck, we can’t write the parameter table as we have been denied a space quota on the default tablespace. D’oh! How painful was that to locate?

alter user NEW_SCHEMA quota unlimited on USERS;



And it all worked.

My question is, why couldn’t DBMS_DATAPUMP tell me that?

About these ads

Written by Neil Chandler

4th April 2012 at 12:41

8 Responses

Subscribe to comments with RSS.

  1. The API doesn’t “behave” in the same manner as the command-line tool. Not nice !

    Hemant K Chitale

    4th April 2012 at 14:30

    • I know. I tried running the job in several different ways but stil could not get it to offer up any additional error messages. I’m just glad the expdp told me what was going on. :-)

      Neil Chandler

      5th April 2012 at 04:42

  2. Hi,

    Interesting , Can you share what performance metric is used to proactively find the issue and avoid it.

    Thanks
    GP Singh

    G.P Singh

    4th April 2012 at 14:34

    • Absolutely! Time. There is a direct correlation between the elapsed time a specific piece of SQL takes to execute and the size of the table. Once the SQL runtime exceeds a threshold, we archive the data. If we do not, other related jobs will exceed their SLA’s.

      Regards

      Neil

      Neil Chandler

      4th April 2012 at 16:31

      • Oh Ok Thanks.

        WE monitor DB time , and if that exceeds threshold , then we check if the plan has changed and get the current and previous history.

        Thanks
        GP Singh

        G.P Singh

        4th April 2012 at 17:19

      • In this case, in this particualr system, the plans are consistent as the stats are locked down. The plans don’t change (not even with statistical decay outside of the upper and lower stats boundaries. We don’t have histograms.) The performance of this aspect of the system is purely a factor of the size of the table, which we control as needed.

        Neil

        Neil Chandler

        5th April 2012 at 04:47

  3. Just out of interest, was anything reported in the alert log?

    Boneist

    5th April 2012 at 08:59

    • Just checked, and no, nothing in the alert log. It was a user-based error so I wouldn’t necessarily expect the alert log to get populated. I just wish the stack trace for the DBMS_DATAPUMP API was as informative as the command line interface (for the record, this problem was on an Oracle 10.2 database running on an AIX HACMP cluster)

      regs

      Neil

      Neil Chandler

      5th April 2012 at 10:32


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: