DevOps, CI/CD and Automation

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?

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

@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

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

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;

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.

@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.

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 u1@pdb1sol122

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 scott@pdb1sol122

Enter password:

Connected.

SQL> grant create table to u1;

Grant succeeded.

SQL> connect u1@pdb1sol122

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,

@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?

What is stream pool size?
SY.

@solomon-yakobson I checked the streams_pool_Size using the sqlplus
SQL> show parameter STREAMS_POOL_SIZE;
NAME TYPE VALUE
------------------------------------ -----------
streams_pool_size big integer 0

And what about SGA_TARGET? Most likely is also set to zero. If so, you need to set streams pool size to say 40M.
SY.

@solomon-yakobson Yes, As you said SGA_TARGET is also zero(attached below). I will try to set STREAMS_POOL_SIZE=40M and will let you know.
NAME TYPE VALUE
---------- ----------- -----
sga_target big integer 0

@solomon-yakobson Sorry for the late reply altering the streams_pool_size parametet, it didn't work for me either. I also granted privileges like
grant dba to AIMIM;
grant all privileges to AIMIM;
grant sysdba to AIMIM;
grant sysbackup to AIMIM;
commit;
But none of them helped me in creating the job.

Double-check streams pool size:

SELECT VALUE FROM GV$PARAMETER WHERE NAME = 'streams_pool_size'
/

I assume

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

is in stored procedure, so keep in mind roles are ignored (unless stored procedure is AUTHID CURRENT_USER). Therefore double-check stored procedure owner (not caller) is directly (not via role) granted CREATE TABLE.
Double-check stored procedure owner (not caller) has quota on its default tablespace.
Restart database. It could be background process QMNC is dead for some reason (e.g. cancelled shutdown).
SY.