datapump import into OCI autonomous db shared infrastructure

Question

on attempting to import a schema from OCI object storage using dbms_datapump

DECLARE
    -- replace with your values
    exported_schema VARCHAR2(64)  := 'DEMO';
    import_schema   VARCHAR2(64)  := 'DEMO'; -- in case you want to remap schema
    data_pump_dir   VARCHAR2(64)  := 'DATA_PUMP_DIR';
    dump_file_name  VARCHAR2(256) := <object storage url>;
    credential_name VARCHAR2(64)  := 'OBJECT_STORE_CRED';
    parallel        NUMBER        := 4;
 
    job_handle      NUMBER;
    job_name        VARCHAR2(64);
    job_status      VARCHAR2(128);
    output_message  VARCHAR2(1024);
    l_sts  KU$_STATUS;
    v_logs ku$_LogEntry;
    v_row  PLS_INTEGER;
BEGIN
    job_name := dbms_scheduler.generate_job_name('import_');
    job_handle := dbms_datapump.open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => job_name); 
    dbms_datapump.add_file(handle => job_handle, filename => dump_file_name, directory => credential_name, filetype => dbms_datapump.ku$_file_type_uridump_file); 
    dbms_datapump.add_file(handle => job_handle, filename => import_schema || '_import.log', directory => data_pump_dir, filetype => 3);
    --dbms_datapump.metadata_remap(job_handle, 'REMAP_SCHEMA', exported_schema, import_schema);
    dbms_datapump.metadata_filter(handle => job_handle, name => 'SCHEMA_EXPR', value => 'IN(''' || exported_schema || ''')');
    dbms_datapump.set_parallel(handle => job_handle, degree => parallel);
    dbms_datapump.start_job(handle => job_handle, skip_current => 0, abort_step => 0); 
    dbms_datapump.wait_for_job(handle => job_handle, job_state => job_status);
    output_message := 'Data Pump Import Execution: ''' || job_status || '''';
    dbms_output.put_line(output_message);
    EXCEPTION
  WHEN OTHERS THEN
    dbms_datapump.get_status(NULL, 8, 0, job_status, l_sts);
    v_logs := l_sts.error;
 
    v_row := v_logs.FIRST;
    LOOP
      EXIT WHEN v_row IS NULL;
      dbms_output.put_line('logLineNumber=' || v_logs(v_row).logLineNumber);
      dbms_output.put_line('errorNumber=' || v_logs(v_row).errorNumber);
      dbms_output.put_line('LogText=' || v_logs(v_row).LogText);
      v_row := v_logs.NEXT(v_row);
    END LOOP;
    RAISE;
END;
/

the following error occurrs

ORA-31623: um job não está associado a esta sessão por meio do handle especificado
ORA-06512: em "SYS.DBMS_DATAPUMP", line 4769
ORA-06512: em "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: em "SYS.DBMS_DATAPUMP", line 4515
ORA-06512: em "SYS.DBMS_DATAPUMP", line 6149
ORA-06512: em line 36
ORA-39001: valor de argumento inválido
ORA-06512: em "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: em "SYS.DBMS_DATAPUMP", line 4951
ORA-06512: em "SYS.DBMS_DATAPUMP", line 5202
ORA-06512: em line 20
Error at Line: 7 Column: 0

I used this program before to import schema using datapump file, but it is no longer working.

I also tried using oracle client 19.17 from my local machine with the following command

impdp admin/<password>@serviceName credential=OBJECT_STORE_CRED directory=data_pump_dir dumpfile=<object storage url> parallel=2 encryption_pwd_prompt=yes exclude=cluster,indextype,db_link

and got the following error

Import: Release 21.0.0.0.0 - Production on Sex Jan 6 16:49:53 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

UDI-06550: operaþÒo gerou erro ORACLE 6550
ORA-06550: linha 1, coluna 72:
PL/SQL: ORA-00942: a tabela ou view nÒo existe
ORA-06550: linha 1, coluna 42:
PL/SQL: SQL Statement ignored

Any suggestion about what is going wrong? Is there another method to import into autonomous database?

Answer 1

My read of this is that the error in the first case is saying that the parameter for this call (line 20 of your program) is not valid:

dbms_datapump.add_file(handle => job_handle, filename => dump_file_name, directory => credential_name, filetype => dbms_datapump.ku$_file_type_uridump_file);

When this happens, it could be the dumpfile name or the credential that is not valid for this operation. There can be other causes for the UDI error, as detailed in "How to resolve the Data Pump error ORA-31623 UDE-31623 (a job is not attached to this session via the specified handle) ? (Doc ID 1907256.1)" (https://support.oracle.com/epmos/faces/DocContentDisplay?id=1907256.1) but with the combination of the secondary error, and of the issue with the command-line version of the operation as well, I think it likely that the URI for the dumpfile and/or the credential being specified are not valid.

I'm not as sure about the second case, though, because you mention using the 19.7 client. The banned shows the 21.3 client being used, connecting to the 19c server. While the 21c client was made backward compatible, I would want to see the latest client in use to be sure it isn't a bug that has already been fixed. The latest instant client is version 21.8.