How to import on-premises ORACLE data to ORACLE RDS, few simple steps!!

March 4, 2015 by Masudur Rahaman Sayem

Importing data to ORACLE RDS will be a complex job if you are doing it for the first time. You can follow the below simple steps to do that.

Create an ORACLE RDS DB instance from AWS management console
Go to your source database and create a DBlink as below
create database link UR_DB_LINK_NAME connect to UR_RDS_USER_ID identified by UR_RDS_PASSWORD
using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=UR_IP_RDS_IP_OR_DNS)(PORT=1521))(CONNECT_DATA=(SID=UR_RDS_SID)))’;
Connect to your local ORACLE DB using Oracle SQL Developer
Click on View menu –> DBA to view the DBA portion to use data pump
Connect with your source DB with a DBA user
Expand the data connection –> right click on Export Job and Click on Data pump export wizard
Select the Tablespaces you want to export and export that to a .dmp file
Transfer the .dmp file to your RDS by using the below script. Run this script from your source DB’s SQL window

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => ‘UR_SOURCE_DIRECTORY‘,
source_file_name => ‘UR_EXPORT_FILE_NAME.dmp’,
destination_directory_object => ‘DATA_PUMP_DIR’,
destination_file_name => ‘UR_IMPORT_FILE_NAME.dmp’,
destination_database => ‘UR_DB_LINK_NAME‘
);
END;
/

It will take some time to export this to RDS – based on your file size and network speed
After successful transfer, connect with the RDS from Oracle SQL developer
Add the DBA connection with RDS to initiate data pump wizard
A tricky part – create tablespace in your RDS with the same name as it was before. It will help you to automatically export those schema, otherwise you will need some manual work map exported tables to you existing RDS tablespace. Use the below script to create those tablespaces: