DBMS_DataPump.OPEN fails in Oracle 18c error:

User_TK218

Recently I am upgrading from Oracle 11g to 18c.

So everything was working fine in 11g but in 18c I am getting an error

ORA 31626: job does not exist

So the work flow is I have a scheduler job named CREATE_BACKUP which runs every week. It contains all the code to create the backup

To create DB backup we use DBMS_DataPump API and initially doing

DBMS_DataPump.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'J$AIM_BACKUP', VERSION => 'LATEST');

Once again everything is working fine in Oracle 11g but when I run in 18c I am getting an error

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137

ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285

ORA-06512: at line 14

 - ORA-31626: job does not exist

I am already granting EXP_FULL_DATABASE and DATAPUMP_EXP_FULL_DATABASE to the corresponding user while database installation.

Do you think, there might be something I can check what's the specific reason for the error? Or anything I missed or any suggestions?

GregV

Hi,

Does it fail exactly at the DBMS_DATAPUMP.open line or you're getting the error from an exception block?

Usually the "job does not exist" error is a consequence of terminating a job that hasn't been created due to another error. Can you post the code?

User_H3J7U

Datapump creates the table based on job name. Table format may deffers accross databse versions.

User_TK218

@GregV This is how the code looks the code doesn't reach the line DBMS_OUTPUT.put_line('Job created') it fails on open and moves to exception

nFlashbackSCN := timestamp_to_scn(SYSDATE);

DBMS_OUTPUT.put_line('Starting Backup process');

hdpBackupJob := DBMS_DataPump.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'J$AIM_BACKUP', VERSION => 'LATEST');

DBMS_OUTPUT.put_line('Job created');

DBMS_DataPump.add_file(handle  => hdpBackupJob,

 filename => sDumpFileName,

 DIRECTORY => sBackupDirectory,

 filesize => sFileSize,

 reusefile => nReuseFile);

DBMS_DataPump.add_file(handle  => hdpBackupJob,

 filename => sLogFileName,

 DIRECTORY => sBackupDirectory,

 filetype => DBMS_DataPump.KU$_FILE_TYPE_LOG_FILE,

 reusefile => nReuseFile);

-- consistent export

DBMS_DataPump.set_parameter(handle => hdpBackupJob, NAME => 'FLASHBACK_SCN', VALUE => nFlashbackSCN);

IF sJobMode = 'SCHEMA'

AND sSchemaName IS NOT NULL

THEN

DBMS_DataPump.metadata_filter(handle => hdpBackupJob, NAME => 'SCHEMA_EXPR', VALUE => 'IN (' || sSchemaName || ')');

END IF;

So this is how the code looks and

GregV

Can you show the exception's handler code as well?

User_TK218

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line ( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);

AIM_DBS.P$Rollback;

BEGIN

DBMS_DataPump.stop_job(handle => hdpBackupJob, IMMEDIATE => 1, keep_master => 0, DELAY => 0);

AIM_DBS.P$Commit;

END;

GregV

Thanks. That's what I was trying to tell you. Here this is the stop_job instruction that produces the error, because the job doesn't exist in fact. SO to get the real error, comment out the DBMS_DataPump.stop_job line.

User_TK218

@GregV Sorry for the delay I tried commenting that line mentioning

DBMS_DataPump.stop_job(handle => hdpBackupJob, IMMEDIATE => 1, keep_master => 0, DELAY => 0);

and still getting the same error

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_DATAPUMP", line 1852

ORA-06512: at "SYS.DBMS_DATAPUMP", line 6833

ORA-06512: at "AIMIM.AIM_BACKUP", line 1034

 - ORA-31626: job does not exist

Now the difference from the past is the line

ORA-06512: at "AIMIM.AIM_BACKUP", line 1034

exactly points to the open function.

Solomon Yakobson

Error is very misleading. DBMS_DATAPUMP.OPEN creates master table. And ORA-31626: job does not exist is raised when Oracle can't create master table:

SQL> revoke create table from u1;

Revoke succeeded.

SQL> connect [email protected]

Enter password:

Connected.

SQL> exec DBMS_OUTPUT.PUT_LINE(DBMS_DataPump.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'J$AIM_BACKUP', VERSION => 'LATEST'));

BEGIN DBMS_OUTPUT.PUT_LINE(DBMS_DataPump.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'J$AIM_BACKUP', VERSION => 'LATEST')); END;

*

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 1852

ORA-06512: at "SYS.DBMS_DATAPUMP", line 6833

ORA-06512: at line 1

SQL> connect [email protected]

Enter password:

Connected.

SQL> grant create table to u1;

Grant succeeded.

SQL> connect [email protected]

Enter password:

Connected.

SQL> exec DBMS_OUTPUT.PUT_LINE(DBMS_DataPump.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'J$AIM_BACKUP', VERSION => 'LATEST'));

PL/SQL procedure successfully completed.

SQL>

SY,

User_TK218

@Solomon Yakobson Yeah it worked for some time as you described in sqlplus mode in command prompt for later it also stopped working in command prompt itself. Also, whenever I try to run the procedure including this code as the same user from the SQL developer it is showing the same error for me. Is there something related to the pluggable database? Or because I am running the procedure in the scheduled job?

Solomon Yakobson

What is stream pool size?

SY.