1

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

  • Oracle docs say that VERSION=COMPATIBLE is the default value on expdp or I misunderstood ? – sgargel Jun 18 '18 at 12:20
  • It's what someone with plenty of experience expdp'ing an impdp'ing told me. But the manual agrees with you. So, you should use version=>'12' when exporting: "Specify a value of 12 to allow all existing database features, components, and options to be exported from Oracle Database 11g release 2 (11.2.0.3) or later into an Oracle Database 12 c Release 1 (12.1)" cfr docs.oracle.com/database/121/ARPLS/d_datpmp.htm#ARPLS66053 – Gerard H. Pille Jun 18 '18 at 13:14
  • This is intresting: Export: Release 12.1.0.2.0 - Production on Lun Giu 18 15:19:49 2018 UDE-00018: Client Data Pump incompatibile con la versione di database 11.2.0.1.0 and doing the same on the expdp11 Export: Release 11.2.0.1.0 - Production on Mon Jun 18 13:19:14 2018 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39023: Version 12 is not supported. – sgargel Jun 18 '18 at 13:20
  • Oracle's manuals used to be of very good quality. But I see now: 11.2.0.3 in the manual. – Gerard H. Pille Jun 18 '18 at 13:23
  • I saw... i think 11.2.0. 1 is the problem. – sgargel Jun 18 '18 at 13:36
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.