Forum Stats

  • 3,768,190 Users
  • 2,252,758 Discussions
  • 7,874,486 Comments

Discussions

Categories

DBMS_DataPump.OPEN fails in Oracle 18c error:

User_TK218
User_TK218 Member Posts: 14 Green Ribbon
in SQL & PL/SQL

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?

Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • GregV
    GregV Member Posts: 3,068 Gold Crown

    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?

    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy

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

    · Share on TwitterShare on Facebook
  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon

    @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

    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,068 Gold Crown

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

    · Share on TwitterShare on Facebook
  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon

    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;

    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,068 Gold Crown

    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.

    · Share on TwitterShare on Facebook
  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon

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

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond

    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
    · Share on TwitterShare on Facebook
  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon
    edited Sep 20, 2021 9:18AM

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

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond

    What is stream pool size?

    SY.

    · Share on TwitterShare on Facebook