Migration and Upgrade from EC2 to RDS Using DBMS_FILE_TRANSFER

Performing a simple manual migration and upgrade from EC2 to RDS without using S3 Object Storage:

Here I will give a brief example of how to import data from Oracle Database on-premises or VM in EC2 into an RDS instance using the Oracle database package DBMS_FILE_TRANSFER instead of an Amazon S3 storage object. I will also show how  this approach could be used for simple migrations and upgrades. In this particular article, the method was used to export a large schema from 11g and import it into 19c.

Overview

EC2: Amazon Elastic Compute Cloud is a part of Amazon’s cloud-computing platform, Amazon Web Services, which allows users to rent virtual computers to run their own computer applications.

RDS: Amazon Relational Database Service is a managed database service from Amazon Web Services. It is a cloud web service designed to simplify the setup, operation and scaling of a relational database for use in applications.

S3: Amazon Simple Storage is a service offered by Amazon Web Services that provides object storage through a web service interface.

DBMS_FILE_TRANSFER: The package provides procedures to copy a binary file within a database or to transfer a binary file between databases. The destination database converts each block when it receives a file from a platform with different endianness.

For this example, the source database is an Oracle 11g version running into a Windows EC2 virtual machine, and the target is an Oracle RDS instance version 19c with a single schema owning the objects. (You would take the same approach if there were several schemas owning objects; you just would need to include all of them in the export and import operations.) The target jumpbox to connect to RDS was a Linux VM.

I’m not discussing several important details to consider as part of the migration and upgrade—like the character set, database size, network configuration, exact versions and others—because the main intention is only to show how useful the package DBMS_FILE_TRANSFER is in these kinds of projects. I would like to mention that for small schemas, using impdp with network_link option would be a suitable approach.

Assume that the source and destination can communicate with each other.

On the Source EC2:

1) Perform an export with datapump on the source:

  C:\>expdp '/ as sysdba' DIRECTORY=DATA_PUMP_DIR SCHEMAS=AppSchema dumpfile=ORCL_AppSchema.dmp compress=y logfile=ORCL_AppSchema.log
  *
  *
  *
  Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: C:\APP\USER\ADMIN\ORCL\DPDUMP\ORCL_AppSchema.DMP
  Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully complete

On the Target RDS:

2) Connect to your RDS instance using sqlplus and create a Database Link. Ensure you can pull data from source into target and that tnsnames entries are properly set already and communication is allowed. Remember to change key entries in <> with the proper values for your environment:

  SQL> CREATE DATABASE LINK DataFrom_EC2
  CONNECT TO  IDENTIFIED BY <********>
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=<1527>))(CONNECT_DATA=(SID=)))';

3) Check if there are any existing dump files. Identify if older .dmp files that should be deleted before adding a new export dump file:

  SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) ORDER BY MTIME;

4) Still from SQLPLUS, proceed to transfer the dump file taken on source to target using DBLink and the package DBMS_FILE_TRANSFER, then confirm with the above query once the file transfer completes:

  SQL>
  BEGIN
    DBMS_FILE_TRANSFER.GET_FILE(
      source_directory_object       => 'DATA_PUMP_DIR',
      source_file_name              => 'ORCL_AppSchema.dmp',
      source_database              => 'DataFrom_EC2',
      destination_directory_object  => 'DATA_PUMP_DIR',
      destination_file_name         => 'ORCL_AppSchema.dmp');
  END;
  /

SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(‘DATA_PUMP_DIR’)) ORDER BY MTIME;

5) With the file copied, you can proceed to import—in this case, you can run the import command from the machine where you run sqlplus to connect to the RDS instance. It will read the file from the location into RDS where it has the .dmp allocated and will proceed with the import. Note that the log file will be created there into the RDS as well, not in the machine where you run the command:

  $ impdp MasterAdminUser@DBTarget_RDS DIRECTORY=DATA_PUMP_DIR dumpfile=ORCL_AppSchema.dmp  logfile=imp_ORCL_AppSchema.log

6) Go back to the SQLPLUS and you will see the .dmp file and now the .log file there. If needed, you can delete them using following command:

  SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) ORDER BY MTIME;
  SQL> EXEC UTL_FILE.FREMOVE('DATA_PUMP_DIR','ORCL_AppSchema.dmp');
  SQL> EXEC UTL_FILE.FREMOVE('DATA_PUMP_DIR','imp_ORCL_AppSchema.log');

The target database is now ready for your validation and confirmation that everything is functional as expected.

Step 4 is what motivated me to write a blog about this package and method. I found it amazing that from SQLPLUS this package can read from the OS directory in the source server and transfer the file into an RDS instance via network using a database link. As you may know, in RDS you don’t have access to the underlying OS and you can’t set the data pump directory to read from the machine where you are connecting to the RDS; the only way to perform an export and import operation is by using a storage object or by using this nice package, DBMS_FILE_TRANSFER. Both cases must be combined with the Oracle Data Pump utility.

Thank you for reading my post.