Export/Import Data on Amazon RDS using Oracle Datapump utility

How you import data into an Amazon RDS DB instance depends on the amount of data you have and the number and variety of database objects in your database.

For example, you can use Oracle SQL Developer to import a simple, 20 MB database. You can use Oracle Data Pump to import complex databases, or databases that are several hundred megabytes or several terabytes in size.

You can also use AWS Database Migration Service (AWS DMS) to import data into an Amazon RDS DB instance.

AWS DMS can migrate databases without downtime and, for many database engines, continue ongoing replication until you are ready to switch over to the target database.

You can migrate to Oracle from either the same database engine or a different database engine using AWS DMS. If you are migrating from a different database engine, you can use the AWS Schema Conversion Tool to migrate schema objects that are not migrated by AWS DMS. For more information about AWS DMS, see What is AWS Database Migration Service.

The following performance guidelines apply to all RDS data import/export operations:

• Load and unload data in parallel using compression and multiple threads.
• For large data loads, consider disabling automated backups by setting the backup retention for the RDS DB instance to zero; a restart of the RDS DB instance is necessary to enact this change. Disabling backups is not recommended for production instances. Disabling backups prevents point-in-time recovery functionality, deletes all previous automated snapshots for the DB instance, and prevents recovery of deleted snapshots. If the DB instance is not yet in production, disabling backups can improve load performance. This change should be immediately rolled back when the data load is complete. Also consider generating recovery points by creating manual snapshots at critical stages of the data load process.
• Consider disabling Multi-AZ during the data load operation to reduce the overhead caused by the synchronous write operations of Multi-AZ data loads. Multi-AZ functionality is a recommended best practice for all production RDS DB instances and should be enabled as soon as the data load is complete.
• If you are loading a large amount of data in parallel, ensure that the client machine has sufficient resources to avoid becoming a bottleneck during the data load process.

Datapump utilities like expdp or impdp cannot be directly used on RDS database since there is no SSH connect allowed on the host where RDS database is running.

1. The import process using Oracle Data Pump and the DBMS_FILE_TRANSFER package has the following steps:

Step 1: Grant privileges to user on the Amazon RDS target instance :

a. Use SQL Plus  to connect to the Amazon RDS target Oracle DB instance into which the data will be imported. Connect as the Amazon RDS master user.

Each Amazon RDS DB instance has an endpoint, and each endpoint has the DNS name and port number for the DB instance. To connect to your DB instance using a SQL client application, you need the DNS name and port number for your DB instance.

You can find the endpoint for a DB instance using the Amazon RDS console

Execute below command from EC2 instance to connect to Oracle RDS instance :

Eg. sqlplus username/password@//oracle.xxxxxxxxxxxxx.amazonaws.com:1521/test

b. Create the required tablespaces before you import the data.

Amazon RDS only supports Oracle Managed Files (OMF) for data files, log files and control files. When you create data files and log files, you can’t specify the physical file names.

By default, tablespaces are created with auto-extend enabled, and no maximum size. Because of these default settings, tablespaces can grow to consume all allocated storage.

The following example creates a tablespace named DEMO with a starting size of 10 GB and a maximum size of 20 GB:

SQL> create tablespace DEMO datafile size 10G autoextend on maxsize 20G;

c. If the user account into which the data will be imported does not exist, create the user account and grant the necessary permissions and roles.

For example, the following commands create a new user and grant the necessary permissions and roles to import the data into the user’s schema:

———————–create user rds_test identified by <password>;
grant create session, resource to rds_test ;
alter user rds_test quota 100M on users;
———————–
Step 2: Grant privileges to user on source database

Use SQL Plus to connect to the Oracle instance that contains the data to be imported. If necessary, create a user account and grant the necessary permissions.

Note : If the source database is an Amazon RDS instance, you can skip this step.

You will use your Amazon RDS master user account to perform the export.

The following commands create a new user and grant the necessary permissions:

———————–
create user export_user identified by <password>;
grant create session, create table, create database link to export_user;
alter user export_user quota 100M on users;
grant read, write on directory data_pump_dir to export_user;
grant select_catalog_role to export_user;
grant execute on dbms_datapump to export_user;
grant execute on dbms_file_transfer to export_user;
———————–

Step 3: Use DBMS_DATAPUMP or EXPDP to create a dump file :

In this step we have two options to create Export Dump file :

a. Export the dumpfile from EC2 database (if you already have the exported dumpfile, skip this step)

$ expdp export_user/password directory=data_pump_dir tables=<table_name> dumpfile=dumpfile.dmo logfile=logfile.log

b.Use SQL Plus or Oracle SQL Developer to connect to the source Oracle instance with an administrative user or with the user you created in Step 2.

If the source database is an Amazon RDS Oracle DB instance, connect with the Amazon RDS master user. Next, use the Oracle Data Pump utility to create a dump file.

The following script creates a dump file named sample.dmp in the DATA_PUMP_DIR directory.

———————–
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => ‘EXPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘sample.dmp’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘exp.log’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,’SCHEMA_EXPR’,’IN (”SCHEMA_1”)’);
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/
———————–

Note :Replace SCHEMA_1 with the name of your schema .

Data Pump jobs are started asynchronously. For information about monitoring a Data Pump job, see Monitoring Job Status in the Oracle documentation.

Step 4: Create a database link to the target DB instance :

Create a database link between your source instance and your target DB instance. Note that your local Oracle instance must have network connectivity to the DB instance in order to create a database link and to transfer your export dump file.

Perform this step connected with the same user account as the previous step.

The following command creates a database link named to_rds that connects to the Amazon RDS master user at the target DB instance:

———————–
SQL>create database link to_rds connect to <master_user_account> identified by <password>using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<dns or ip address of remote db>)(PORT=<listener port>))(CONNECT_DATA=(SID=<remote SID>)))’;
———————–

Step 5: Use DBMS_FILE_TRANSFER to copy the exported dump file to the target DB instance

Use DBMS_FILE_TRANSFER to copy the dump file from the source database  instance to the target DB instance.

Login to EC2 Oracle instance or on-premise Oracle instance, and copy the exported dumpfile from the local DATA_PUMP_DIR to RDS DATA_PUMP_DIR.

The following script copies a dump file named sample.dmp from the source instance to a target database link named to_rds (created in the previous step):

———————–

SQL>BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => ‘DATA_PUMP_DIR’,
source_file_name => ‘sample.dmp’,
destination_directory_object => ‘DATA_PUMP_DIR’,
destination_file_name => ‘sample_copied.dmp’,
destination_database => ‘to_rds’
);
END;
/
———————–

Step 6: Use DBMS_DATAPUMP or IMPDP to import the data file on the target DB instance:

In this step also we have two options to Import data using dump file :

a.On the EC2 Oracle server, execute the impdp command connecting to the RDS DB instance in order to import the copied dump file on RDS database:

$ impdp rds_test /password@//oracle.xxxxxxxxxxxxx.amazonaws.com:1521/test directory=data_pump_dir  dumpfile=dumpfile.dmo logfile=logfile.log

b. Or else you can use DBMS_DATAPUMP to import the schema in the DB instance. Note that additional options such as METADATA_REMAP might be required.

Connect to the DB instance with the Amazon RDS master user account to perform the import.

———————–
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 (‘rds_test”)’);
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/
———————–

Step 7: Clean up

After the data has been imported, you can delete the files you no longer want to keep. You can list the files in the DATA_PUMP_DIR using the following command:

———————–
select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) order by mtime;
———————–

The following command can be used to delete files in the DATA_PUMP_DIR that you no longer require:

———————–
exec utl_file.fremove(‘DATA_PUMP_DIR’,'<file name>’);
———————–

For example, the following command deletes the file named “test.dmp”:

———————–
exec utl_file.fremove(‘DATA_PUMP_DIR’,‘test.dmp’);
———————–

Similarly if you want perform the export on the remote RDS instance and want to pull the dumpfile from the remote RDS to local host, use the following steps:

Step 1 :

Perform the remote database export on RDS using the following command.

Connect to RDS instance using from EC2 instance using command as given below :

Eg. sqlplus username/password@//oracle.xxxxxxxxxxxxx.amazonaws.com:1521/test

———————–
grant read, write on directory data_pump_dir to username;
grant execute on dbms_datapump to username;
———————–

You can also use RDS Master user to perform EXPORT/IMPORT operation.

Then connect to your EC2 instance and then issue below EXPDP command to perform export operation.
———————–
$ expdp username/password@//oracle.xxxxxxxxxxxxx.amazonaws.com:1521/test directory=data_pump_dir tables=test dumpfile=dumpfile.dmo logfile=logfile.log
———————–

It will create the DUMP file and LOG file in RDS Oracle DATA_PUMP_DIR directory.

To view the files in RDS Oracle  DATA_PUMP_DIR , connect to RDS Oracle instance and then issue below command :

———————–
select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) order by mtime;
———————–

Step 2:  Create a database link to the target DB instance (i.e. Points to RDS Oracle from EC2 instance ) :

Create a database link between your source instance and your target DB instance. Note that your local Oracle instance must have network connectivity to the DB instance in order to create a database link and to transfer your export dump file.

Perform this step connected with the same user account as the previous step.

The following command creates a database link named from_rds that connects to the Amazon RDS master user at the target DB instance:

———————–
SQL>create database link from_rds connect to <master_user_account> identified by <password> using  ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<dns or ip address of remote db>)(PORT=<listener port>))(CONNECT_DATA=(SID=<remote SID>)))’;
———————–

Step 3 : Login to the local instance on EC2 and pull the dumpfile from the RDS’ DATA_PUMP_DIR to local DATA_PUMP_DIR

———————–
BEGIN
DBMS_FILE_TRANSFER.get_file(
source_directory_object => ‘DATA_PUMP_DIR’,
source_file_name => ‘dumpfile.dmp’,
source_database => ‘from_rds’,
destination_directory_object => ‘DATA_PUMP_DIR’,
destination_file_name => ‘dumpfile.dmp’);
END;
/