Summary Prerequisites and limitations Architecture Tools Epics Related resources

Migrate an on-premises Oracle database to Amazon RDS for Oracle using Oracle Data Pump

Created by Mohan Annam (AWS) and Brian motzer (AWS)

Environment: PoC or pilot	Source: Databases: Relational	Target: Amazon RDS for Oracle
R Type: Replatform	Workload: Oracle	Technologies: Migration; Databases
AWS services: Amazon RDS

Summary

This pattern describes how to migrate an Oracle database from an on-premises data center to an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance by using Oracle Data Pump.

The pattern involves creating a data dump file from the source database, storing the file in an Amazon Simple Storage Service (Amazon S3) bucket, and then restoring the data to an Amazon RDS for Oracle DB instance. This pattern is useful when you encounter limitations using AWS Database Migration Service (AWS DMS) for the migration.

Prerequisites and limitations

Prerequisites

An active AWS account
The required permissions to create roles in AWS Identity and Access Management (IAM) and for an Amazon S3 multipart upload
The required permissions to export data from the source database
AWS Command Line Interface (AWS CLI) installed and configured

Product versions

Oracle Data Pump is available only for Oracle Database 10g Release 1 (10.1) and later versions.

Architecture

Source technology stack

On-premises Oracle databases

Target technology stack

Amazon RDS for Oracle
SQL client (Oracle SQL Developer)
An S3 bucket

Source and target architecture

Amazon S3 multipart upload from an on-premises Oracle DB to Amazon RDS by using Oracle Data Pump.

Tools

AWS services

AWS Identity and Access Management (IAM) helps you securely manage access to your AWS resources by controlling who is authenticated and authorized to use them. In this pattern, IAM is used to create the roles and policies necessary for migrating data from Amazon S3 to Amazon RDS for Oracle.
Amazon Relational Database Service (Amazon RDS) for Oracle helps you set up, operate, and scale an Oracle relational database in the AWS Cloud.
Amazon Simple Storage Service (Amazon S3) is a cloud-based object storage service that helps you store, protect, and retrieve any amount of data.

Other tools

Oracle Data Pump helps you move data and metadata from one database to another at high speeds. In this pattern, Oracle Data Pump is used to export the data dump (.dmp) file to the Oracle server, and to import it into Amazon RDS for Oracle. For more information, see Importing data into Oracle on Amazon RDS in the Amazon RDS documentation.
Oracle SQL Developer is an integrated development environment that simplifies the development and management of Oracle databases in both traditional and cloud-based deployments. It interacts with both the on-premises Oracle database and Amazon RDS for Oracle to run the SQL commands required for exporting and importing data.

Epics

Task	Description	Skills required
Create the bucket.	To create the S3 bucket, follow the instructions in the AWS documentation.	AWS systems administrator

Task	Description	Skills required
Configure IAM permissions.	To configure permissions, follow the instructions in the AWS documentation.	AWS systems administrator

Task	Description	Skills required
Create the target Amazon RDS for Oracle DB instance.	To create the Amazon RDS for Oracle instance, follow the instructions in the AWS documentation.	AWS systems administrator
Associate the role with the DB instance.	To associate the role with the instance, follow the instructions in the AWS documentation.	DBA

Task Description Skills required

Task	Description	Skills required
Create the user.	Connect to the target Amazon RDS for Oracle database from Oracle SQL Developer or SQL*Plus, and run the following SQL command to create the user to import the schema into. `create user SAMPLE_SCHEMA identified by <PASSWORD>; grant create session, resource to <USER NAME>; alter user <USER NAME> quota 100M on users;`	DBA

Create the user.

Connect to the target Amazon RDS for Oracle database from Oracle SQL Developer or SQL*Plus, and run the following SQL command to create the user to import the schema into.


create user SAMPLE_SCHEMA identified by <PASSWORD>;
grant create session, resource to <USER NAME>;
alter user <USER NAME> quota 100M on users;

DBA

Task Description Skills required

Task	Description	Skills required
Create a data dump file.	To create a dump file named `sample.dmp` in the `DATA_PUMP_DIR` directory for exporting the `SAMPLE_SCHEMA` user, use the following script. 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 => 'export.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); dbms_datapump.metadata_filter(hdnl, 'SCHEMA_EXPR', 'IN (''SAMPLE_SCHEMA'')'); dbms_datapump.start_job(hdnl); END; / Review the export details by reviewing the `export.log` file in your local `DATA_PUMP_DIR` directory.	DBA

Create a data dump file.

To create a dump file named sample.dmp in the DATA_PUMP_DIR directory for exporting the SAMPLE_SCHEMA user, use the following script.


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 => 'export.log', 
                            directory => 'DATA_PUMP_DIR', 
                            filetype => dbms_datapump.ku$_file_type_log_file);

    dbms_datapump.metadata_filter(hdnl, 'SCHEMA_EXPR', 'IN (''SAMPLE_SCHEMA'')');  

    dbms_datapump.start_job(hdnl);
END;
/

Review the export details by reviewing the export.log file in your local DATA_PUMP_DIR directory.

DBA

Task Description Skills required

Task	Description	Skills required
Upload the data dump file from the source to the S3 bucket.	Using AWS CLI, run the following command. `aws s3 cp sample.dmp s3://<bucket_created_epic_1>/`	DBA

Upload the data dump file from the source to the S3 bucket.

Using AWS CLI, run the following command.


aws s3 cp sample.dmp s3://<bucket_created_epic_1>/

DBA

Task Description Skills required

Task	Description	Skills required
Download the data dump file to Amazon RDS	To copy the dump file `sample.dmp` from the S3 bucket to the Amazon RDS for Oracle database, run the following SQL command. In this example, the `sample.dmp` file is downloaded from the S3 bucket `my-s3-integration1` to the Oracle directory `DATA_PUMP_DIR`. Make sure that you have sufficient disk space allocated to your RDS instance to accommodate both the database and the export file. `-- If you want to download all the files in the S3 bucket remove the p_s3_prefix line. SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name => 'my-s3-integration', p_s3_prefix => 'sample.dmp', p_directory_name => 'DATA_PUMP_DIR') AS TASK_ID FROM DUAL;` The previous command outputs a task ID. To review the status of the download by reviewing the data in the task ID, run the following command. `SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-<task_id>.log'));` To see the files in the `DATA_PUMP_DIR` directory, run the following command. `SELECT filename,type,filesize/1024/1024 size_megs,to_char(mtime,'DD-MON-YY HH24:MI:SS') timestamp FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => upper('DATA_PUMP_DIR'))) order by 4;`	AWS systems administrator

Download the data dump file to Amazon RDS

To copy the dump file sample.dmp from the S3 bucket to the Amazon RDS for Oracle database, run the following SQL command. In this example, the sample.dmp file is downloaded from the S3 bucket my-s3-integration1 to the Oracle directory DATA_PUMP_DIR. Make sure that you have sufficient disk space allocated to your RDS instance to accommodate both the database and the export file.


-- If you want to download all the files in the S3 bucket remove the p_s3_prefix line.

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
      p_bucket_name    =>  'my-s3-integration',    
      p_s3_prefix => 'sample.dmp',
      p_directory_name =>  'DATA_PUMP_DIR') 
   AS TASK_ID FROM DUAL;

The previous command outputs a task ID. To review the status of the download by reviewing the data in the task ID, run the following command.


SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-<task_id>.log'));

To see the files in the DATA_PUMP_DIR directory, run the following command.


SELECT filename,type,filesize/1024/1024 size_megs,to_char(mtime,'DD-MON-YY HH24:MI:SS') timestamp
FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => upper('DATA_PUMP_DIR'))) order by 4;

AWS systems administrator

Task Description Skills required

Task	Description	Skills required
Restore the schema and data to Amazon RDS.	To import the dump file into the `sample_schema` database schema, run the following SQL command from SQL Developer or SQL*Plus. DECLARE hdnl NUMBER; BEGIN hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', 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 => 'import.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''SAMPLE_SCHEMA'')'); DBMS_DATAPUMP.START_JOB(hdnl); END; / To see the log file from the import, run the following command. `SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR','import.log'));`	DBA

Restore the schema and data to Amazon RDS.

To import the dump file into the sample_schema database schema, run the following SQL command from SQL Developer or SQL*Plus.


DECLARE
hdnl NUMBER;
BEGIN

hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', 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  => 'import.log', directory => 'DATA_PUMP_DIR', filetype  => dbms_datapump.ku$_file_type_log_file);

DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''SAMPLE_SCHEMA'')');

DBMS_DATAPUMP.START_JOB(hdnl);

END;
/

To see the log file from the import, run the following command.


SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR','import.log'));

DBA

Task Description Skills required

Task	Description	Skills required
List and clean up the export files.	List and remove the export files in the `DATA_PUMP_DIR` directory, run the following commands. `-- List the files SELECT filename,type,filesize/1024/1024 size_megs,to_char(mtime,'DD-MON-YY HH24:MI:SS') timestamp FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => upper('DATA_PUMP_DIR'))) order by 4;` `-- Remove the files EXEC UTL_FILE.FREMOVE('DATA_PUMP_DIR','sample.dmp'); EXEC UTL_FILE.FREMOVE('DATA_PUMP_DIR','import.log');`	AWS systems administrator

List and clean up the export files.

List and remove the export files in the DATA_PUMP_DIR directory, run the following commands.


-- List the files
SELECT filename,type,filesize/1024/1024 size_megs,to_char(mtime,'DD-MON-YY HH24:MI:SS') timestamp FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => upper('DATA_PUMP_DIR'))) order by 4;


-- Remove the files
EXEC UTL_FILE.FREMOVE('DATA_PUMP_DIR','sample.dmp');
EXEC UTL_FILE.FREMOVE('DATA_PUMP_DIR','import.log');

AWS systems administrator

Related resources

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Migrate from Oracle to Amazon RDS for Oracle

Migrate from PostgreSQL on Amazon EC2 to Amazon RDS for PostgreSQL