Question:

I have an existing .dmp file on EC2 instance which has access to RDS instance running Oracle 11g on AWS.

I have read <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html" rel="nofollow">Importing Data in AWS RDS</a>, it seems that AWS does not support this kind of direct transfer.

It does require to have a Source Oracle DB from where you have to create/export a .dmp file which you can then transfer to destination RDS instance by establishing a db link.

My question is, is there a way I can transfer/import my existing .dmp file to the DATA_DUMP_DIR on RDS Instance?

Any suggestions?

File access for the RDS instance is forbidden. Access to the DATA_PUMP_DIR directory only through the db_link and use DBMS_FILE_TRANSFER package.

<ul><li>Option 1</li> </ul>

You can do the export of data using the old exp utility on the EC2 instance, this utility also creates export files .dmp, but for a different format. The format is not compatible with impdp expdp. The exp imp utility can connect over the SQL*NET network to the target database as client-server. This utility is obsolete and has less performance. The dmp file is not created on the server, as when running the utility expdp. The dmp file is written on the side where the utility exp is run (server or client)

$ORACLE_HOME/bin/exp parfile=parfile_exp_full FILE=export.dmp LOG=export.log

And then do the data import using the imp to RDS instance.

$ORACLE_HOME/bin/imp parfile=parfile_imp_full FILE=export.dmp LOG=import.log <ul><li>Option 2</li> </ul>

You can export the data to an CSV file using the utility $ORACLE_HOME/bin/sqlplus -s user/pass@ec2 @csv2.sql.

set heading off set termout OFF SET FEEDBACK OFF SET TAB OFF set pause off set verify off SET UNDERLINE OFF set trimspool on set echo off set linesize 1000 set pagesize 0 set wrap off spool test2.csv select code||','||name||','||code_rail from alexs.all_station; spool off exit;

And then make the data import to RDS instance using the utility sqlldr.

Eventually, I had to spin up another AWS Instance, Install Oracle XE on it, then place my dump file in the DATA_PUMP_DIR & then follow the AWS RDS Data Import guide.

It's pretty annoying that there is no other way to do this. And having no SSH access to the RDS instace just adds up to that! Also, the AWS Doc is not clear about the particulars.