DBMS_DATAPUMP error: The current API cannot be executed because of inconsistencies between the API and the current definition of the job

Question

I'm trying to do an impdp on a Amazon RDS Oracle instance using DBMS_DATAPUMP.

I'm following the steps described in the Amazon RDS guide (Importing Data into Oracle on Amazon RDS).

I've already copied successfully the dmp to the RDS instances.

This is my impdp job executed with awssys user:

-- IMPORT DATAPUMP
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => '20180618_my_copied.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''MYSCHEMA'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/      

I can't understand the meaning of the error displayed:

ORA-39002: operazione non valida
ORA-06512: a "SYS.DBMS_DATAPUMP", line 6224
ORA-06512: a line 7
39002. 00000 -  "invalid operation"
*Cause:    The current API cannot be executed because of inconsistencies
           between the API and the current definition of the job.
           Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
           will further describe the error.
*Action:   Modify the API call to be consistent with the current job or
           redefine the job in a manner that will support the specified API.

This is the version of the source DB:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

This is the version of Amazon RDS instance:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

EDIT: I've added version=COMPATIBLE to both the expdp command and DBMS_DATAPUMP options but error remains.

expdp system@source_db version=COMPATIBLE dumpfile=20180618_my.dmp logfile=20180618_my.log directory=DATA_PUMP_DIR CONSISTENT=Y  SCHEMAS=MYSCHEMA,MYSCHEMA2,MYSCHEMA3,MYSCHEMA4

Answer 1

Your versions differ between source and destination.

hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null, version=>'COMPATIBLE');

Oh, the bad news: you need this for the expdp too.

Answer 2

After many tries I found this way:

1. Running expdp with version=11.2 parameter set.
2. Copy with DBMS_FILE_TRANSFER.PUT_FILE the dmp to the RDS Instance
3. Running impdp with version=11.2 parameter set from the 11.2 machine.

This causes:

Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-39068: invalid master table data in row with PROCESS_ORDER=-1
ORA-01403: no data found

Following ORACLE support IMPDP Raises ORA-39068: Invalid Master Table Data In Row With PROCESS_ORDER=-1 And ORA-1403 (Doc ID 1556555.1) I successfully deleted the DUAL in excess.

SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name = 'DUAL'
AND OBJECT_TYPE = 'TABLE';

OWNER      OBJECT_NAME OBJECT_TYPE STATUS
---------- ----------- ----------- -------------
SYS        DUAL        TABLE       VALID
AWSSYS     DUAL        TABLE       VALID

DROP TABLE AWSSYS.DUAL;

By repeating step 3 impdp now works without problems.