1

I have been trying to import a dump file into Oracle 12g under RDS in AWS and getting below error all the time and I can't figure it out on what is happening. Any help or pointers are greatly appreciated.

Here is what I am trying to do.

1) I got a .dat dump file from customer (not sure how they have exported this dump from oracle) and my aim is to import this to AWS oracle RDS instance.

2) As per How to transfer Oracle dump file to AWS RDS instance?

I have created new EC2 instance and installed Oracle Express edition and then copied .dat file to DATA_PUMP_DIR directory (/u01/app/oracle/admin/XE/dpdump/).

3) Created database link to AWS oracle RDS instance and able to copy this to rds instance using DBMS_FILE_TRANSFER.PUT_FILE.

4) I can see the file under DATA_PUMP_DIR path in oracle rds instance.

so far so good.

5) Now, I am trying to import the dump file into RDS using below procedure

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name => null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'sample.dat', directory => 'DATA_PUMP_DIR');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

All the time I get below error 

DECLARE
*
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4087
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4338
ORA-06512: at line 5

I understand the error is pointing to the line DBMS_DATAPUMP.ADD_FILE() but I could not able to make out what is wrong here.

I have tried various options to import but none helped. I have created tablespace and also make sure user is having correct permissions.

Any pointers are greatly appreciated.

Nisarg
  • 1,631
  • 6
  • 19
  • 31
Newbee
  • 11
  • 3
  • https://stackoverflow.com/questions/48398623/error-importing-a-dmp-file-in-aws-rds-oracle-db/48404625#48404625 – Dmitry Demin Aug 16 '18 at 13:55
  • Sorry. that is not helping and in my case I have the dump file already moved to rds. As I mentioned in my post, I don't know the dump file is generated by customer. All I am trying to do is to blindly import that into rds. Is it something possible in first place? Thanks. – Newbee Aug 16 '18 at 15:16

1 Answers1

0

1)Connect to the RDS instance with the Amazon RDS master user account.

2) Run this pl/sql block

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

/

3) You can start importing from any oracle client. For example, Instance EC2. 

impdp RDS_master_user/password@rds_instance DUMPFILE=sample.dmp   DIRECTORY=DATA_PUMP_DIR parfile=import_parfile

import_parfile

SCHEMAS =SCHEMA_NAME
Dmitry Demin
  • 2,006
  • 2
  • 15
  • 18
  • Thank you Dmitry. I am getting error mention in my post when performing step (2) in your response. – Newbee Aug 17 '18 at 09:16