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

Create the bucket.

To create the S3 bucket, follow the instructions in the AWS documentation.

Configure IAM permissions.

To configure permissions, follow the instructions in the AWS documentation.

Create the target Amazon RDS for Oracle DB instance.

To create the Amazon RDS for Oracle instance, follow the instructions in the AWS documentation.

Associate the role with the DB instance.

To associate the role with the instance, follow the instructions in the AWS documentation.

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;

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.

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>/

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;

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'));

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');