54427

How to transfer Oracle dump file to AWS RDS instance?

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?

Answer1:

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.

Answer2:

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.

Recommend

  • Error Importing a .dmp file in AWS RDS Oracle DB
  • Google Street View container inside Shiny Application
  • Apply kurtosis to a distribution in python
  • Shell variable expansion - indirection while calling a utility with env
  • Calculate the depth of subclass in the OWL ontology
  • Verbose log abbriviations meaning in SVC, scikit-learn
  • double precision error when converting to scientific notation
  • How to prevent TreeItem selection?
  • cordova.js for android phonegap application
  • Kinect API written solely in Java without native functions?
  • Can you use DataSet and DataTables in a Portable Class Library
  • Checkpointing In ALS Spark Scala
  • Heroku push rejected - Hartl's Rails 3.2 tutorial
  • What Makes These Two Array Adds Different?
  • Bigquery event streaming and table creation
  • Cannot upload to OneDrive using the new SDK
  • How do I access an unhandled exception in an MVC Error view?
  • Google Custom Search with transparent background
  • Change Inet root folder for iis 7
  • Javascript Callbacks with Object constructor
  • vba code to select only visible cells in specific column except heading
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Weird JavaScript statement, what does it mean?
  • Font Awesome Showing Box instead of Icons
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • Apache 2.4 - remove | delete | uninstall
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • How do I rollback to a specific git commit
  • Is there a mandatory requirement to switch app.yaml?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Benchmarking RAM performance - UWP and C#
  • embed rChart in Markdown
  • Is it possible to post an object from jquery to bottle.py?
  • Busy indicator not showing up in wpf window [duplicate]
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • Python/Django TangoWithDjango Models and Databases
  • To Get the radio button value in ruby on rails